Declare Variable for a Query String

Question

I was wondering if there was a way to do this in MS SQL Server 2005:

  DECLARE @theDate varchar(60)
  SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59'''

  SELECT    AdministratorCode, 
            SUM(Total) as theTotal, 
            SUM(WOD.Quantity) as theQty, 
            AVG(Total) as avgTotal, 
            (SELECT SUM(tblWOD.Amount)
                FROM tblWOD
                JOIN tblWO on tblWOD.OrderID = tblWO.ID
                WHERE tblWO.Approved = '1' 
                AND tblWO.AdministratorCode = tblWO.AdministratorCode
                AND tblWO.OrderDate BETWEEN @theDate
            )
 ... etc

Is this possible to do?

Ecclesiology

learn.microsoft.com/en-us/sql/relational-databases/… – Riggall

Answer 1

It's possible, but it requires using dynamic SQL.
I recommend reading The curse and blessings of dynamic SQL before continuing...

DECLARE @theDate varchar(60)
SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59'''

DECLARE @SQL VARCHAR(MAX)  
SET @SQL = 'SELECT AdministratorCode, 
                   SUM(Total) as theTotal, 
                   SUM(WOD.Quantity) as theQty, 
                   AVG(Total) as avgTotal, 
                  (SELECT SUM(tblWOD.Amount)
                     FROM tblWOD
                     JOIN tblWO on tblWOD.OrderID = tblWO.ID
                    WHERE tblWO.Approved = ''1''
                      AND tblWO.AdministratorCode = tblWO.AdministratorCode
                      AND tblWO.OrderDate BETWEEN '+ @theDate +')'

EXEC(@SQL)

Dynamic SQL is just a SQL statement, composed as a string before being executed. So the usual string concatenation occurs. Dynamic SQL is required whenever you want to do something in SQL syntax that isn't allowed, like:

• a single parameter to represent comma separated list of values for an IN clause
• a variable to represent both value and SQL syntax (IE: the example you provided)

EXEC sp_executesql allows you to use bind/preparedstatement parameters so you don't have to concern yourself with escaping single quotes/etc for SQL injection attacks.

Graduation

I think this is the most correct answer. I've been using SQL Server 2005 as well recently, and using a variable for query string replacement like the OP wants is not possible (generates syntax errors). Variables cannot include both syntax and data types, as @Ponies says. Dynamic SQL is the way to go for building queries in SQL Server via strings. Just remember to be careful about your quotes and types! The string you execute requires some types, like datetime or int, to be converted or cast for string concatenation. – Diazonium

Answer 2

DECLARE @theDate DATETIME
SET @theDate = '2010-01-01'

Then change your query to use this logic:

AND 
(
    tblWO.OrderDate > DATEADD(MILLISECOND, -1, @theDate) 
    AND tblWO.OrderDate < DATEADD(DAY, 1, @theDate)
)

Waki

Hang on. That can't be the answer if the question shows clearly two different dates. How did you code it in the end @Ecclesiology ? Where's the '2010-08-31' date in the answer? Also, the question asks clearly if you can use DECLARE variables to substitute code into another SELECT statement. The proper answer is below. – Gripsack

Answer 3

Using EXEC

You can use following example for building SQL statement.

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
EXEC (@sqlCommand)

Using sp_executesql

With using this approach you can ensure that the data values being passed into the query are the correct datatypes and avoind use of more quotes.

DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

Reference

Riggall

Answer 4

I will point out that in the article linked in the top rated answer The Curse and Blessings of Dynamic SQL the author states that the answer is not to use dynamic SQL. Scroll almost to the end to see this.

From the article: "The correct method is to unpack the list into a table with a user-defined function or a stored procedure."

Of course, once the list is in a table you can use a join. I could not comment directly on the top rated answer, so I just added this comment.

Tees