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