This post originated from an RSS feed registered with Web Buzz by Josh Baltzell. Original Post: Need some stored procedure feedback Feed Title: ShortDomainName.com Feed URL: http://www.contegix.com/Rss.aspx Feed Description: Weblog of Josh Baltzell. Focusing on the computer industry and .NET programming related news. I like to get involved on the discussions wherever they are. Latest Web Buzz Posts Latest Web Buzz Posts by Josh Baltzell Latest Posts From ShortDomainName.com

I have been using Stored Procedures in my .NET apps for a while now because I like the idea of limiting the rights of the SQL user to only what I want it to have.  Plus if there happens to be a performance increase then all the better.  But... not being a SQL expert I doubt myself on the quality of my procedures.  Could anyone out there that finds this give me some critique on whether or not I am using stored procedures in the best way possible?  Below are a few examples of what I am using.

A:

CREATE Procedure VI_CartSubmitOrder
(
 @OrderID int,
 @PaymentReferenceID varchar(20),
 @BalanceChange money,
 @UserID int
)
AS
BEGIN
 UPDATE    OrderMain
 SET              Submitted = 1, DateSubmitted = GETDATE(), PaymentReferenceID = @PaymentReferenceID
 WHERE     (OrderID = @OrderID)
END
 UPDATE    iX_UserData
 SET       Custom06 = Custom06 - @BalanceChange
 WHERE     (LogonID = @UserID)GO

B:

CREATE Procedure VI_CatalogGetDetails
(
    @ProductID int
)
As
SELECT 
    Products.ProductID,Products.ProductTitle,Products.Cost,Products.DescShort,Products.DescLong,Products.ImageFull,Products.ImageLarge,Products.ImageCatalog,Products.Variable
FROM   
    Products
GROUP BY   
    Products.ProductID,Products.ProductTitle,Products.Cost,Products.DescShort,Products.DescLong,Products.ImageFull,Products.ImageLarge,Products.ImageCatalog,Products.Variable
HAVING 
    Products.ProductID = @ProductID

GO

C:

CREATE Procedure VI_CartTotalPrice
(
    @OrderID    int,
    @TotalCost money OUTPUT
)
AS
SELECT     @TotalCost = SUM(Products.Cost * OrderItems.Quantity)
FROM         OrderItems INNER JOIN
                      Products ON OrderItems.ProductID = Products.ProductID INNER JOIN
                      OrderMain ON OrderItems.OrderID = OrderMain.OrderID
WHERE     (OrderMain.OrderID = @OrderID)

GO

D:

CREATE Procedure VI_CartAddItem
 @OrderID int,
 @ProductID int,
 @Quantity int,
 @intOrderItemID int OUTPUT
 
AS
 
BEGIN
 INSERT INTO OrderItems
  (
   OrderID,
   Quantity,
   ProductID,
   DateTime,
   ShippingID
  )
  VALUES
  (
   @OrderID,
   @Quantity,
   @ProductID,
   GETDATE(),
   0
  )
 SELECT @intOrderItemID = @@IDENTITY
END
return @intOrderItemID

GO