February 11, 2009 at 6:08 am
I have a stored procedure that has a query in it like this
Declare @sql nvarchar(MAX)
Set @sql =
'SELECT SalesPerson, State, SUM(Sales) as TotalSales, SUM(Expense) as Expenses, region, group
FROM tblSales
Where tblSales.[Region] IN (' + @Region+ ') and tblSales.State IN (' + @State + ')
and tblSales.[DateofSales]
BETWEEN
COALESCE(NULLIF(' + @StartDate + ', '+ '''' + '''' +' ),tblSales.[DateofSales])
AND
COALESCE(NULLIF(' + @EndDate + ','+ '''' + '''' +'),tblSales.[DateofSales])
ORDER by tblSales.SalesPerson
EXEC (@SQL)
the issue I'm having is that the dates are filtering correctly, if I pass in StartDate = 1/1/2009 and EndDate = 2/10/2009, its pulling back all data, meaning, I'm seeing data with dates from 2000, 2004, 2008 and so on. What am I missing or need to get the dates to filter correctly so that I only see data between and including the startdate and enddate?
February 11, 2009 at 6:23 am
You shouldn't need to use dynamic SQL at all. If @Region and @State are some sort of comma delimited string, I would parse them into table variables and join on them instead of using an IN clause.
February 11, 2009 at 6:24 am
I figured it out, I had to fix my quotes around my parameter.
February 11, 2009 at 6:26 am
How would I do that?
The data is being passed as such
@Region = NE, SE, NW, SW, MA and so on
@State = 1,2,3,4,5,6,7,8,9,10. . . . .
how could I do it like you recommended? Is there a difference such as performance, or any benefit?
February 11, 2009 at 6:36 am
Here is some code that does delimiting:
DECLARE @String VARCHAR(100)
DECLARE @Delimiter VARCHAR(2)
SELECT
@String = 'NE, SE, NW, SW, MA',
@Delimiter = ', '
SELECT SUBSTRING(@String+@Delimiter, n,
CHARINDEX(@Delimiter, @String+@Delimiter, n) - n)
FROM tally
WHERE n <= LEN(@String)
AND SUBSTRING(@Delimiter + @String,
n, 1) = @Delimiter
ORDER BY n
If you insert the results for each comma string you have into a table variable then you can simply join to the table.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 11, 2009 at 6:59 am
I would do something like this:
Here I'm setting variables and adding comma before and after the input strings
DECLARE @State VARCHAR(100), @Region VARCHAR(100)
SELECT @State = '1, 2, 3, 4, 5, 6, 7', @Region = 'NE, SE, NW, SW, MA'
SET @State = ',' + @State + ','
SET @Region = ',' + @Region + ','
DECLARE @RegionTable TABLE (Region CHAR(2))
DECLARE @StateTable TABLE (State INT)
I'm using two CTEs to create tally tables on the fly. I assume that you don't have one in your database. A tally table can be used instead of looping.
;WITH Tally (N) AS
(
SELECT TOP (LEN(@State))
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
INSERT INTO @StateTable
SELECT SUBSTRING(@State,N+1,CHARINDEX(',',@State,N+1)-N-1)
FROM Tally
WHERE N < LEN(@State)
AND SUBSTRING(@State,N,1) = ','
;WITH Tally (N) AS
(
SELECT TOP (LEN(@Region))
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
INSERT INTO @RegionTable
SELECT SUBSTRING(@Region,N+1,CHARINDEX(',',@Region,N+1)-N-1)
FROM Tally
WHERE N < LEN(@Region)
AND SUBSTRING(@Region,N,1) = ','
Once the two table variables are filled you can simply join to them.
SELECT SalesPerson, STATE, SUM(Sales) AS TotalSales, SUM(Expense) AS Expenses, region, GROUP
FROM tblSales a INNER JOIN
@StateTable b ON a.State = b.State INNER JOIN
@RegionTable c ON a.Region = c.Region
WHERE tblSales.[DateofSales] BETWEEN NULLIF(@StartDate, '') AND NULLIF(@EndDate,'')
ORDER BY tblSales.SalesPerson
You should get better performance because this is a more set based solution. I suggest reading articles on this site from Jeff Moden. I've completely changed the way I approach SQL problems based on his articles. Thanks Jeff!!
Greg
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply