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?
This question is related to
sql
sql-server
sql-server-2005
tsql
dynamic-sql
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)
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
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)
)
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.
Source: Stackoverflow.com