拆分输入参数并在存储过程中输出表

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.

我有一个要求,我得到一个字符串,如'1500,4444,7777'。这些是产品的ID。现在我需要拆分此输入,我已经有了Split功能。也试过使用循环。

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.

拆分ID之后,我需要检索输入参数中发送的所有ID的ProductName及其GUID。我应该从SP返回ProductName和GUID的LIST,以便在Web方法中使用它。

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.

Product表包含Product GUID,Product Name和Product ID。现在我必须根据ID检索GUID和Name。

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.

我可以拆分产品ID,获取产品名称,但现在我不知道如何将产品名称及其GUID添加到列表并发送。

Please find the SP I tried till now.

请找到我试过的SP直到现在。

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

2 个解决方案

#1

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

然后使用Product表进行连接

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

#2

Using a split TBV Function like this

使用像这样的拆分TBV功能

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

然后,您可以将上述函数与PRODUCT表一起加入,并返回列表/表格

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

#1