存储过程

1.最简单的存储过程
CREATE PROCEDURE dbo.sp
AS
SELECT * from ShoppingCart
GO

2.创建带参数的存储过程
CREATE PROCEDURE dbo.spShoppingCartUpdateQty
(
    @CartID    VARCHAR(50),
    @ProductID INT,
    @Quantity  INT
)
AS

UPDATE ShoppingCart
SET
    Quantity = @Quantity
WHERE
    CartID = @CartID
  AND
    ProductID = @ProductID
GO

3.创输出参数的存储过程
CREATE PROCEDURE dbo.spShoppingCartTotal
(
    @CartID    VARCHAR(50),
    @TotalCost money OUTPUT
)
AS

SELECT @TotalCost =  Count(*)
FROM dbo.ShoppingCart
WHERE (A.CartID = @CartID)
GO

-----------------------------------------------------------
 CREATE PROCEDURE dbo.spCustomersLogin
(
    @Email      VARCHAR(100),
    @Password   VARCHAR(50),
    @ID INT OUTPUT
)
AS

SELECT
    @ID = ID
FROM
    Customers
WHERE
    Email = @Email
  AND
    Password = @Password

IF @@Rowcount < 1
SELECT
    @ID = 0
GO

4.带事务的存储
 CREATE PROCEDURE dbo.spOrdersInsert
(
    @CustomerID INT,
    @CartID     VARCHAR(50),
    @Memo  VARCHAR(2000),
    @OrderID    INT OUTPUT
)
AS

-- 确保数据的一致性, 因而起用事务
BEGIN TRAN OrdersInsert

/* 产生订单 */
INSERT INTO Orders
(
    CustomerID,
    Memo
)
VALUES
(
    @CustomerID,
    @Memo
)

-- 返回当前所产生的OrderID
SELECT
    @OrderID = @@Identity   

/* 将当前用户的当前购物车COPY到当前所产生的ORDER明细中*/
INSERT INTO OrderDetails
(
    OrderID,
    ProductID,
    Quantity,
    UnitCost
)

SELECT
    @OrderID,
    ShoppingCart.ProductID,
    Quantity,
    Products.UnitCost

FROM
    ShoppingCart
  INNER JOIN Products ON ShoppingCart.ProductID = Products.ProductID
 
WHERE
    CartID = @CartID

/* 完成后, 清空当前购物车ID的所有明细*/
EXEC spShoppingCartEmpty @CartId

COMMIT TRAN OrdersInsert
GO

5.内部构造SQL语句
 CREATE PROCEDURE dbo.spCustomersTop
(
    @TOP VARCHAR(25),  -- TOP 子句
    @ORDERBY VARCHAR(500) -- ORDER BY 子句
)
AS

SET NOCOUNT ON
DECLARE @sSQL VARCHAR(500)

SELECT @sSQL = 'SELECT TOP ' + @TOP --需为varchar类型
SELECT @sSQL = @sSQL + ' * FROM dbo.Customers'
SELECT @sSQL = @sSQL + ' WHERE ' + @ORDERBY + ' = 1'

--PRINT @sSQL

EXEC(@sSQL)
SET NOCOUNT OFF
GO