Problem understnading Variables in sp_executesql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem understnading Variables in sp_executesql

Author

Topic

rebeccasmith
Starting Member

3 Posts

Posted - 2010-11-04 : 17:12:02

Hi,

I'm having problems understanding the use of variables in sp_executesql statements. I have the following code:


DECLARE @Variable varchar(30);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @Variable = 'DESCRIPTION';
SET @SQLString = N'SELECT @max_titleOUT = @Cols 
   FROM TBL_BACKUPTEST'
SET @ParmDefinition = N'@Cols nvarchar(100),@max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @Cols=@Variable, @max_titleOut=@max_title OUTPUT;
PRINT @max_title;

the output of this is the column name (DESCRIPTION) but what i want is the value of the column.
when i take out the variable @Cols i get the value:


DECLARE @Variable varchar(30);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @Variable = 'DESCRIPTION';
SET @SQLString = N'SELECT @max_titleOUT = DESCRIPTION 
   FROM TBL_BACKUPTEST'
SET @ParmDefinition = N'@Cols nvarchar(100),@max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @Cols=@Variable, @max_titleOut=@max_title OUTPUT;
PRINT @max_title;

I'm not sure why using the variable @Cols does not produce the same result. It would be great if somebody could help me with this as i am really getting nowhere.

Thanks
rebecca

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-04 : 18:11:11

You can't parameterize column names with sp_executesql. You need to build concatenate those in yourself:


DECLARE @SQLString nvarchar(500)
	,@Cols varchar(30)
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @Cols = 'DESCRIPTION';
SET @SQLString = N'SELECT @max_titleOUT = [' + @Cols + ']
   FROM TBL_BACKUPTEST'
SET @ParmDefinition = N'@max_titleOUT varchar(30) OUTPUT';

print @sqlString
EXECUTE sp_executesql @SQLString, @ParmDefinition, @max_titleOut=@max_title OUTPUT;
PRINT @max_title;

Be One with the Optimizer
TG

rebeccasmith
Starting Member

3 Posts

Posted - 2010-11-08 : 20:02:12

Thanks that really helped me out

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-09 : 08:49:33

You're welcome! I'm glad you got it...

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources