StealthRT StealthRT - 6 months ago 14
SQL Question

declare variable for query string

Hey all, 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


Possible to do?

David

Answer

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.

Comments