Dynamic sql query input and output values pass in variables

Question

pass column name & value dynamically and get result of query into another variable.

@ColumnName , @SKU_ID are input variables, output data store into @ColumnValue variable.
DECLARE @ColumnName
char(50)
DECLARE @SKU_ID
varchar(50)
DECLARE @ColumnValue
varchar(150)
DECLARE @Sqlcommand
nvarchar(1000)
DECLARE @ColumnData
varchar(50)

SET @ColumnName = 'Color_Code'
SET @SKU_ID  = 'W16933'

SET @Sqlcommand = 'SELECT @ColumnData ='+@ColumnName+ 'FROM Stagetable WHERE SKU_ID = @SKU_ID' 
exec sp_executesql @Sqlcommand, N'ColumnData varchar(50) out' , @ColumnValue out

I'm getting below Error

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'ColumnData'. Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable "@ColumnData". please help me out.

podiluska · Accepted Answer · 2012-11-19 10:12:25Z

1

You need to prefix the variable declaration with an @

exec sp_executesql 
    @Sqlcommand, 
    N'@ColumnData varchar(50) out, @SKU_ID varchar(50)' , 
    @ColumnValue out, @SKU_ID

answered Nov 19, 2012 at 10:12

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic sql query input and output values pass in variables

I'm getting below Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

I'm getting below Error

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related