Re: Query about the LIKE statement

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Vince (nmvkPLEASERMVTHIS_at_vsnl.net)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 21:50:39 +0800

Uri,
Thanks for your quick response. The problem with the IN statement is that I
cannot do IN ('1%') unlike LIKE where I can do '1%'. I guess the best
solution is to declare some 20 parameters and set them to '%%' if there is
no value. If there is a value, I can put that in. I suppose there it won't
be such a hinder on performance.

Out of curiosity, why doesn't this work:
where ([COL] LIKE @Hello)

where @Hello is : 1% or [COL] like '2%

I thought this would be intrepreted as:
where ([COL] like '1% or [COL] like '2%'

I understand that this would be more on the realms of Dynamic SQL but was
wondering how SQL intreprets such variables. Also, I read something about
SQL Injection, isn't this similar to that?

Vince

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:O1EIzmi0EHA.3072@TK2MSFTNGP11.phx.gbl...
> Vince
> I think in that way you will have to build dynamic sql.
> As you know SQL Server does not support arrays as parameters for stored
> procedures.Look at below example, I am sure it will give an idea and you
> solve the problem.
>
> CREATE PROCEDURE sparray
> @array nvarchar(4000)
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @nsql nvarchar(4000)
> SET @nsql = '
> SELECT *
> FROM sysobjects
> WHERE name IN ( ' + @array + ')'
>
> PRINT @nsql
> EXEC sp_executesql @nsql
>
> END
> GO
>
> ---Usage
> EXEC sparray
> @array = '''sysobjects'',''sysindexes'',''syscolumns'''
> GO
>
>
>
>
> "Vince" <nmvkPLEASERMVTHIS@vsnl.net> wrote in message
> news:%23DZxwhi0EHA.2804@TK2MSFTNGP15.phx.gbl...
> > THanks Uri and Mal for your response but I don't know how many
parameters
> > there are. For instance, if the user chooses to get information
pertaining
> > to teacher Angel and James, I could have two parameters. But, there are
> > around 10 teachers and over 20 levels and the user could choose any
> > combination of them. This is why I ruled out using more parameters.
> >
> > Also, Mal, your method requires logic in regex. I can only say [A-Za-z]
> with
> > the teachers. What if the user wants information pertaining to Angel,
> Andrew
> > and James. It's hard to write all combinatations in regex and would be
> easy
> > if I could say where [COL] like 'James%' or [COL] like 'Angel%' or [COL]
> > like 'Andrew%'. If the user looks up only one teacher, I would have to
> make
> > the remaining two choices like '%%'. Is that the only way out? Declare a
> > maxmimum numer of parameters and if there is a value, send them or
> otherwise
> > use %%? Is this the best way? The reason why I need this in a stored
> > procedure is because I am using Cross Tab reports after the records are
> > obtained.
> >
> > Thanks a lot for responding.
> >
> > Vince
> >
> > "Mal .mullerjannie@hotmail.com>" <<removethis> wrote in message
> > news:1A2BC42E-9B04-439C-AAC4-5BD6FC0C2890@microsoft.com...
> > > This is an example
> > >
> > >
> > > create proc a (@myval varchar(99))
> > >
> > > as
> > >
> > > begin
> > >
> > > select * from Customers
> > > where postalcode like @myval
> > > end
> > >
> > > go
> > >
> > > a '1[0-2]%'
> > >
> > >
> > > Your query
> > >
> > > select * from TABLE where
> > > [COL] like 111% or [COL] like 112%.
> > >
> > >
> > > Modifed
> > >
> > > declare @variable varchar(99)
> > > set @variable = '11[1-2]%'
> > >
> > > select * from TABLE where
> > > [COL] like @variable
> > >
> > > Try that's , it's not tested.
> > >
> > > Mal
> > >
> > > "Vince" wrote:
> > >
> > > > If I have a procedure like (say):
> > > >
> > > > CREATE procedure SP_ABC(@PAR1 varchar(20) as
> > > > select * from TABLE where [COL] like @PAR1
> > > >
> > > > If I supply sp_ABC(111%), I get all rows where COL begins with 111.
> > > >
> > > > Now, I would like to pass multiple values to it, in other words I
need
> > to be
> > > > able to say sp_ABC(111%,112%) meaning select * from TABLE where
[COL]
> > like
> > > > 111% or [COL] like 112%.
> > > >
> > > >
> > > > Is there any way I can do this without using DSQL? The number of
such
> > values
> > > > could vary. I tried injecting values into it from the client side
> > > > application, like sending 111% OR [COL] LIKE 112% but that did not
> work.
> > > >
> > > > Any better solution?
> > > >
> > > > Thanks a lot.
> > > > Vince
> > > >
> > > >
> > > >
> >
> >
>
>

Relevant Pages

  • RE: SSIS package fail from Job
    ... We have not received your response. ... I recommend that you use SQL Profiler to monitor ... Microsoft Online Community Support ... where an initial response from the community or a Microsoft Support ...
    (microsoft.public.sqlserver.dts)
  • RE: Transaction Log filling up
    ... Thank you for your response. ... I already know how to shrink the log when it ... It used to be that under SQL 7 there was an option called "Truncate Log on ... Checkpoint" which I don't see as an option under SQL 2000. ...
    (microsoft.public.sqlserver.odbc)
  • Re: Tools with/for Express
    ... I had to start with SQL Server ... I then attempted to use Query Analyzer. ... No such luck with Enterprise Manager. ... I tried harder to get it started because your response told me I ...
    (microsoft.public.sqlserver.tools)
  • Re: combo box NotInList Event
    ... Yes my RowSourceType is set to Table/Query. ... Characters found after end of SQL statement. ... Response As Integer) ...
    (microsoft.public.access.modulesdaovba)
  • Re: Raptors 86 manager
    ... If this is in response to last Sunday's game ... Vince Carter trade. ...
    (alt.sports.basketball.nba.la-lakers)