CODEWITHSUNDEEP
c#sql-serverstored-proceduresoutput-parameter
Kelly Cline
Kelly Cline

Reputation: 2246

Calling Stored Procedure with output parameters

This is MS Sql Server: I have wrapped the system stored procedure, sp_sequence_get_range, so that I can have it return with a simple select (I was not able to figure out how to deal with the OUTPUT parameters in my C# framework).

This is the entire stored procedure:

CREATE PROCEDURE GetSequenceRange( @name VARCHAR, @counter INTEGER) AS

DECLARE @firstValue SQL_VARIANT = 0;
DECLARE @lastValue SQL_VARIANT = 0;
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'EXEC sp_sequence_get_range @sequence_name = N''' + @name + N''', @range_size = ' + CONVERT(VARCHAR, @counter)
    + N', @range_first_value = @firstValue OUTPUT, @range_last_value = @lastValue OUTPUT;'
EXECUTE sp_executesql @sql, N'@name VARCHAR, @counter INTEGER, @firstValue SQL_VARIANT OUTPUT, @lastValue SQL_VARIANT OUTPUT', @name, @counter, @firstValue OUTPUT, @lastValue OUTPUT
SELECT @firstValue, @lastValue

When I try to execute the procedure like

exec GetSequenceRange 'tablename', 3

I get

Invalid object name 't'.

Whatever name I provide, the first letter is returned in this error message.

I have also tried 

exec GetSequenceRange @name='tablename', @counter = 3

The 'tablename' in all these examples is a qualified sequence name; I have tried both owner.sequence and database.owner.sequence.

I think there is something fundamentally wrong with my approach, but I don't see it.

Bonus for an answer that shows how to get the results from sp_sequence_get_range from a C# call without having this stored procedure wrapper in the first place.

Upvotes: 2

Views: 2329

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48904

You should be able to call the stored procedure directly rather easily. The following should work:

long _RangeFirstValue;
long _RangeFirstValue;

using (SqlConnection _Connection = new SqlConnection(_MyConnectionString))
{
    using (SqlCommand _Command = _Connection.CreateCommand())
    {
        _Command.CommandText = "sp_sequence_get_range";
        _Command.CommandType = CommandType.StoredProcedure;

        SqlParameter _ParamSequenceName =
                             new SqlParameter("sequence_name", SqlDbType.NVarChar, 776);
        _ParamSequenceName.Value = _MySequenceName;
        _Command.Parameters.Add(_ParamSequenceName);

        SqlParameter _ParamRangeSize = new SqlParameter("range_size", SqlDbType.BigInt);
        _ParamRangeSize.Value = _MyRangeSize;
        _Command.Parameters.Add(_ParamRangeSize);

        SqlParameter _ParamRangeFirstValue =
                             new SqlParameter("range_first_value", SqlDbType.Variant);
        _ParamRangeFirstValue.Direction = ParameterDirection.Output;
        _Command.Parameters.Add(_ParamRangeFirstValue);

        SqlParameter _ParamRangeLastValue =
                             new SqlParameter("range_last_value", SqlDbType.Variant);
        _ParamRangeLastValue.Direction = ParameterDirection.Output;
        _Command.Parameters.Add(_ParamRangeLastValue);

        _Command.ExecuteNonQuery();

        _RangeFirstValue = (long)(_ParamRangeFirstValue.Value);
        _RangeLastValue = (long)(_ParamRangeLastValue.Value);
    }
}

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40359

[Edit]

Meh. Reread the problem after posting this, and yeah, the problem is probably too-short varchars. Odds are sp_executeSQL will run fine with the "unmapped" variables. Leaving this answer here, because this code should be simplified, if only to make future maintenance and modification simpler.

First and foremost, I totally agree that if you can call the system procedure directly, then you should do so--dynamic SQL is a pain and is best avoided. With that said, the problem with this code is that your complex dynamic SQL is confused.

Step one is to build the dynanmic SQL. This is fine, I'm copying it here with some spacing for clarity:

SET @sql = N'EXEC sp_sequence_get_range @sequence_name = N'''
 + @name
 + N''', @range_size = '
 + CONVERT(VARCHAR, @counter)
 + N', @range_first_value = @firstValue OUTPUT, @range_last_value = @lastValue OUTPUT;'

Here, you are concatenating hard-coded text with passed in variables @name and @counter. If you pass in "MyName" and "42", you get (again added spacing for clarity):

EXEC sp_sequence_get_range
  @sequence_name     = N'MyName'
 ,@range_size        = 42
 ,@range_first_value = @firstValue OUTPUT
 ,@range_last_value  = @lastValue OUTPUT;

You then pass this as the first parameter of sp_executeSQL:

EXECUTE sp_executesql
  @sql
 ,N'@name VARCHAR, @counter INTEGER, @firstValue SQL_VARIANT OUTPUT, @lastValue SQL_VARIANT OUTPUT'
 ,@name
 ,@counter
 ,@firstValue OUTPUT
 ,@lastValue OUTPUT

And there it is--you're attempting to pass in @name and @counter as parameters to sp_executeSQL, which you do not need to do because they are hard-coded into the dynamic SQL text. Moreover, they are not defined as parameters within @sql for sp_executeSQL to pass (map?) it's parameter-defined values into. Take them out, and it should be fine:

EXECUTE sp_executesql
  @sql
 ,N'@firstValue SQL_VARIANT OUTPUT, @lastValue SQL_VARIANT OUTPUT'
 ,@firstValue OUTPUT
 ,@lastValue OUTPUT

Upvotes: 0

Related Questions