[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

SQL 2005 - Pass a comma delimited parameter to a SP and return varchar to be used in a where IN clause.

Hello all...
This should be the easiest points you've gotten on here.  I'm an Access guy and could do this in VBA with my eyes closed, but when it comes to SQL Server, I'm a complete newbee...

All I want to do is pass a comma delimited set of values (ie..  ITEM1,ITEM2,ITEM3,ITEM4) to a stored procedure and have it return a varchar that I can use in a where clause (ie.. '''ITEM1'',''ITEM2'',''ITEM3'',''ITEM4''')

I also need to know how to call that SP from within another SP (I told you I was a newbee)...

Example portion of code from calling SP:

if CHARINDEX(',',@ITEM)>0
begin
      @ITEM = CALL SP TO PARSE THE STRING AND RETURN VALUE IN THE FORMAT IT'S NEEDED IN.  
end


Here's my attempt at it, but I clearly don't have a clue.  Maybe I'm close, maybe not...  All I know is I need this ASAP.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[SP_ParseString]
      @string varchar(500) =  NULL
      ,@delimeter char(1) =  NULL
AS
      SET NOCOUNT ON;

declare      
      @pos                  int
      ,@piece                  varchar(500)
      ,@buildpiece      varchar(500)

if right(rtrim(@string),1) <> @delimeter
 set @string = @string  + @delimeter

set @pos =  patindex('%' + @delimeter + '%' , @string)
while @pos <> 0

BEGIN

 set @piece = '''' + left(@string, @pos - 1) + ''''
 
if isnull(@buildpiece,'NULL')='NULL'
      begin
            set @buildpiece = @piece
      end
else
      begin
            set @buildpiece = @buildpiece + ', ' + @piece
      end
      
 set @string = stuff(@string, 1, @pos, '')
 set @pos =  patindex('%,%' , @string)

--print @buildpiece

end
0
Cristal638
Asked:
Cristal638
3 Solutions
 
SharathData EngineerCommented:
Do you want to convert a comma seperated string into a tabular format or do you want to pass a comma seperated string to SP as parameter and use that inside SP code?
0
 
Ephraim WangoyaCommented:
What you need is a function that returns a table which you can then query

 
CREATE FUNCTION fn_ParseString(@string varchar(500), @delimeter char(1))
RETURNS @Items TABLE(Item varchar(20))
AS
BEGIN
  DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(@delimeter, @string, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@string) + 1
                         END - @pos - 1
      INSERT @Items(Item)
         VALUES (ltrim(rtrim(substring(@string, @pos + 1, @valuelen))))
      SELECT @pos = @nextpos
   END
   RETURN
END

Open in new window


within your store procedure you can do

declare @param varchar(max)
set @param = 'ITEM1,ITEM2,ITEM3,ITEM4'

select * from anothertable
where item in (select Item from dbo.fn_ParseString(@param, ','))
0
 
Nico BontenbalCommented:
Yes, you'll have to go with ewangoya's solution, or something like this. Even if you got your original plan (return a varchar that I can use in a where clause) to work, it won't do what you expect. For example this:
declare @in as varchar(100)
set @in = '''John'',''Jane'''
select * from Customer where CustomerName IN (@in)

Open in new window

Won't return any records. (Unless you actually have a record with 'John','Jane' as the first name). The difference between
in ('John','Jane')
and
IN (@in)
It that in the first example you are passing multiple expressions to the in clause. Where in the second example you only pass one expression. That one expression is a string that contains a comma and quotes, but that doesn't make it different from any other expression. It's like using
Select * from Customer where Customer in (forms!select!customers)
In access. That won't work either.
The alternative is using dynamic sql. this is like using a strSQL variable in VBA and then execute the text of this variable as a query. This would go something like this:
declare @in as varchar(100)
declare @sql as varchar(1000)
set @in = '''John'',''Jane'''
set @sql = 'select * from Customer where CustomerName IN (' + @in + ')'
exec (@sql)

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
Split the comma separated string and again create appripriate '','','', etc.
http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now