Avoid Dynamic Query
-
hi,
declare @SearchSQL VARCHAR(4000)
SET @SearchSQL ='P.ProductName Like ''%'' AND (P.ProductValue BETWEEN 200 AND 1000)
AND P.Status != ''Sold'''
SELECT * FROM Product WHERE .... i wan to implement the above search string in this query w/o using dynamic query how could i do that ?
Abhijit - http://abhijitmore.wordpress.com
-
Couldn't understand the question clearly. In the scenario that you have specified, you don't need a dynamic SQL, you can simply write:
SELECT * FROM Product P WHERE
P.ProductName Like '%' AND (P.ProductValue BETWEEN 200 AND 1000)
AND P.Status != 'Sold'
Please specify if you are using dynamic SQL for some specific reason.
-
Okay, the scenario is i have custom filter, which stores the SearchTable, SearchColumnName & SearchParameterValue in table in different columns like Product in 1 column | ProductName in 2 column | % in 3rd column,
means dynamic search, now if i'll search for Product Table it should search for filter criteria provided..
Abhijit - http://abhijitmore.wordpress.com
-
What you are suggesting is very dangerous.
Who will populate your search table? What checks will you have in place to prevent something inefficient from being generated?
If you end up with
WHERE ProductName LIKE '%pod'or
ProductColor <> 'Red'You could get very poor performance (depending on how much data there is) as any indexes on these columns will not be used. SQL WHERE Clause Optimization is not something you can expect your users to understand, and my recommendation would be a major rethink of your application design.
If you have to do it this way make sure the user interface has plenty of validation to prevent inefficient queries from being generated.
-
Abhijit More (8/20/2008)
Okay, the scenario is i have custom filter, which stores the SearchTable, SearchColumnName & SearchParameterValue in table in different columns like Product in 1 column | ProductName in 2 column | % in 3rd column,means dynamic search, now if i'll search for Product Table it should search for filter criteria provided..
In such a situation, you are not left with much of an option, you will have to deal with the RED QUERY.
-
One way to handle this type of situation is in a stored procedure, have a parameter for each column that could have a filter on it, with a default value of NULL. Then in your query, do:
[font="Courier New"]WHERE (@ProductName IS NULL OR P.ProductName Like @ProductName + '%')
AND (@MinValue IS NULL OR P.ProductValue >= @MinValue)
AND (@MaxValue IS NULL OR P.ProductValue <= @MaxValue)
AND (@IncludeSold = 1 OR P.Status != 'Sold')[/font]
-
charshman (8/20/2008)
One way to handle this type of situation is in a stored procedure, have a parameter for each column that could have a filter on it, with a default value of NULL. Then in your query, do:[font="Courier New"]WHERE (@ProductName IS NULL OR P.ProductName Like @ProductName + '%')
AND (@MinValue IS NULL OR P.ProductValue >= @MinValue)
AND (@MaxValue IS NULL OR P.ProductValue <= @MaxValue)
AND (@IncludeSold = 1 OR P.Status != 'Sold')[/font]
This is an elegant solution, but it won't perform well.
I have described this in OR Condition Performance.
I'm hoping that SQL Server 2008 handles this differently, but haven't had the chance to test it yet.
-
Man the requirement of this fellow is that his table name is also dynamic. 😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply