Stored procedure output parameter using sp_executesql returning null

Question

I'm trying to determine why a db call in .Net is failing with an output parameter. I'm using Dapper ORM to make the call and this is a bare bones sample of the trace. It uses sp_executesql to parametrize the call and I think there lies the problem with the output parameter.

Consider this code:

CREATE PROC test
    @addressId INT = NULL OUTPUT
AS
    -- using select and set to see if if makes a difference using either
    select @addressId = 1
    SET @addressId = 1
GO

declare @p3 int
set @p3=NULL
exec sp_executesql N'test',N'@addressId int output',@addressId=@p3 output
select @p3

I would expect select @p3 to return 1, why does it return null?

Why are you using dynamic sql here? This should be a straight forward stored procedure call....no dynamic sql needed. — Sean Lange
– Sean Lange, Commented Apr 20, 2016 at 19:35
It was an output for one of the traces. I'm wondering now if it was a sqlText (instead of proc) call which caused that kind of sql. I've got it working now using another method. — Phil Cooper
– Phil Cooper, Commented Apr 21, 2016 at 10:03

Ed Harper · Accepted Answer · 2016-04-20 19:36:53Z

3

You need to explicitly declare @addressId as an output parameter in the stored procedure call in exactly the same way you would if you weren't using dynamic SQL:

declare @p3 int
set @p3=NULL
exec sp_executesql N'EXEC test @addressId=@addressId OUTPUT',N'@addressId int output',@addressId=@p3 output
select @p3

Without dynamic SQL, the code would have to be:

declare @p3 int
set @p3=NULL
EXEC test @addressId=@p3 OUTPUT
select @p3

answered Apr 20, 2016 at 19:36

Ed Harper

21.6k5 gold badges60 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phil Cooper Over a year ago

Thanks for your response. The app is sending the sql correctly now and the traces looks like the second example in your answer. At least I know now what was missing from the original trace to make it work as expected.

Charlieface · Accepted Answer · 2022-08-05 14:05:35Z

0

As mentioned, the problem is that the dynamic SQL isn't actually passing the parameter through.

However, in this instance, you don't actually need dynamic SQL at all.

declare @p3 int;
exec test @addressId=@p3 output;
select @p3;

Furthermore, it's highly likely that you were simply using SqlCommand incorrectly.

SqlCommand.CommandType defaults to CommandType.Text which explains why the above code was generated, as it expects that the text is an SQL batch rather than the name of a stored procedure. No error was generated because it's valid to call a procedure without using the keyword EXEC.

If you use CommandType.StoredProcedure then everything should just work.

answered Aug 5, 2022 at 14:05

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Stored procedure output parameter using sp_executesql returning null

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related