sql server - SQL add a variable to a query -

how create variables specified once , used in queries later in script? these variables may used multiple times in query, , in multiple queries in script. use @x such variable in examples below.

what want like:

declare @query nvarchar(1000) declare @x nvarchar(40)  set @x = 'test' set @query = 'select [name]                      , ' + @x + ' [testcase]               mytable'  exec (@query)  -- returns "invalid column name 'test'" 

which returns error mentioned above. achieve equivalent of:

declare @query nvarchar(1000) declare @x nvarchar(40)  set @x = 'test' set @query = 'select [name]                      , ''test'' [testcase]               mytable'  exec (@query)  -- returns e.g. -- name   testcase -- alice  test -- bob    test 

i note following doesn't work , returns same error first:

declare @query nvarchar(1000) declare @x nvarchar(40)  set @x = 'test' set @query = 'select [name]                      , ' + 'test' + ' [testcase]               mytable'  exec (@query)  -- returns "invalid column name 'test'" 

based on error , since i'm not trying use @x column name, variable, assume i'm using invalid implementation of variable.

since you're not trying use variable column name, not need use dynamic sql @ all. (which thing(tm) since dynamic sql should used great deal of caution due being great attack surface.)

a simple:

declare @x nvarchar(40)  set @x = 'test'  select [name], @x testcase mytable 

will do.

---

that being said, if have use case dynamic sql (again particular query in question here not perhaps ad-hoc query being passed in procedure), thing pass variable parameter query via sp_executesql. akin creating stored procedure parameters:

declare @x nvarchar(40) declare @query nvarchar(1000)  set @x = 'test'  set @query = 'select [name], @x testcase mytable'  exec sp_executesql @query, n'@x nvarchar(1000)', @x