C# & SQL Server stored procedures - can't get output parameter value, return value works

Question

I would like to get a value of an output parameter of a stored procedure, but I'm getting +1 all the time. When obtaining a value by return everything's ok.

I managed to obtain a value of the output parameter using Entity Framework, however, it works really slowly. I'm attaching my stored procedure and C# code used to get the values from the stored procedures.

Stored procedure with an output parameter works fine in SQL Server.

Does someone have any idea why my code doesn't work?

Thanks for your help in advance.

Stored procedure:

Create procedure sp_CountEachNumberOccurencesOutput
    @number tinyint, 
    @count_number int output
As
    SET NOCOUNT ON

    select count(*) 
    from RawData
    where Number1 = @number or Number2 = @number or Number3 = @number

    select @count_number = count(*)

C# code for stored procedure with and output parameter which uses Entity Framework and works, but it's slow:

using (SqlConnection cn = new SqlConnection(cnStr))
{
    cn.Open();

    using (SqlCommand cmd = new SqlCommand("sp_CountEachNumberOccurencesOutput", cn))
    {
        var outParam = new SqlParameter();
        int outputValue = 0;
        outParam.SqlDbType = SqlDbType.Int;
        outParam.Direction = ParameterDirection.Output;

        DbContext dbContext = new DbContext(this.connectionString);

        var data = dbContext.Database.SqlQuery<int>("sp_CountEachNumberOccurencesOutput @number, @count_number OUT",
                   new SqlParameter("number", parameter),
                   new SqlParameter("count_number", outputValue),
                   outParam);

        outputValue = data.ToList()[0];

        return outputValue;
    }
}

C# code for stored procedure with and output parameter which doesn't work:

using (SqlConnection cn = new SqlConnection(cnStr))
{
    cn.Open();

    using (SqlCommand cmd = new SqlCommand("sp_CountEachNumberOccurencesOutput", cn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter param = cmd.Parameters.Add("@number", SqlDbType.Int);
        param.Direction = ParameterDirection.Input;
        param.Value = parameter;

        SqlParameter outputParameter = cmd.Parameters.Add("@count_number", SqlDbType.Int);
        outputParameter.Direction = ParameterDirection.Output;
        outputParameter.Value = 0;                   

        cmd.ExecuteNonQuery();

        cn.Close();

        int OutputValue = (int)cmd.Parameters["@count_number"].Value;

        return OutputValue;
    }
}

This code gets return value and works:

using (SqlConnection cn = new SqlConnection(cnStr))
{
    cn.Open();

    using (SqlCommand cmd = new SqlCommand("sp_CountEachNumberOccurencesReturn", cn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter param = cmd.Parameters.Add("@number", SqlDbType.Int);
        param.Direction = ParameterDirection.Input;
        param.Value = parameter;

        SqlParameter returnValue = new SqlParameter();
        returnValue.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(returnValue);

        return Convert.ToInt32(cmd.ExecuteScalar());
    }
}

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Jul 12 '17 at 20:32

Allen King · Accepted Answer · 2017-07-12T20:45:48.323

1

Not sure if you can assign count(*) to a parameter. It only works in the sql statement.

Perhaps try this

Create procedure sp_CountEachNumberOccurencesOutput
    @number tinyint, @count_number int output
    As
    SET NOCOUNT ON
    select @counter_number=count(*)  from RawData
                where Number1 = @number or Number2 = @number  or Number3 = @number

edited Jul 12 '17 at 20:45

answered Jul 12 '17 at 20:16

Allen King

2,372
4
34
52

Actually `select count(*) into @counter_number from RawData` didn't work (incorrect syntax), but it gave me a hint how to solve this: `select @count_number = count(*) from RawData`. Thanks a lot! – Łukasz Sypniewski Jul 12 '17 at 20:44
@Sypcio thanks. Yes I guess its b/c I use too much MySQL :-). Fixed my answer. – Allen King Jul 12 '17 at 20:46

score 1 · Answer 2 · answered Jul 12 '17 at 20:59

I used to perform this task as following:

public Int32 ExecuteGenericSP(string spName, out int errNum, out string errMsg, params object[] lstParams)
{
    Int32 returnValue;
    /* Aqui */
    SqlDatabase _sqlDatabase = new SqlDatabase(getConnectionString());
    using (DbConnection con = _sqlDatabase.CreateConnection())
    {
        DbCommand cmd = _sqlDatabase.GetStoredProcCommand(spName);
        _sqlDatabase.DiscoverParameters(cmd);
        int n = 3;

        errNum = 0;
        errMsg = "";
        cmd.Parameters["@RETURN_VALUE"].Value = 0;

        cmd.Parameters["@errNum"].Value = 0;
        cmd.Parameters["@errDes"].Value = "";

        foreach (object o in lstParams)
        {
            if (cmd.Parameters[n].DbType.ToString() == "DateTime")
                cmd.Parameters[n].Value = Convert.ToDateTime(o);
            else
                cmd.Parameters[n].Value = o.ToString();
            n++;
        }

        returnValue = _sqlDatabase.ExecuteNonQuery(cmd);

        errNum = Convert.ToInt16(_sqlDatabase.GetParameterValue(cmd, "errNum"));
        errMsg = _sqlDatabase.GetParameterValue(cmd, "errDes").ToString();
    }
    return returnValue;
}

And for instance, this is a sample of a SP:

ALTER PROCEDURE [dbo].[AUD_Insertar]
    @errNum smallint OUTPUT,
    @errDes varchar(50) OUTPUT,
    @id smallint,    
    @message varchar(MAX),
    @type char(1),
    @userId varchar(20),
    @modulo char(1)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SET @errNum = 0;
    SET @errDes = '';

    INSERT INTO log(message, type, userId, modulo) VALUES (@message, @type, @userId, @modulo)
    IF (@@ROWCOUNT = 0)
    BEGIN
        SET @errNum = 1;
        SET @errDes = 'Error while inserting';
    END
END

I hope this can help you. Regards,

Adrian

Hi Sypcio, if you don't mind (and if you are able to do it), could you please mark my answer as "useful" (the "Up" arrow at the top). Thanks! — Adrian Di Ruggiero, Jul 13 '17 at 21:42

C# & SQL Server stored procedures - can't get output parameter value, return value works

2 Answers2