Why does the output parameter in a SQL Server stored procedure keep returning null?

Question

I have written a C# program which calls a SQL Server stored procedure to obtain information from database.

C# program:

public void testing(ref int pIMPORT_ID)
{
    string sMessage = null;
    int sValue = 0;
    ADODB.Command cnnCmd = null;
    object resc;

    ModCommon.adodb_connect();

    cnnCmd = new ADODB.Command();
    cnnCmd.ActiveConnection = ModMain.gConn;
    cnnCmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;
    cnnCmd.CommandText = "sp_TEST";

    try
    {
        cnnCmd.Parameters.Append(cnnCmd.CreateParameter("RETURN_VALUE", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamReturnValue));
        cnnCmd.Parameters.Append(cnnCmd.CreateParameter("IMPORT_ID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput));
        cnnCmd.Parameters.Append(cnnCmd.CreateParameter("MSG", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamOutput, 200));
        cnnCmd.Parameters.Refresh();

        cnnCmd.Parameters["@IMPORT_ID"].Value = pIMPORT_ID;

        cnnCmd.Execute(out resc);

        sMessage = cnnCmd.Parameters["@MSG"].Value;
        sValue = cnnCmd.Parameters["@RETURN_VALUE"].Value;

        ModMain.gConn.Close();
    }
    catch (Exception ex)
    {
        FileSystem.FileOpen(11, ModMain.strErrLogFile, OpenMode.Output);
        FileSystem.PrintLine(11, "*****");
        FileSystem.PrintLine(11, "Error occurs!");
        FileSystem.FileClose(11);
        System.Environment.Exit(0);
    }
}

After executing the following stored procedure, I get {} for sMessage and null for sValue.

ALTER PROCEDURE [dbo].[sp_TEST]
    @IMPORT_ID int,
    @MSG varchar(200) output
AS
BEGIN
    DECLARE @SQL varchar(4000)
    DECLARE @cnt_B int = 0

    CREATE TABLE #TEMP (cnt int)

    SELECT @SQL = 'insert into #TEMP select count(*) from STAFF where IMPORT_ID = ' + STR(@IMPORT_ID)
    EXEC (@SQL)

    IF @@ROWCOUNT > 0
        SET @MSG = 'NOT EMPTY'
    ELSE 
        SET @MSG = 'EMPTY'

    DROP TABLE #TEMP

    RETURN 0
END

But after executing the following stored procedure (same as the above but with the line execute(@SQL) commented), I get "NOT EMPTY" for sMessage and 0 for sValue, which is the desired result.

ALTER PROCEDURE [dbo].[sp_TEST_TEMP_TABLE]
    @IMPORT_ID int,
    @MSG varchar(200) output
AS
BEGIN
    DECLARE @SQL varchar(4000)
    DECLARE @cnt_B int = 0

    CREATE TABLE #TEMP(cnt int)

    SELECT @SQL = 'insert into #TEMP select count(*) from STAFF where IMPORT_ID = ' + STR(@IMPORT_ID)
    --exec (@SQL)

    IF @@ROWCOUNT > 0
        SET @MSG = 'NOT EMPTY'
    ELSE
        SET @MSG = 'EMPTY'

    DROP TABLE #TEMP

    RETURN 0
END
GO

Why would this happened? The STAFF table is not empty.

Answer 1

Execute your stored procedure in SSMS and look for any errors. For me, it was:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'STAFF'.

It is completely possible that your dynamic SQL gets its database context different from the one the stored procedure resides in. So, in order of prevalence:

• Make sure you actually need to use dynamic SQL. Yes, it has to be justified each time you step into this territory, because it makes your code potentially vulnerable to injection attacks.

• Prepend your dynamic statements with database context switch. For example:

  set @SQL = 'use '+ quotename(db_name()) +'; insert into #TEMP select count(*) from STAFF where IMPORT_ID = ' + STR(@IMPORT_ID);

• Parameterise your dynamic statements. It will require ditching exec() in favour of sys.sp_executesql, but you'll be glad you did it next time you'll try to concatenate a string parameter which is coming from user;

• Make sure that any object you touch dynamically does exist in the current database (or you'll get error 208), and that it has corresponding columns (error 207).

Answer 2

I don't think you should be using @@ROWCOUNT in your case, why don't you query your temp table to see if there is any row ?

I'm also not sure I understand why you're writing dynamic query.

You could do this instead:

DECLARE @cnt as int 
SELECT @cnt = count(*) from STAFF where IMPORT_ID = @IMPORT_ID
if (@cnt > 0) etc...