CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresodbc
dandan78
dandan78

Reputation: 13894

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

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.

Upvotes: 0

Views: 1668

Answers (3)

Adriaan Stander
Adriaan Stander

Reputation: 166476

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();

Upvotes: 1

t0mm13b
t0mm13b

Reputation: 34592

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.

Upvotes: 0

treaschf
treaschf

Reputation: 5944

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.

Upvotes: 0

Related Questions