CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
Narendra
Narendra

Reputation: 21

Dynamic sql query input and output values pass in variables

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.

Upvotes: 2

Views: 4999

Answers (1)

podiluska
podiluska

Reputation: 51514

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

Upvotes: 1

Related Questions