Incorporating a function to split KVP into SP

• tonygman

  SSC Enthusiast

  Points: 193

  More actions

  May 15, 2019 at 6:02 am

  #3642214

  I have a function that splits key value pairs and a stored procedure that needs to use these kvp's.

  Here is my function:

  CREATE FUNCTION dbo.KeyValuePairs( @inputStr VARCHAR(MAX)) 
  RETURNS @OutTable TABLE 
  (KeyName VARCHAR(MAX), KeyValue VARCHAR(MAX))
  AS
  BEGIN
  
  DECLARE @separator CHAR(1), @keyValueSeperator CHAR(1)
  SET @separator = ','
  SET @keyValueSeperator = ':'
  
  DECLARE @separator_position INT , @keyValueSeperatorPosition INT
  DECLARE @match VARCHAR(MAX) 
  
  SET @inputStr = @inputStr + @separator
  
  WHILE PATINDEX('%' + @separator + '%' , @inputStr) <> 0 
   BEGIN
    SELECT @separator_position =  PATINDEX('%' + @separator + '%' , @inputStr)
    SELECT @match = LEFT(@inputStr, @separator_position - 1)
    IF @match <> '' 
    BEGIN
              SELECT @keyValueSeperatorPosition = PATINDEX('%' + @keyValueSeperator + '%' , @match)
              IF @keyValueSeperatorPosition <> -1 
                BEGIN
          INSERT @OutTable
   VALUES (LEFT(@match,@keyValueSeperatorPosition -1),_
   RIGHT(@match,LEN(@match) - @keyValueSeperatorPosition))
                END
     END
     SELECT @inputStr = STUFF(@inputStr, 1, @separator_position, '')
  END
  
  RETURN
  END
  GO
  

  An example of what it does:

  SELECT * FROM dbo.KeyValuePairs(Phone:123-456-7890, email: email@email.com)

  would give me the following format

  KeyName            KeyValue

  Phone                   123-456-7890

  email                     email.com

   

  I need to find a way to incorporate this information into my stored procedure, the values for the parameters (PhoneNum, EmailAddr, DrivLic) are going to be coming in as KVPs. This is why I need to incorporate the function. Since I'm able to treat the function as a table I can pivot this information and get the key names as column and key values into a single row. Based on what I have now I'm not sure how I could tie KVP's back to the table if I don't include ID. I'm also not sure whether I need to use a function at all or if a splitString would accomplish my needs. As far as the KVP's go - there may be 2 pairs like the example above or it may come in as 10 pairs of different key names.

   

  Here is my stored procedure:

  CREATE PROCEDURE [Schema].[ProcName]
      (@PhoneNum  VARCHAR(100),
       @EmailAddr VARCHAR(100),
       @DriverLic VARCHAR(100),
       @EligiblityDate VARCHAR(10))
  AS
  BEGIN
  SET NOCOUNT ON;
      DECLARE @Phone BIT = 0,
              @Email BIT = 0,
              @License BIT = 0
  
      SELECT @Phone = case when ProductType = 1 and Value = @PhoneNum then 1 else 0 end,
          @Email = case when ProductType = 2 and Value = @EmailAddr then 1 else 0 end,
          @License = case when ProductType = 3 and Value = @DriverLic then 1 else 0 end
      FROM Table 
          WHERE Product = 'ProductA' 
            AND @EligibilityDate BETWEEN EligStart AND EligEnd 
            AND ProductType = 1 
  
      SELECT 
          @Phone AS Phone,
          @Email AS Email,
          @License AS License;
  
      RETURN 0;
  END