split string parameter and do select on that array of strings?

Question

I have a stored procedure GetReportItems:

GetReportItems

@ItemId varchar(max)

SELECT rt.ReportName, rt.ReportId, rg.OriginatedFrom
FROM Reports rt
    JOIN ReportOrigin rg on rg.ReportId = rt.ReportId
        WHERE rt.ColA = SUBSTRING(@ItemId, 1, 3) 
        AND rt.ColB = SUBSTRING(@ItemId, 4, Len(@ItemId) - 3)

@ItemId I can pass it to be as: ABC123Z OR DEF3456Y OR GHI7890X and it all works fine.

But I need to update this stored procedure to allow:

pass in ABC123Z~DEF3456Y~GHI7890X as the @ItemId, the parameter to the stored procedure.
the stored procedure to split @ItemId string on ~ and call SELECT on each of those string.

How could I do 1 and 2 above? Even if I pass in multiple parameters to the stored proc, how can I aggregate SELECT on all these parameters?

That is starting from the wrong place. Splitting the items at all is bad enough; trying to deal with variable length lists of items to be split is ... not very sensible. I'm not sure exactly what to recommend as the alternative. I'd probably use a temporary table with two columns, the ColA part and the ColB part of the strings. I'd then load the table with the values to be searched for and then do the query as a join of the main table and the temporary. — Jonathan Leffler
– Jonathan Leffler, Commented Dec 29, 2011 at 23:22

Jason 'Bug' Fenter · Accepted Answer · 2011-12-29 23:29:33Z

Temp tables are your friend here. :) Take your @ItemID and split it into a temp table, then join your report to that temp table.

-- We need some variables for working with the data
DECLARE   @Sep Char,
          @SepPos Int
SET @Sep = '~'

-- We need a place to store our arguments
CREATE TABLE #Values (Val1 VarChar(3), Val2 VarChar(50))

SELECT @SepPos = CharIndex (@Sep, @ItemID)
WHILE @SepPos > 0 BEGIN
   -- Parse the leading argument into the temp table
   INSERT INTO #Values (Val1, Val2)
   SELECT   SubString (@ItemID, 1, 3),
            SubString (@ItemID, 4, @SepPos - 4)

   -- Remove the leading argument from the argument string
   SELECT @ItemID = SubString (@ItemID, @SepPos + 1, Len (@ItemID))
   -- Find the next separator
   SELECT @SepPos = CharIndex (@Sep, @ItemID)
END
-- On the last loop, it won't have a separator, so we'll end up with
-- one last argument to parse into our temp table
INSERT INTO #Values (Val1, Val2)
SELECT   SubString (@ItemID, 1, 3),
         SubString (@ItemID, 4, Len (@ItemID) - 3)

-- Now join to our report
SELECT   *
FROM     Reports rt
   JOIN ReportOrigin rg ON rg.ReportId = rt.ReportId
   JOIN #Values ON
        rt.ColA = #Values.Val1 AND rt.ColB = #Values.Val2

Collectives™ on Stack Overflow

split string parameter and do select on that array of strings?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related