CODEWITHSUNDEEP
sqlsql-serversplit
Renu R
Renu R

Reputation: 69

Split input parameter and output a table in a stored procedure

I have a requirement, where I get a string such as '1500,4444,7777'. These are the ID's of a Product. Now I need to Split this input, I already have a Split function too. Have tried using looping also.

After splitting the ID's, I need to retrieve the ProductName and its GUID for all the ID's sent in the Input Parameter. And I should return a LIST of the ProductName and the GUID from the SP to use it in a Web Method.

The Product table contains the Product GUID, Product Name and the Product ID. Now I have to retrieve the GUID and Name based on the ID.

I could split the Product ID, get the Product Name but now I am stuck at how to add Product Name and its GUID to a list and send.

Please find the SP I tried till now.

CREATE PROCEDURE GetProductNamesByProductNumber 
(@productNumberList nvarchar(max))
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @Err int

  DECLARE @pos int
  DECLARE @len int
  DECLARE @value varchar(8000)
  DECLARE @prodName varchar(8000)
  DECLARE @prodNames varchar(8000)


  SET @productNumberList = @productNumberList + ','
  SET @pos = 0
  SET @len = 0

  WHILE CHARINDEX(',', @productNumberList , @pos + 1) > 0
  BEGIN
    SET @len = CHARINDEX(',', @productNumberList , @pos + 1) - @pos
    SET @value = SUBSTRING(@productNumberList , @pos, @len)

    SELECT
      @prodName = ProductName FROM Product
    WHERE ProductNumber = @value

    SET @pos = CHARINDEX(',', @productNumberList , @pos + @len) + 1

    IF @prodNames <> ''
      SET @prodNames += ',' + @prodName 
    ELSE
      SET @prodNames= @prodName 

  END

  DECLARE @output_table TABLE (
    ProductName nvarchar(max)
  )
  INSERT @output_table
    SELECT
      *
    FROM SplitString(@prodNames, ',')

  SELECT * FROM @output_table

  SET @Err = @@Error
  RETURN @Err
END

GO

Upvotes: 0

Views: 1341

Answers (2)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

Once you split the input parameter, then add that in to temp table say 

create table #tempProductID( productid int)

Then make a join with your Product table

SELECT Product.ProductID, Product.GUID, Product.Name 
FROM Product INNER JOIN #tempProductID 
ON Product.ProductID = #tempProductID.ProductID

Upvotes: 1

Dbloch
Dbloch

Reputation: 2376

Using a split TBV Function like this

CREATE FUNCTION [dbo].[udf_SplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

You could then join the above function with the PRODUCT table and return the list/table like 

CREATE PROCEDURE GetProductNamesByProductNumber 
(@productNumberList nvarchar(max))
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @Err int


    SELECT
      ProductName, ProductGuid, ProductNumber FROM Product 
       INNER JOIN ( SELECT Value FROM   dbo.Split(@productNumberList, ',') ) a ON p.ProductNumber = a.Value


  END

Upvotes: 0

Related Questions