Re: Query question
From: AnthonyThomas (Anthony.Thomas_at_CommerceBank.com)
Date: 01/06/05
- Next message: Florence Lee: "My database become very slow, what step do i need to take??"
- Previous message: AnthonyThomas: "Re: How do I enable this permently?"
- In reply to: Paul: "Query question"
- Next in thread: Paul: "Re: Query question"
- Reply: Paul: "Re: Query question"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 5 Jan 2005 19:54:31 -0600
The CASE statement is there to include or exclude that condition in the
query; however, it is a lousy implementation. You are obviously using
dynamic sql inside of a stored procedure. Other than a convenient place to
put it, dyanmical sql inside a proc reduces the effectiveness of using
stored procedure.
The '%' before and after the passed in parameter are wildcard characters
that allow any string as a substitute. So, any string plus parameter plus
any string becomes the search condition. You are gauranteed to do a table
scan or clustered index scan as that criteria could never be supported by an
index.
As a better solution, try something more like this:
SELECT Col1, Col2, ..., Coln
FROM Tab1 JOIN Tab2
ON Tab1.Key1 = Tab2.Key1
AND Tab1.Key1 = Tab2.Key2
...
AND Tab1.Keyn = Tab2.Keyn
...
...
JOIN Tabn
ON ...
WHERE criterion1 AND criterion2 ... AND criterionN
AND (@stringvar IS NULL
OR TabX.ColX LIKE (@stringvar + '%')
)
This is executed directly. There is no need for a variable nor the use of
the EXEC(@var) function. TabX.ColX can be indexed and used if it is highly
selectable. The query execution plan can be reused.
Hope this helps.
Sincerely,
Anthony Thomas
-- "Paul" <Paul@discussions.microsoft.com> wrote in message news:B39C178F-3ABF-4EB2-99F1-3BEA896DC966@microsoft.com... Hi I have a large stored procedure that performs several queries conditionally on a database depending on values that are passed into the procedure. Anyhow I am using a string search and have set up SELECT @stringvar='%' + @stringvar + '%' then have case when @stringvar IS NOT NULL then 'AND (table.field LIKE @stringvar)' ELSE '' It works fine just could not remember why I needed to have the SELECT @stringvar='%' + @stringvar + '%' statement. thanks, -- Paul G Software engineer.
- Next message: Florence Lee: "My database become very slow, what step do i need to take??"
- Previous message: AnthonyThomas: "Re: How do I enable this permently?"
- In reply to: Paul: "Query question"
- Next in thread: Paul: "Re: Query question"
- Reply: Paul: "Re: Query question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
