sp_executesql sets a column to null

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.

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.