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

User · Answer

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

User · Answer

DECLARE  theDate DATETIME SET  theDate    2010-01-01    Then change your query to use this logic   AND        tblWO OrderDate  gt  DATEADD MILLISECOND  -1   theDate       AND tblWO OrderDate  lt  DATEADD DAY  1   theDate

User · Answer

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

User · 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

[sql] Declare Variable for a Query String

Using EXEC

Using sp_executesql

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql

Examples related to dynamic-sql