[dba-SQLServer] passing IN search condition to stored procedure

You can use this function:

create function dbo.fn_ParseString2VarCharTable (
 @string   varchar(8000)                      ) returns @tbl table (
   RecordID  int identity(1,1) not null primary key clustered,
   [String]  varchar(8000)   not null           ) as
   begin
      declare @spos int, @pos int
      set @string = replace(replace(@string, ', ', ','), ' ,', ',')
      set @spos = 1
      set @pos = 100
      while (@pos) > 0 begin
         set @pos = charindex(',', @string, @spos)
         insert @tbl ([String])
            select
               ltrim (
                  rtrim (
                     substring (
                        @string, @spos,
                           case
                              when (@pos - @spos) <= 0 then
datalength(@string) + 1
                              else @pos
                           end - @spos
                     )
                  )
               )
         set @spos = @pos + 1
      end
      return
   end

GO

Then you'd do a join instead of IN:

SELECT * FROM table t 
inner join dbo.fn_ParseString2VarCharTable(@CustCode) f
on [CustCode] = f.[String]

Robert Djabarov
SQL Server & UDB
Sr. SQL Server Administrator
Phone: (210)  913-3148
Pager: (210) 753-3148
9800 Fredericksburg Rd. San Antonio, TX  78288
www.usaa.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Eric
Barro
Sent: Thursday, April 08, 2004 4:56 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] passing IN search condition to stored procedure

Greetings SQL gurus!

I have a .NET page that declares parameters for passing to a stored
procedure with the code fragment...

myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@CustCode",
SqlDbType.varchar, 255)).Value = strCustCode

On the stored procedure I have this...

SELECT * FROM table
WHERE [CustCode] IN (@CustCode)

with @CustCode defined as a parameter.

I can't get it to work...has anyone passed a bunch of values to a stored
proc with an IN search condition and gotten it to work?

strCustCode would be equal to ('CUST01', 'CUST02', 'CUST03')

---
Eric Barro
Senior Systems Analyst
Advanced Field Services
(208) 772-7060
http://www.afsweb.com 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 4/8/2004


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com