SQL parameterized query for "BETWEEN" and "IN" operator not working

Question

SELECT top 10 * 
  FRPM Events  
    WHERE (StartDayID between @p0 
             AND BusinessEventCode in @p1),
       N'@p0 nvarchar(4000),@p1 nvarchar(4000)',@p0=N'20110701 
           and 20140724',@p1=N'HighVoltage,LowVoltage'

StartDayID is an integer, BusinessEventCode is a nvarchar column.

Did not find any help on the internet for BETWEEN and IN operator..

for the "in" part of the statement, you could separate your comma separated list into a temp table/variable and then using in (select whatever from #tempTable). For the dates, you could use two variables startDate and endDate, and use "between startDate and endDate" — Kritner, Sep 05 '14 at 14:02
This is a auto generated query from nhibernate, i dont have much control over the query or the parameters. I thought i am making a syntactical mistake putting a single quote here and ther. — Ananth Ram, Sep 07 '14 at 17:41

score 0 · Answer 1 · answered Sep 05 '14 at 14:04

0

Change where condition in query like this:

...where (StartDayID between @p0 and @p1) AND BusinessEventCode in (@p2);

@p0 = 20110701
@p1 = 20140724
@p2 = HighVoltage,LowVoltage'

answered Sep 05 '14 at 14:04

Amardeep Kumar

31
2

This is a auto generated query from nhibernate, i dont have much control over the query or the parameters. I thought i am making a syntactical mistake putting a single quote here and ther. – Ananth Ram Sep 07 '14 at 17:41

score 0 · Answer 2 · answered Sep 05 '14 at 14:18

As it was pointed out in my comment, you could do the

WHERE (StartDayID between @p0

part of your query by splitting out @p0 into two variables as such:

declare @startDate datetime
declare @endDate datetime

select @startdate = 20110701, @endDate = 20140724
....
WHERE (StartDayID between @startDate and @endDate)

the IN part of your query is a bit trickier, and you could need to split the individual values out into a temp/variable table in order to process them in an IN statement.

You could do this with a split function such as (taken from http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql/):

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

Then in your query where you have:

....
AND BusinessEventCode in @p1),

this would become:

@p1=N'HighVoltage,LowVoltage'

....
AND BusinessEventCode in (SELECT Data FROM dbo.Split(@p1, ','))),

Note that if going this route, you might want to rethink your delimiter, as "," can be common in text, where something like a pipe (|) would potentially be less common.

score 0 · Answer 3 · answered Sep 05 '14 at 14:39

0

if you have to use those parameters you could do it with dynamic SQL. it wouldn't be my first recommendation but it might be feasible depending on your use case.

declare @p0 nvarchar(100)=N'20110701 and 20140724';
declare @p1 nvarchar(100)=N'''HighVoltage'',''LowVoltage''';
declare @SQL nvarchar(Max);

set @SQL = 'SELECT TOP 10 * '+
    'FROM Events '+
        'WHERE StartDayID between '+ @p0 + 
        ' AND BusinessEventCode IN(' + @p1 + ')'

exec sp_executesql @SQL

answered Sep 05 '14 at 14:39

AndyM

1
1

Hi AndyM, just trying to understand "how different is a parameterized query when compared to dynamic sql" both ways we are sending some variables to a query. Also, i want to reiterate the same comment for your info, This is a auto generated query from nhibernate, i dont have much control over the query or the parameters. I thought i am making a syntactical mistake putting a single quote here and ther. – Ananth Ram Sep 07 '14 at 17:47

SQL parameterized query for "BETWEEN" and "IN" operator not working

3 Answers3