Click here to Skip to main content
15,902,635 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a dynamic stored procedure and have to add where clause dynamically in case statement in SQL Server 2008.

My procedure is as below: -

SQL
CREATE PROCEDURE SPGETDATA
    @STRNAME NVARCHAR(100),
    @STRCODE NVARCHAR(100)
    
    AS
    BEGIN
    SELECT  myTable.*
    FROM myTable
    WHERE 
    IsDELETED = 0  
    AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '' THEN 
    '%'+ @STRNAME + '%' ELSE '%%' END 
    AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '') THEN 
    '%' + @STRCODE + '%'  ELSE '%%' END** 
    
    END

The user can select either @strname or @strcode. But not both at a time.

In that case one like statement is ok but the alternative is always a burden over query because it will always be as

SQL
@STRNAME like '%%'
or as below

SQL
@STRCODE like '%%'

Now if i use this approach, will compiler will cost some time to search like '%%' even there is nothing to match or it will bypass it and cost nothing. I checked the execution plan also but it displays nothing for the like clause.
Hence i have to use this in webApps so speed of the sp has to consider. And the table has millions of rows.

Any one who can help please?
Please help?
Posted
Updated 5-Dec-11 0:55am
v8
Comments
Amir Mahfoozi 5-Dec-11 6:53am    
You can try creating indexes on those columns and see how much it boosts the search performance.

How about replacing AND with OR?

FROM:
SQL
WHERE
    IsDELETED = 0
    AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '' THEN
    '%'+ @STRNAME + '%' ELSE '%%' END
    AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '') THEN
    '%' + @STRCODE + '%'  ELSE '%%' END**

TO:
SQL
WHERE
    IsDELETED = 0
    AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '' THEN
    '%'+ @STRNAME + '%' ELSE '%%' END
    OR STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '') THEN
    '%' + @STRCODE + '%'  ELSE '%%' END**


Regards,
Eduard
 
Share this answer
 
Comments
Gopal Krishna Ranjan 5-Dec-11 4:42am    
But i want to know will compiler cost some time if we use like '%%' (empty) inside. Like prevents DB from using indexes but i have to use it in one case so i want to use it in another case also. But i am not confirm that will it cost some time if there is nothing inside wildchar or it will bypass by compiler.
This is a really nice article on building 'Search' type procedures where you have multiple parameters

http://www.sommarskog.se/dyn-search-2005.html[^]

I normally build my searches along the line of search_orders_1 (dynamic SQL)

e.g.

http://www.sommarskog.se/dynsearch/search_orders_1.sp[^]

Personally, I find this gives the best performance and maintainability - but there are various options that are documented there.
 
Share this answer
 
Comments
Gopal Krishna Ranjan 5-Dec-11 7:29am    
Thanks dear.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900