I've recently learned that it's much better to use sp_executesql with parameters instead of Execute for dynamic queries. The problem I am facing is that I have different parameters that are passed into my WHERE clause depending on the filters user selected
on the website. Below is the example of my query. In this query there are 2 parameters: Category and Price. Sometime Price should be used in the WHERE and sometimes it shouldn't. But how can I define one SP to handle different scenarious?
Thank you in advance.
DECLARE @Category nvarchar(40)
DECLARE @Price int
DECLARE @SQLString NVARCHAR(3000)DECLARE @ParmDefinition NVARCHAR(1000)
SET @SQLString = 'SELECT distinct TOP 5 Products_Brand As Brand, Count(DISTINCT Products_pID) As CNT '
SET @SQLString = @SQLString +
'FROM _dta_mv_0_9987 WITH (NOEXPAND) ' +
'LEFT OUTER JOIN Address ON ProductsLocation_aID = Address.aID ' +
'WHERE ((ProductsLocation_aID = -1) OR (Address.City LIKE 'gft' AND Address.State LIKE 'tx' )) ' +
'and products_subcategory = @ParamCategory ' +
'and productslocation_price>@ParamPrice' +
' GROUP BY Prod
View Complete Post