Warm tip: This article is reproduced from stackoverflow.com, please click
sql-server tsql

Procedure data input a temp table

发布于 2020-03-27 10:16:16

I have a procedure which returns the result correctly, but I could not store the procedure result in a single table or temptable.

My procedure return values like this

ItemName
BATH RUG-VDW.WO COTTON CLASSIC FRAME
-------------------------------------------
ItemName    ItemCode    StyleNo UPC EAN UnitFactor
BATH RUG-VDW.WO COTTON CLASSIC FRAME    4       884631844635    0884631844635   4.580
BATHRUG-VDW.WO COTTON CLASSIC FRAME 5       884631844659    0884631844659   4.580
BATH RUG-VDW.W COTTON CLASSIC FRAME 6       884631844666    0884631844666   4.580
ItemName
BATH RUG-VDW.WOVEN COTTON CLASSIC FRAME 12-840
-------------------------------------------
ItemName    ItemCode    StyleNo UPC EAN UnitFactor
BATH RUG-VDW.WOVEN COTTON CLASSIC FRAME 12-840  30  12-840  884631881906    0884631881906   4.580
BATH RUG-VDW.WOVEN COTTON CLASSIC FRAME 12-840  31  12-840  884631881913    0884631881913   4.580
----------------------------------------------------------------------------

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_GetDetail]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @tab TABLE (no INT, name VARCHAR(30))

    DECLARE @MyCursor CURSOR;
    DECLARE @MyField VARCHAR(MAX);
    DECLARE @Qry VARCHAR(MAX);

    SET NOCOUNT ON;

    SET @MyCursor = CURSOR FOR
        SELECT DISTINCT ItemName FROM dbo.NewTest             

    SET @Qry=''

    OPEN @MyCursor 

    FETCH NEXT FROM @MyCursor INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
        /* YOUR ALGORITHM GOES HERE */

        SET @Qry = @Qry + '; SELECT '''+@MyField+''' AS ItemName'
        SET @Qry = @Qry + '; SELECT ItemName, ItemCode, StyleNo, UPC, EAN, UnitFactor FROM NewTest WHERE ItemName = '''+@MyField+''''

        FETCH NEXT FROM @MyCursor INTO @MyField 
    END;    

    EXEC (@Qry);

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;
Questioner
hemant rajoria
Viewed
71
osiris_v6 2019-07-03 21:07

Cursors should be avoided as much as possible. This returns a single result set. Hope this is OK for your needs.

IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable;

CREATE TABLE #MyTempTable
    (
      ItemName NVARCHAR(100) ,
      ItemCode NVARCHAR(100) ,
      StyleNo NVARCHAR(100) ,
      UPC NVARCHAR(100) ,
      EAN NVARCHAR(100) ,
      UnitFactor NVARCHAR(100) ,
      ItemNameGroup NVARCHAR(100)
    );
WITH    cte
          AS ( SELECT   [ItemName] ,
                        [ItemCode] ,
                        [StyleNo] ,
                        [UPC] ,
                        [EAN] ,
                        [UnitFactor] ,
                        ROW_NUMBER() OVER ( PARTITION BY [ItemName] ORDER BY [ItemName] ) AS rn
               FROM     [dbo].[NewTest]
             )
    INSERT  INTO [#MyTempTable]
            ( [ItemName] ,
              [ItemCode] ,
              [StyleNo] ,
              [UPC] ,
              [EAN] ,
              [UnitFactor] ,
              [ItemNameGroup]
            )
    SELECT  c.[ItemName] ,
            c.[ItemCode] ,
            c.[StyleNo] ,
            c.[UPC] ,
            c.[EAN] ,
            c.[UnitFactor] ,
            t.[ItemName]
    FROM    [cte] AS c
    INNER JOIN ( SELECT [cte].[ItemName]
                 FROM   [cte]
                 WHERE  [cte].[rn] = 1
               ) t ON c.[ItemName] = t.[ItemName];

SELECT  [mtt].[ItemName] ,
        [mtt].[ItemCode] ,
        [mtt].[StyleNo] ,
        [mtt].[UPC] ,
        [mtt].[EAN] ,
        [mtt].[UnitFactor] ,
        [mtt].[ItemNameGroup]
FROM    [#MyTempTable] AS [mtt];