SQL Server query showing error when applying search between condition

Question

My table data is like this:

I'm using this query:

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)

 SELECT @cols = STUFF((SELECT distinct ','+ QUOTENAME('COMPLETE_' + cast(row_number() over(partition by CID order by CID) as varchar(10))) 
                       FROM allleads 
                       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 

 SET @query = 'SELECT CustomerName, address, CID, ' + @cols + 
              ' FROM (SELECT CustomerName, address, CID, COMPLETE,''COMPLETE_''+ CAST(row_number() over (partition by CID order by RecordDate) as varchar(10)) val from allleads) x PIVOT (MAX(COMPLETE) for val in (' + @cols + ')) p ' execute(@query)

is working fine.

But when I add a search condition between date like this:

DECLARE @cols AS NVARCHAR(MAX),
       @query AS NVARCHAR(MAX),
       @dt AS VARCHAR(10),
       @dt1 AS VARCHAR(10) set
       @dt='2017/05/18'  set 
       @dt1='2017/07/10'
select @cols = STUFF((SELECT distinct ','+
                   QUOTENAME('COMPLETE_' + 
                  cast(row_number() 
                 over(partition by CID order by CID) as varchar(10))) 
                 from allleads FOR XML PATH(''), 
                 TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') 

    set @query = 'SELECT CustomerName,address,CID, ' + 
        @cols + ' from (select CustomerName,address,CID, COMPLETE,''COMPLETE_''+ 
        cast(row_number()
        over(partition by CID order by RecordDate) as varchar(10))
  val from allleads **where convert(varchar(10),RecordDate,111) between '+@dt+' and '+@dt1+'**) x pivot(max(COMPLETE) for val in (' + @cols + ')) p ' execute(@query)

then it is showing an error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2017/05/19' to data type int.

Please help.

Thanks in advance.

What is the **exact** value of `@query`? – mjwills Aug 06 '17 at 11:41 — mjwills, Aug 06 '17 at 11:41

score 0 · Accepted Answer · answered Aug 06 '17 at 11:49

You should really use parameters for constants in a query . . . and use sp_executesql to pass them in.

In your case, the problem is missing quotes around the dates. Instead of:

between '+@dt+' and '+@dt1+'

You can do:

between '''+@dt+''' and '''+@dt1+'''

In other words, the error is occurring when you execute the code, not when you are defining the strings.

You should also learn to write your code in a less sloppy manner. I don't see how even you can read it, much less anyone else.

SQL Server query showing error when applying search between condition

1 Answers1