SQL代码不返回任何值

I have the following stored procedure that is being called in C#:

我有以下在C#中调用的存储过程:

ALTER PROCEDURE [dbo].[GetJobStatusSummary] @FiscalYear int,
@JobIDList varchar(max)
AS
  SELECT
    SUM(CASE StatusID
      WHEN 5 THEN 1
      ELSE 0
    END) AS Completed,
    SUM(CASE StatusID
      WHEN 5 THEN 0
      ELSE 1
    END) AS Incomplete
  FROM Review
  WHERE FiscalYear = @FiscalYear
  AND JobID + '|' + Position IN (SELECT
    Argument
  FROM dbo.CreateInClause(@JobIDList))
  GROUP BY FiscalYear

When I pass in the following (this was taken from SQL Server Management Studio):

当我传入以下内容时(这是从SQL Server Management Studio中获取的):

DECLARE @return_value int

EXEC @return_value = [dbo].[GetJobStatusSummary] @FiscalYear = 2015,
                                                 @JobDList = N'00304730,00334573,00635385,'

SELECT
  'Return Value' = @return_value
GO

I'm not getting anything back for Completed or Incomplete, just a return value of 0. I've verified there is in fact data in the database pertaining to these Job ID's. I'm not that skilled at SQL, as I've sort of inherited this project. The stored procedure hasn't changed, so I'm not sure what I'm missing. Any ideas?

我没有收到任何已完成或未完成的任何内容,只返回值为0.我已经确认数据库中存在与这些作业ID相关的数据。我不熟悉SQL,因为我继承了这个项目。存储过程没有改变,所以我不确定我缺少什么。有任何想法吗?

Edit: Sample of data from the table, is this useful? The ID's I'm using to test are not represented here, but this is an example of the data in there

编辑:表格中的数据样本,这有用吗?我用来测试的ID没有在这里表示,但这是其中数据的一个例子

JobID   StatusID  FiscalYear    CreatedDT   LastUpdatedDT
14999       5       2011    55:15.4 12:15.3
163511      2       2011    08:20.6 12:15.3

4 个解决方案

#1

This line:

SELECT  'Return Value' = @return_value

is going to return 0 if the procedure execute correctly. The @return_value is that for. So you don't need to use SELECT 'Return Value' = @return_value at the end of the call.

如果过程正确执行,则返回0。 @return_value就是为了。因此,您不需要在调用结束时使用SELECT'返回值'= @return_value。

try executting you're query along with the parameters so you can determine if it is good.

尝试执行您的查询以及参数,以便您可以确定它是否良好。

And try again just:

再试一次:

DECLARE @return_value int
DECLARE @FiscalYear int
DECLARE @JobDList VARCHAR(MAX)

EXEC    @return_value = [dbo].[GetJobStatusSummary]
        @FiscalYear = 2015,
        @JobDList = N'00304730,00334573,00635385,'

#2

SELECT SUM( CASE StatusID WHEN 5 THEN 1 ELSE 0 END ) AS Completed  ,
    SUM( CASE StatusID WHEN 5 THEN 0 ELSE 1 END ) AS Incomplete,
    FiscalYear
FROM    Review
WHERE   FiscalYear = @FiscalYear AND 
        JobID + '|' + Position IN ( SELECT Argument FROM dbo.CreateInClause( @JobIDList ) )
GROUP BY FiscalYear

#3

I would start by execute the two queries to see if there is any data in the tables:

我将从执行两个查询开始,看看表中是否有任何数据:

SELECT StatusID FROM Review WHERE FiscalYear = 2015;

And when there are results, check what the output of 'JobID + '|' + Position' is.

当有结果时,检查'JobID +'|'的输出是什么+位置'是。

SELECT StatusID, JobID + '|' + Position FROM Review WHERE FiscalYear = 2015;

Maybe you can see that the output is not in the list which comes out of: SELECT Argument FROM dbo.CreateInClause( @JobIDList )

也许你可以看到输出不在列表中:SELECT Argument FROM dbo.CreateInClause(@JobIDList)

#4

I think I got it. As I said, the SQL (unless it's just selecting something) is above my head, but since I know the SQL didn't change, it looks like the C# did.

我想我明白了。正如我所说,SQL(除非它只是选择一些东西)在我的脑海之上,但由于我知道SQL没有改变,它看起来像C#。

I have this line of code in my C#

我在C#中有这行代码

var jobs= from d in jobData select d.JobID;

In the SQL it was also looking for position to be there, with the "|", which it was not. I changed that line to:

在SQL中,它也在寻找位置,使用“|”,它不是。我改变了这一行:

var jobs= from d in jobData select d.JobID;+ "|" + d.Position

I apologize if I posted this in the wrong forum.

如果我在错误的论坛上发布这个,我道歉。

#1