CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
VoodooChild
VoodooChild

Reputation: 9784

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

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:

  1. pass in ABC123Z~DEF3456Y~GHI7890X as the @ItemId, the parameter to the stored procedure.
  2. 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?

Upvotes: 0

Views: 605

Answers (1)

Jason 'Bug' Fenter
Jason 'Bug' Fenter

Reputation: 1624

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

Upvotes: 1

Related Questions