Doing away with like '%%'

Here's the setup

@.searchcondition varchar(50) --is a parameter passed into a stored procedure

IF(@.searchconditions is null)

BEGIN

SET @.searchcondition = '%'

END

ELSE

BEGIN

SET @.searchcondtion = '%' + @.searchcondition + '%'

END

select * from sometable

where somecolumn like @.searchcondition

Now the procedure is fine if the user passes a string, but if it's null, it seems really inefficient to search for '%'. Is there a generalized approach, other than say putting slightly different versions of the query in an if block?

Have you considering using the Fulltext features in SQL Server 2005 rather than LIKE?|||I'm trying to make minor optimizations to a setup I do not have full control of. Thanks for the pointer to the fulltext stuff though, I'll definitely be using that on my future projects.|||

Did you already try the following:

select * from sometable

where (somecolumn like @.searchcondition) or (@.searchcondition is null)

?

|||Nice, works like a charm. Thanks for the tip.

Does the parameter of a report has has length limitation?

Hi,
I tried to pass a long sql statement(about 2000 characters) as the parameter
of a report by ReportingService.Render() function, but it always encountered
an exception.
So What's is the max length of a parameter by using web service method?
Thanks.What kind of exception?
What version of RS ?
As far as I know there is no limit is parameter length, especially for web
service method.
But there is definitly a problem with HTML4.0 rendering if a query string is
above 2K in length in RS 2000. RS chocks on that.
I'm using a work around by storing sql statement (parameters) in a separate
table and pass a returned record id from that table
to the reporting server. a stored procedure or a set of sql statements can
retreive that values based on passed id and execute them.
Kind of two step execution, but it works for long parameters.
If you POST to RS - there is no such thing as a query string, but HTML4.0
rendering is still a problem. See above.
"David Zhu" <DavidZhu@.discussions.microsoft.com> wrote in message
news:0BBD7B75-DACB-4710-B863-BCDF6C59E996@.microsoft.com...
> Hi,
> I tried to pass a long sql statement(about 2000 characters) as the
> parameter
> of a report by ReportingService.Render() function, but it always
> encountered
> an exception.
> So What's is the max length of a parameter by using web service method?
> Thanks.
>|||Thank you very much!
"Oleg Yevteyev" wrote:
> What kind of exception?
> What version of RS ?
> As far as I know there is no limit is parameter length, especially for web
> service method.
> But there is definitly a problem with HTML4.0 rendering if a query string is
> above 2K in length in RS 2000. RS chocks on that.
> I'm using a work around by storing sql statement (parameters) in a separate
> table and pass a returned record id from that table
> to the reporting server. a stored procedure or a set of sql statements can
> retreive that values based on passed id and execute them.
> Kind of two step execution, but it works for long parameters.
> If you POST to RS - there is no such thing as a query string, but HTML4.0
> rendering is still a problem. See above.
> "David Zhu" <DavidZhu@.discussions.microsoft.com> wrote in message
> news:0BBD7B75-DACB-4710-B863-BCDF6C59E996@.microsoft.com...
> > Hi,
> >
> > I tried to pass a long sql statement(about 2000 characters) as the
> > parameter
> > of a report by ReportingService.Render() function, but it always
> > encountered
> > an exception.
> >
> > So What's is the max length of a parameter by using web service method?
> >
> > Thanks.
> >
>
>