Issues with parameter passing in a dynamic query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Issues with parameter passing in a dynamic query

All,<br /><br />I am facing a weird problem. When I use a dynamic sql like this:<br /><br />execute sp_executesql N’SELECT a.a_webc_url<br />FROM pfieldnet_property_table_s a join pfieldnet_property_table_r b<br />ON a.a_webc_url = b.a_webc_url<br />WHERE (b.a_expiration_date IS NULL OR b.a_expiration_date &gt;= getdate())<br />and a_effective_date BETWEEN DATEADD(m, -1 <br />,getdate()) AND getdate() <br />AND i_full_format = ”smhtml”<br />AND a.a_webc_url in (SELECT distinct c.a_webc_url<br />FROM pfieldnet_property_table_r as c, <br />pfn_check_content_assignment e <br />WHERE pfn_additional_loc = @location1 and c.a_webc_url = e.a_webc_url <br />and ( ims_fuid = ”ALL” OR ims_fuid = @terr_code1) ) <br />ORDER BY a_effective_date desc,r_creation_date desc’ ,<br />N’@terr_code1 varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@location1 varchar(80)’,<br />@terr_code1=’USERA’ , @location1=’8′<br /><br />I get 2 rows. I have passed USERA in terr_code1 variable<br /><br />Now when I hardcode this userid in the where condition like this:<br /><br />execute sp_executesql N’SELECT a.a_webc_url<br />FROM pfieldnet_property_table_s a join pfieldnet_property_table_r b<br />ON a.a_webc_url = b.a_webc_url<br />WHERE (b.a_expiration_date IS NULL OR b.a_expiration_date &gt;= getdate())<br />and a_effective_date BETWEEN DATEADD(m, -1 <br />,getdate()) AND getdate() <br />AND i_full_format = ”smhtml”<br />AND a.a_webc_url in (SELECT distinct c.a_webc_url<br />FROM pfieldnet_property_table_r as c, <br />pfn_check_content_assignment e <br />WHERE pfn_additional_loc = @location1 and c.a_webc_url = e.a_webc_url <br />and ( ims_fuid = ”ALL” OR ims_fuid =”USERA”) ) <br />ORDER BY a_effective_date desc,r_creation_date desc’ ,<br />N’@terr_code1 varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@location1 varchar(80)’,<br />@terr_code1=’USERA’ , @location1=’8′<br /><br />Note that the where condition in sub query has now changed to:<br />ims_fuid = ”ALL” OR ims_fuid =”USERA”<br /><br />I get a different set of results (5 rows). Can someone pls suggest what could be the culprit here. Interestingly it is only with a particular userid that I run into this issue. I have tried changing the parameter with other user ids and they work fine.<br /><br />Thanx<br /><br /><br />
Why are you running it as dynamic query.
You can run it as is.
Try it without sp_executesql. You are passing variable values in where clause so you can execute them directly without building dynamic query. Try this and post the result.
]]>