Calling Stored Procedure with output parameters

Question

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.

did you try the names in square brackets? eg [mydb].[dbo].[sometable] — BugFinder, Mar 03 '16 at 14:25
The missing varchar size in the create procedure statement was the problem. — Kelly Cline, Mar 03 '16 at 14:32
You need to add the length to your variables in your dynamic sql too. You should ALWAYS ALWAYS declare the length of character variable and columns. — Sean Lange, Mar 03 '16 at 14:40
@SeanLange Doesn't `VARCHAR` default to `50` if not specified? — user692942, Mar 03 '16 at 14:41
@Lankymart it is 30 in most cases. See why we should specify the length? The default is easy to forget. — Sean Lange, Mar 03 '16 at 14:43
@SeanLange I always do just remember reading it had a default once. — user692942, Mar 03 '16 at 14:44
@KellyCline to avoid using a wrapper proc at all, see if this helps to call the inner stored procedure directly from C#: http://stackoverflow.com/questions/10905782/using-stored-procedure-output-parameters-in-c-sharp — Tab Alleman, Mar 03 '16 at 14:45
When declaring a variable without length the default is 1, when convert/cast it will default to 30. — Sean Lange, Mar 03 '16 at 14:46
@TabAlleman [char and varchar (Transact-SQL)](https://msdn.microsoft.com/en-gb/library/ms176089.aspx) - *"When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30."* So your both right. — user692942, Mar 03 '16 at 14:46
@Lankymart the default varchar size in table designer is 50. Not I use the table designer ever but it is weird that it defaults to a different size. — Sean Lange, Mar 03 '16 at 14:49
@SeanLange Yeah good point. I haven't for years either but knew I'd seen it somewhere. — user692942, Mar 03 '16 at 14:55
They said they would make it consistent back in 2007: http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=267605 - Unfortunately, nothing has happened since then - so always specify the length yourself! — Bridge, Mar 03 '16 at 16:03

Solomon Rutzky · Answer 1 · 2016-03-03T15:59:38.827

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

score 0 · Answer 2 · answered Mar 03 '16 at 15:50

[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

Calling Stored Procedure with output parameters

2 Answers2