CODEWITHSUNDEEP
sqlsql-servert-sql
w0051977
w0051977

Reputation: 15787

Field Name stored in variable

Is it possible to do this?

DECLARE @TableName As varchar(50)
set @TableName = 'tbl_goccurrence'   
DECLARE @OccurrenceFileNo As varchar(50)
    exec ('select ' + @OccurrenceFileNo + ' = occurrencefileno from @TableName where id=902000000001387589')
    print @OccurrenceFileNo

The error I get is: Incorrect syntax near '='

Upvotes: 0

Views: 47

Answers (2)

D Stanley
D Stanley

Reputation: 152501

{starting over based on updated question}

I think you want:

exec sp_executesql N'select @OccurrenceFileNo = occurrencefileno from ' 
                 +  @TableName 
                 + ' where id=902000000001387589'
                  , N'@OccurrenceFileNo varchar(50) out'
                  , @occurrenceFileNo out

Upvotes: 0

Jerrad
Jerrad

Reputation: 5290

You can use sp_executesql with an output parameter to assign the value to @occurrenceFileNo.

DECLARE @OccurrenceFileNo As varchar(50)
exec sp_executesql N'select @OccurrenceFileNo = occurrencefileno from tbl_goccurrence where id=902000000001387589', N'@OccurrenceFileNo varchar(50) out', @occurrenceFileNo out
print @OccurrenceFileNo

Upvotes: 1

Related Questions