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?

      @ClntCode VARCHAR(20),
      @TransBegDate datetime,
      @TransEndDate datetime,
      @Source varchar(40)
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

          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
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))
    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) <> ''
          WHILE @Pos > 0 --and @Loops < 100
              -- set @loops = @loops + 1
               SET @Value = LEFT(@Parameters, @Pos - 1)
               IF @Value <> ''
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
     INSERT @result
     SELECT value
        FROM @TempList

Open in new window

Avatar of boukaka


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


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.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
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


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


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