Sql criteria filter as comma del string

Question

I have a stored proc defines as follows.

PROCEDURE [dbo].[GetSales](@dateFilter nvarchar(50))
AS
BEGIN
     SELECT sum(Amount)
     FROM Sales
     WHERE SalesDate in (' + @dateFilter  + ')
     group by SalesDate 

END

to select data from this table

Id SalesDate     Amount
1   Apr-2010       40.25
2   May-2010       12.10
3   Jun-2010        2.50

I used Execute sp command and passed this text 'Mar-2010','Apr-2010'

The call generated this code and retured nothing.

DECLARE @return_value int

EXEC    @return_value = [dbo].[GetSales]
    @dateFilter = N'''Mar-2010'',''Apr-2010'''

SELECT  'Return Value' = @return_value

But this (call statement X) works

 SELECT sum(Amount)
     FROM Sales
     WHERE SalesDate in ('Mar-2010','Apr-2010')
     group by SalesDate 

returning

(No column name)
40.25

How can I fromat the parameter filter so the stored proc uses it as statement X above?

Answer 1

You have two options. Either create the entire SQL as nvarchar and use EXEC(statement) or parse the delimited input string into a temp table and then use WHERE SalesDate in(SELECT value FROM #t)

E.g.

EXEC(N'SELECT sum(Amount) FROM Sales WHERE SalesDate in ('+ @DateFilter + N') group by SalesDate')

This approach can provide a loophole for SQL Injection attacks.

Answer 2

CREATE PROCEDURE GetSales
    @dateFilter nvarchar(50)
AS
BEGIN
   execute('SELECT sum(Amount) FROM Sales WHERE SalesDate in (' + @dateFilter + ') group by SalesDate');
END

Answer 3

The dynamic sql solution is messy and hard to maintain. IMO it's better to parse the list into a temp table or variable and join it.

http://stackoverflow.com/questions/3516064/creating-a-sql-table-from-a-comma-concatenated-list/3516162#3516162

Answer 4

I found the answer from http://www.sommarskog.se/arrays-in-sql-2005.html

ALTER FUNCTION [dbo].[iter$simple_intlist_to_tbl](@list nvarchar(MAX)) RETURNS @tbl TABLE (PeriodFilter nvarchar(10)) AS BEGIN DECLARE @pos int, @nextpos int, @valuelen int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0 BEGIN SELECT @nextpos = charindex(',', @list, @pos + 1) SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@list) + 1 END - @pos - 1 INSERT @tbl (PeriodFilter) VALUES (substring(@list, @pos + 1, @valuelen)) SELECT @pos = @nextpos END RETURN END

this proc ALTER PROCEDURE [dbo].[GetSales](@dateFilter nvarchar(50)) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT sum(Amount) FROM Sales WHERE SalesDate in ( select * from dbo.iter$simple_intlist_to_tbl(@dateFilter)) group by SalesDate

END

the cal

DECLARE @return_value int

EXEC @return_value = [dbo].[GetSales] @dateFilter = N'May-2010,Apr-2010'

SELECT 'Return Value' = @return_value

GO