温馨提示:本文翻译自stackoverflow.com,查看原文请点击:sql server - Procedure data input a temp table

sql server - 程序数据输入临时表

发布于 2020-03-27 10:22:49

我有一个过程可以正确返回结果,但是无法将过程结果存储在单个表或临时表中。

我的过程返回这样的值

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
----------------------------------------------------------------------------

码:

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;

查看更多

查看更多

提问者
hemant rajoria
被浏览
30
osiris_v6 2019-07-03 21:07

应尽可能避免使用游标。这将返回一个结果集。希望这可以满足您的需求。

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];

发布
问题

分享
好友

手机
浏览

扫码手机浏览