Link to home
Start Free TrialLog in
Avatar of boukaka
boukakaFlag for Canada

asked on

Loop through an Array parameter without using a table or xml

I have a stored procedure that I use to pull information for a report. The users need to be able to pass multiple values into the @Source parameter and I was going to have them separate their values with comma's but I'm not sure how to retrieve the results correctly.

I don't have a loop in here yet because I don't know how to code it but the @Source parameter could have multiple values separated by values (i.e. maria,tony,jeff). Can anyone tell me how to modify this so that it loops through the values in the @Source parameter and pulls information based on that?

ALTER      PROCEDURE [dbo].[_GNM_TC_DETAIL_SLS2]
      @ClntCode VARCHAR(20),
      @TransBegDate datetime,
      @TransEndDate datetime,
      @Source varchar(40)
AS
      
create     table #SalesJournal      
(
                policyid                    integer,
      provinceid      integer,
      provincecode      varchar(10),
      createby            integer,
      userlogin            varchar(40),
      userlogin2      varchar(40)
  )
insert into #SalesJournal(policyid,provinceid, provincecode, createby,userlogin,userlogin2)
select distinct      
      p.policyid,vp.provinceid, vp.provincecode, p.createby, up.userlogin, up2.userlogin

from      vwProvinces vp on p.provinceid=vp.provinceid
      inner join
      userprofile up on p.createby=up.userprofileid
      inner join userprofile up2 on p.modifyby=up2.userprofileid

where
          p.clientcode = @ClntCode
         and p.transactiondate between
         convert(datetime, @TransBegDate) and convert(datetime, @TransEndDate)
         and up.userlogin in Join(@Source,", ") or up2.userlogin in Join(@Source,",")
Avatar of chapmandew
chapmandew
Flag of United States of America image

please create the below function, and change your where clause like this:

         and ( up.userlogin in ( select value from dbo.ParmsToList(@Source,',') )
           or up2.userlogin in ( select value from dbo.ParmsToList(@Source,',') )
             )


create FUNCTION dbo.ParmsToList (@Parameters varchar(500), @delimiter varchar(10) )
returns @result TABLE (Value varchar(8000))
AS  
begin
    declare @dx varchar(9)
    --declare @loops int
    -- set @loops = 0
 
     DECLARE @TempList table
          (
          Value varchar(8000)
          )
 
     if @delimiter is null  set @delimiter = ' '
     if datalength(@delimiter) < 1 set @delimiter = ' '
     set @dx = left(@delimiter, datalength(@delimiter)-1)
 
     DECLARE @Value varchar(8000), @Pos int
 
     SET @Parameters = @Parameters + @delimiter
     SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
     IF REPLACE(@Parameters, @delimiter, @dx) <> ''
     BEGIN
          WHILE @Pos > 0 --and @Loops < 100
          BEGIN
              -- set @loops = @loops + 1
               SET @Value = LEFT(@Parameters, @Pos - 1)
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END  

Open in new window

Avatar of boukaka

ASKER

This report is being run from Crystal reports and I don't believe that I can do that.
Avatar of boukaka

ASKER

Hmm.. I did as you suggested but I forgot to mention I'm running 2005. The error I get when I try to alter the stored procedure is this:

Msg 468, Level 16, State 9, Procedure _GNM_TC_DETAIL_SLS2, Line 74
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of boukaka

ASKER

AH, yes, I made a slight modification

and ( up.userlogin COLLATE Latin1_General_CI_AI in ( select value from dbo.ParmsToList(@Source,',') )
           or up2.userlogin COLLATE Latin1_General_CI_AI in ( select value from dbo.ParmsToList(@Source,',') ))

and I don't have an error, now all that remains to be seen is if the stored procedure actually pulls the data. I'm running it now.
Avatar of boukaka

ASKER

SUCCESS!!! It's perfect!! Absolutely 100% perfect! Thank you so much, I've been struggling with this problem for what seems like EVER.