SQL add a variable to a query

Question

How do I create variables that are specified once and then used in queries later in a script? These variables may be used multiple times in a query, and in multiple queries in a script. I use @x as such a variable in the examples below.

What I want to do is something like:

Declare @Query nvarchar(1000)
Declare @x nvarchar(40)

Set @x = 'test'
Set @Query = 'Select [Name]
                     , ' + @x + ' as [TestCase]
              From mytable'

Exec (@Query)

-- returns "Invalid column name 'test'"

Which returns the error mentioned above. I would like it to achieve the equivalent of:

Declare @Query nvarchar(1000)
Declare @x nvarchar(40)

Set @x = 'test'
Set @Query = 'Select [Name]
                     , ''test'' as [TestCase]
              From mytable'

Exec (@Query)

-- Returns e.g.
-- Name   TestCase
-- Alice  Test
-- Bob    Test

I also note that the following doesn't work and returns the same error as the first:

Declare @Query nvarchar(1000)
Declare @x nvarchar(40)

Set @x = 'test'
Set @Query = 'Select [Name]
                     , ' + 'test' + ' as [TestCase]
              From mytable'

Exec (@Query)

-- returns "Invalid column name 'test'"

Based on the error and since I'm not trying to use the @x as a column name, but just as a variable, I assume I'm using an invalid implementation of a variable.

lc. · Accepted Answer · 2016-08-24 06:49:03Z

4

Since you're not trying to use a variable as a column name, you do not need to use dynamic SQL at all. (Which is a Good Thing(TM) since dynamic SQL should only be used with a great deal of caution due to it being a great attack surface.)

A simple:

declare @x nvarchar(40)

set @x = 'test'

select [Name], @x as TestCase
from mytable

will do.

That being said, if you have a use case for dynamic SQL (again the particular query in question here does not but perhaps an ad-hoc query is being passed in to the procedure), the thing to do would be to pass your variable as a parameter to the query via sp_executesql. This is akin to creating a stored procedure with parameters:

declare @x nvarchar(40)
declare @query nvarchar(1000)

set @x = 'test'

set @query = 'select [Name], @x as TestCase from mytable'

exec sp_executesql @query, N'@x nvarchar(1000)', @x

edited Aug 24, 2016 at 6:49

answered Aug 24, 2016 at 6:44

lc.

117k21 gold badges164 silver badges190 bronze badges

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

2 Comments

conor Over a year ago

Accepted this answer as it answers my question most thoroughly and simplifies it - I don't in fact need dynamic SQL in this case.

lc. Over a year ago

I'm glad it helped. To answer the second half of your question, you can try yourself select @query to see what query is being composed. You'll see select [Name], test as ... which treats "test" as a column name. You'll need to wrap that in quotes - as the other answers here show. But then you introduce the problem of "what happens if @x has quotes in it", demonstrating the danger of dynamic SQL. For example try set @x = 'test'', Name, Name, Name, ''hahaha' with one of the other answers' queries!

Ranjana Ghimire · Accepted Answer · 2016-08-24 06:49:17Z

2

You were missing quotes. Thats it. Try below code.

 Declare @Query nvarchar(1000)
    Declare @x nvarchar(40)

    Set @x = 'test'
    Set @Query = 'Select [Name]
                         , ''' + @x + ''' as [TestCase]
                  From mytable'

    Exec (@Query)

answered Aug 24, 2016 at 6:49

Ranjana Ghimire

1,8451 gold badge14 silver badges22 bronze badges

2 Comments

conor Over a year ago

Thanks @Ranjana. Why do you need three single quotes surrounding this? I know two act as an escape, but not sure what three do.

Ranjana Ghimire Over a year ago

Simple idea is the select query you have created searches for column named test since that does not exist , it throws error. Since that is column value so you should pass it as 'test'. With ''' one quote is generated so '''+@x+''' becomes 'test' . with your query it would be as :'+@x+'-> test. Since test is not a column name so error occurs

TheGameiswar · Accepted Answer · 2016-08-24 06:43:28Z

1

Declare @Query nvarchar(1000)
Declare @x nvarchar(40)

Set @x = 'test'
Set @Query = 'Select [Name],'++''''+@x+''''+ ' as [TestCase]
              From mytable'

print @query

Output:
Select [Name],'test' as [TestCase] From mytable

answered Aug 24, 2016 at 6:43

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

2 Comments

conor Over a year ago

This worked but I don't understand what the extra + and single quotes do. Could you please add an explanation?

TheGameiswar Over a year ago

@conor:try this..select '','''',''''''

Collectives™ on Stack Overflow

SQL add a variable to a query

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related