Pivot table not worked by dynamic parameter

Question

I wrote this query:

declare @startdate nvarchar(10) = '2016/01/01',
        @enddate nvarchar(10) = '2019/10/04',
        @cols nvarchar(max),
        @strsql nvarchar(max);

select 
    @cols =
    (select distinct QUOTENAME(maintenancename)+','
       from (select distinct maintenancename 
          from Table_maintenancetype where coalesce(maintenancename, '') != '') AS t1   
       for xml path('')    
    ) 
set @cols=LEFT(@cols,LEN(@cols)-1)

set @strsql=N'select *
from
(select sum(timedurationok) as wt, maintenancetype,tcode
            from Table_maintenancereport    
            where ((svok=1 and need_netok=0) or (svok=1 and netok=1 and need_netok=1))and tcode<>-1
            and dateendrole >='+ @startdate+ 'and dateendrole<='+ @enddate+'
            group by maintenancetype,tcode
) d 
pivot
(sum(wt)
for maintenancetype in ('+@cols+')
) piv
'

exec sp_executesql @strsql

When execute this no record return but when replace @startdate and @enddate by '2016/01/01','2019/10/04'

The query works fine I don't know what the problem is... and when use between in query I get error for can not convert nvarchar to data int 2016

[Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) - you should always use the most appropriate data type - that's what they're there for, after all! `@startdate` and `@enddate` are **clearly** dates - so you should **NOT** declare them as `nvarchar(10)` - use `DATE` or `DATETIME2(n)` instead! — marc_s, Nov 02 '19 at 09:50

score 3 · Accepted Answer · answered Nov 02 '19 at 09:40

first, do a print @strsql to show your dynamic query and check.

You will see that your query is dateendrole >= 2016/01/01

the date should be in single quote dateendrole >= 2016/01/01'

your query should be

and dateendrole >= '''+ @startdate+ ''' and dateendrole<= '''+ @enddate+'''

a better way is to use parameter with sp_executesql

declare both the variable as date data type since it is going to hold date value

declare @startdate date = '20160101',
        @enddate   date = '20191004'

and in your dynamic query, just use the variable

and dateendrole >= @startdate and dateendrole <= @enddate

finally when calling sp_executesql, pass in the variable. And don't forget to print out the verify your query

print @strsql
exec sp_executesql @strsql, N'@startdate date, @enddate date', @startdate, @enddate

Any answer that tells someone to stop (insecurely) injecting a parameter in a query gets a +1 from me. — Thom A, Nov 02 '19 at 09:44

Pivot table not worked by dynamic parameter

1 Answers1