sp_executesql sets a column to null

Question

I'm attempting to execute some SQL inside of sp_executesql.

Here is the generated SQL:

exec sp_executesql
N'declare @RC int
EXECUTE @RC = [dbo].[sp_StoredProcedureName]
  @parameterName
select @RC',
N'@parameterName nvarchar(4000)',
@parameterName=N'TEST'

Here is the stored procedure that is called from the generated SQL:

ALTER PROCEDURE [dbo].[sp_StoredProcedureName] (
    @parameterName varchar(4000)
)
with execute as owner
as

DECLARE @returnValue int

BEGIN TRANSACTION 

INSERT INTO [dbo].[TableName]
        (parameterName)
    VALUES
        (@parameterName)
    set @returnValue = IDENT_CURRENT('TableName')
COMMIT

SELECT @returnValue
GO

For some reason, parameterName is never set.

When attempting to select from TableName after the SP has been executed, ParameterName is NULL.

I am using MS SQL. The SQL was generated by ADO.NET.

Do not use the `sp_` prefix for stored procedure names. This is reserved for the system and using it has a [real impact](https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix). — Jeroen Mostert, Jun 14 '19 at 13:48
`SELECT @returnValue` returns a result set, not a return value. This stored procedure returned a result set with a single column and a single row — Panagiotis Kanavos, Jun 14 '19 at 13:49

score 2 · Accepted Answer · edited Jan 07 '22 at 14:39

2

Your stored procedure is not returning anything, hence the return value is NULL.

In general, you should only be using the return value from a stored procedure as a status, not to return actual data.

Real return values should be returned via output parameters.

Further, I strongly recommend an OUTPUT clause for this purpose:

ALTER PROCEDURE [dbo].[sp_StoredProcedureName] (
    @parameterName varchar(4000),
    @returnValue int OUTPUT
)
with execute as owner
as
BEGIN
    DECLARE @ids TABLE (id int);

    INSERT INTO [dbo].[TableName] (parameterName)
        OUTPUT id INTO @ids
        VALUES (@parameterName);

    SELECT TOP (1) @returnValue = id  -- only 1 is expected anyway
    FROM @ids;
END;

You would then call this as:

declare @RC int;
declare @parameterName nvarchar(4000);
set @parameterName = N'TEST';

exec [dbo].[sp_StoredProcedureName] @parameterName, @rc int OUTPUT;

Dynamic SQL is not necessary.

edited Jan 07 '22 at 14:39

Nimantha

6,405
6
28
69

answered Jun 14 '19 at 13:46

Gordon Linoff

1,242,037
58
646
786

I would then call the parameter something other than `@returnValue` -- that's quite confusing because client code often has to use a `ReturnValue` pseudo-parameter if it wants to capture the actual return value, so these would look the same. – Jeroen Mostert Jun 14 '19 at 13:50
@JeroenMostert . . . I agree with the naming. The OP chose that name for the returned id. I would just use `@id`. – Gordon Linoff Jun 14 '19 at 13:51
Yep, sorry these names are just for data obfuscation reasons. – Jessica Jun 14 '19 at 14:07
@GordonLinoff, thanks for the advice, I'll look into implementing it this way +1 – Jessica Jun 14 '19 at 14:08

sp_executesql sets a column to null

1 Answers1