Advanced Search

alter stroeprocedure

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. USE [CalendarOrders]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[ItemLookupWithTotals_Select]    Script Date: 02/05/2009 15:09:27 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. -- =============================================
  9. -- Author:		<Author,siqing Li>
  10.  
  11. -- =============================================
  12. ALTER PROCEDURE [dbo].[ItemLookupWithTotals_Select] --@ReturnValue int output
  13. AS
  14. BEGIN
  15. 	SET NOCOUNT ON;
  16.  
  17.     SELECT 
  18. 		ItemId,
  19. 		SUM(Qty) AS Total
  20.  
  21. 	INTO
  22. 		#TotalTemp
  23. 	FROM
  24. 		BulkItems
  25. 	WHERE
  26. 		PurchaseDate IS NULL
  27. 	GROUP BY
  28. 		ItemId
  29.  
  30. 	DECLARE @TempItems TABLE
  31. 	(ItemId INT,
  32. 	 Category VARCHAR(25),
  33. 	 MFG VARCHAR(50),
  34. 	 Description VARCHAR(100),
  35. 	 SIZE VARCHAR(50),
  36. 	 Price money,
  37. 	 Total INT DEFAULT 0)
  38.  
  39. 	INSERT INTO @TempItems (ItemID, Category, MFG, Description, SIZE, Price)
  40. 	SELECT ItemID, Category, MFG, Description, SIZE, Price FROM ItemLookup
  41.  
  42. 	UPDATE @TempItems 
  43. 	SET a.Total = b.Total
  44. 	FROM @TempItems AS a JOIN
  45. 		#TotalTemp AS b
  46. 		ON a.ItemID = b.ItemID
  47.  
  48.     --SELECT * FROM @TempItems
  49.    SELECT ItemID, Category, MFG, Description, SIZE, Price,Price*Total AS Amount
  50.    FROM @TempItems
  51. 	DROP TABLE #TotalTemp
  52.  
  53. --SELECT @ReturnValue = SCOPE_IDENTITY()
  54.  
  55. --RETURN @ReturnValue
  56.  
  57. END
  58.  
  59.  
  60. --Execute ItemLookupWithTotals_Select

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.