T-SQL stored procedure returns null in code, but works in console

Question

I have a stored procedure

CREATE procedure [dbo].[get_unique_identifier]
AS
DECLARE @ret_val INT
UPDATE seq SET @ret_val = id = id + 1
RETURN @ret_val

that queries a table (seq) that has a single int column and single row, increments the value and then then returns it. Don't ask why I'm doing this, but in short, the idea is to simulate a PostgreSQL sequence and no, an identity column would not do the job. Anyway, this works fine in SQL Management Studio, where sequential executions of

DECLARE @returned INT
EXEC @returned = get_unique_identifier
SELECT @returned

produce the expected output. Unfortunately, I can't seem to get the returned value in the application I'm working on

OdbcCommand command = new OdbcCommand("get_unique_identifier");
command.CommandType = CommandType.StoredProcedure;
OdbcParameter return_param = new OdbcParameter("@RETURN_VALUE", OdbcType.BigInt);
return_param.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(return_param);
Util.SQLExecuteParameterizedNonQuery(command);
Console.WriteLine(command.Parameters["@RETURN_VALUE"].Value.ToString());

The output is an empty string, but the value itself is DBNull.Value. The OdbcType.BigInt is left over from some testing I was doing. It was initially Int.

Edit: This is clearly a bug with ODBC. A workaround is posted below. Don't use ODBC if you don't have do.

Answer 1

The return value of stored procedures is Int (32-bit), not BigInt (64-bit), but I'm not sure that this would be the problem with your code.

Answer 2

Have you not tried using an output variable instead...I am jogging my memory and think that the line command.Parameters["@RETURN_VALUE"].Value.ToString() could be this...The reason it worked in SQL Management Studio because you declared a variable and used it in an TSQL EXEC statement. I hope this answer somehow gives you a hint..

Hope this helps, Best regards, Tom.

Answer 3

I am sorry to have to say, but this might be due to using the Odbc

I tried this using out SqlClient database object and it returned the values as expected.

SqlCommand command = new SqlCommand("ZZZ_get_unique_identifier");
            command.CommandType = CommandType.StoredProcedure;
            SqlParameter return_param = new SqlParameter("@RETURN_VALUE",SqlDbType.BigInt);
            return_param.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(return_param);
            SqlConnection con = new SqlConnection(dbM.DefaultConnectionString);
            con.Open();
            command.Connection = con;
            command.ExecuteNonQuery();
            int i = Convert.ToInt32(command.Parameters["@RETURN_VALUE"].Value.ToString());
            con.Close();