CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mike Dole
Mike Dole

Reputation: 687

Output parameter in stored procedure is null

I can't seem to get the output parameter right, it's empty where I'm exepecting 'TEMPTABLE'?

create table TEMPTABLE
(
  Id int identity primary key,
  Name nvarchar(50) not null,
);
GO

insert into TEMPTABLE(Name) values('Name 1');
GO

DECLARE @PARMOUTP nvarchar(50)
DECLARE @ParameterDefinition AS NVARCHAR(2000)
DECLARE @DestTable VARCHAR(50)
declare @sql nvarchar(max)

    set @sql = 'SELECT   OBJECT_NAME(OBJECT_ID) AS TABLENAME 
                FROM     SYS.IDENTITY_COLUMNS  
                WHERE OBJECT_NAME(OBJECT_ID) = @DestTable'

set @DestTable = 'TEMPTABLE'

set @ParameterDefinition = '@DestTable VARCHAR(50), @PARMOUTP VARCHAR(50) OUTPUT'


EXECUTE sp_executesql @sql,
                  @ParameterDefinition,
                  @DestTable,
                  @PARMOUTP OUTPUT

print @PARMOUTP;
print isnull(@PARMOUTP, 'PARMOUTP is null!');

What am I doing wrong here?

Regards,

Mike

Upvotes: 0

Views: 2695

Answers (2)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

Your query is not suited for sp_executesql with one parameter. In the query you should assign value to that parameter and execute it. Something like this:

create table TEMPTABLE
(
  Id int identity primary key,
  Name nvarchar(50) not null,
);
GO

insert into TEMPTABLE(Name) values('Name 1');
GO

DECLARE @PARMOUTP nvarchar(50)
DECLARE @ParameterDefinition AS NVARCHAR(2000)
DECLARE @DestTable VARCHAR(50)
declare @sql nvarchar(max)

    set @sql = 'SELECT   @PARMOUTP = OBJECT_NAME(OBJECT_ID) AS TABLENAME 
                FROM     SYS.IDENTITY_COLUMNS  
                WHERE OBJECT_NAME(OBJECT_ID) = @DestTable'

set @DestTable = 'TEMPTABLE'

set @ParameterDefinition = '@DestTable VARCHAR(50), @PARMOUTP VARCHAR(50) OUTPUT'


EXECUTE sp_executesql @sql,
                  @ParameterDefinition,
                  @DestTable,
                  @PARMOUTP OUTPUT

print @PARMOUTP;
print isnull(@PARMOUTP, 'PARMOUTP is null!');

Remember that you are returning scalar value from your dynamic query.

Upvotes: 2

marc_s
marc_s

Reputation: 755207

In your dynamic SQL statement, you're never assigning any value to the output parameter!

You're selecting the table name as a result set - but it never gets stored into the output variable.

Try this:

set @sql = 'SELECT @PARMOUTP = OBJECT_NAME(OBJECT_ID)
            FROM SYS.IDENTITY_COLUMNS  
            WHERE OBJECT_NAME(OBJECT_ID) = @DestTable'

Upvotes: 1

Related Questions