Query question

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.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.|||Hi thanks for the response. It did seem like there would be a better way
other than the dynamic sql. Someone had suggested to me from this newsgroup
to use it so I went that route. No official SQL training so just learning by
trail and error.
"AnthonyThomas" wrote:
> 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.
>
>