CODEWITHSUNDEEP
sqlt-sqlsql-server-2008stored-procedures
Ashish
Ashish

Reputation: 2564

Using LIKE with IN / OR in Stored Procedure

I am trying to create a stored procedure in SQL Server 2008 where I am trying to replicate a simple query of

SELECT Col1, Col2 
FROM Table
WHERE Col1 LIKE 'A%'
    OR Col1 LIKE 'B%'
    OR Col1 LIKE 'C%'

as

CREATE PROCEDURE usp_MySP
    @ColValues varchar(100) = NULL
AS
SELECT Col1, Col2 
FROM Table
WHERE (@ColValues IS NULL OR Col1 LIKE (????))

Unable to replace ??. Basically I want to use LIKE with IN / OR. Any help?

Upvotes: 2

Views: 3005

Answers (3)

a1ex07
a1ex07

Reputation: 37382

Will it work for you :

WHERE  Col1 LIKE '[A,B,C]%'
// or for A,B,C you can just write
WHERE  Col1 LIKE '[A-C]%'

Upvotes: 1

Johan
Johan

Reputation: 76723

Is this what you meant?

CREATE FUNCTION dbo.ParseString (@string varchar(500), @delimeter char(1))
RETURNS @valuelist table (
  -- columns returned by the function
  value nvarchar(255) NOT NULL
)
begin
  declare @pos int
  declare @piece varchar(500)

  –- Need to tack a delimiter onto the end of the input string if one doesn’t exist
  if right(rtrim(@string),1) <> @delimiter
   set @string = @string  + @delimiter

  set @pos =  patindex('%,%' , @string)
  while @pos <> 0
  begin
    set @piece = left(@string, @pos – 1)

    insert into @valuelist (value) VALUES (@piece)

    set @string = stuff(@string, 1, @pos, '')
    set @pos =  patindex('%,%' , @string)
  end
end

CREATE PROCEDURE usp_MySP
    @ColValue1 varchar(100) = NULL
    ,@ColValue2 varchar(100) = NULL
AS
SELECT distinct Col1, Col2 
FROM Table
inner join parsestring('a,b,c')
WHERE col1 like concat(value,'%');

Upvotes: 0

tenfour
tenfour

Reputation: 36896

If the number of parameters is unknown, then you will need to do a table operation. Basically something like

select
    col1,
    col2
from
    Table t
    inner join MyParameters p on (t.Col1 like p.Query)

In order to generate table MyParameters, you can either construct it in your code, or use the new table-valued parameters in 2008.

Upvotes: 3

Related Questions