>If I turned it into a static SQL, I'd have to write 6 similar, yet different SQL all surrounded by IF's based on whether the user wanted the report for one person or all, date range - or not, or upto this date or after this date.
>
>But yeah - I missed the extraneous 'end'
>
You need to first master static SQL before you can attempt dynamic SQL. For dynamic SQL use sp_executeSQL and parameters.
Read this
Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) long and comprehensive article by Erland Sommarskog
>>If you turned this into static SQL, did you replace single quote correctly? There is an extra END after RevCnt which should be removed, e.g.
>>
>>
>>Select e.Category, e.EnteredBy,
>>rtrim(u.UserLastName) + ',' + u.UserFirstName as FullName,
>>COUNT( case when r.checkid =0 then 1 end) as chkcnt,
>>COUNT(case when r.CheckId= -1 then 1 end) as revcnt
>>
>>FROM ExpMaster as e
>>INNER JOIN ReviewHistory as r
>>ON r.XPID = e.origid
>>INNER JOIN UserFile as u
>>ON u.UserInit = e.EnteredBy
>>WHERE e.EnteredBy = 'DAB' and e.EnteredDate between @mbDate and @mbEDate
>>group by e.Category, e.EnteredBy, u.UserLastName, u.UserFirstName
>>order by e.EnteredBy
>>
>>
>>If you want to keep this dynamic, then use sp_executeSQL and parameters as is, take a look at this blog
>>
Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec
If it's not broken, fix it until it is.
My Blog