Re: Query question
From: Paul (Paul_at_discussions.microsoft.com)
Date: 01/06/05
- Next message: Jeff: "Create Disaster recovery site (Seperate physical location)"
- Previous message: harvinder: "RE: Update Stats"
- In reply to: AnthonyThomas: "Re: Query question"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 6 Jan 2005 07:49:04 -0800
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.
>
>
>
- Next message: Jeff: "Create Disaster recovery site (Seperate physical location)"
- Previous message: harvinder: "RE: Update Stats"
- In reply to: AnthonyThomas: "Re: Query question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
