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?

Answer 1

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