Revision: 11447
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at February 5, 2009 16:13 by siqingli
Initial Code
USE [CalendarOrders]
GO
/****** Object: StoredProcedure [dbo].[ItemLookupWithTotals_Select] Script Date: 02/05/2009 15:09:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,siqing Li>
-- =============================================
ALTER PROCEDURE [dbo].[ItemLookupWithTotals_Select] --@ReturnValue int output
AS
BEGIN
SET NOCOUNT ON;
SELECT
ItemId,
SUM(Qty) AS Total
INTO
#TotalTemp
FROM
BulkItems
WHERE
PurchaseDate IS NULL
GROUP BY
ItemId
DECLARE @TempItems TABLE
(ItemId int,
Category varchar(25),
MFG varchar(50),
Description varchar(100),
Size varchar(50),
Price money,
Total int DEFAULT 0)
INSERT INTO @TempItems (ItemID, Category, MFG, Description, Size, Price)
SELECT ItemID, Category, MFG, Description, Size, Price FROM ItemLookup
UPDATE @TempItems
SET a.Total = b.Total
FROM @TempItems AS a JOIN
#TotalTemp AS b
ON a.ItemID = b.ItemID
--SELECT * FROM @TempItems
Select ItemID, Category, MFG, Description, Size, Price,Price*Total as Amount
FROM @TempItems
DROP TABLE #TotalTemp
--SELECT @ReturnValue = SCOPE_IDENTITY()
--RETURN @ReturnValue
END
--Execute ItemLookupWithTotals_Select
Initial URL
Initial Description
Initial Title
alter stroeprocedure
Initial Tags
Initial Language
SQL