存储过程

以一个存储过程为例，说说建立存储过程可能会碰见的东西：

==================================================================================

 IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('GetCommodityConsumptionSummary') AND sysstat & 0xf = 4)
    DROP PROCEDURE GetCommodityConsumptionSummary
GO
 

CREATE PROCEDURE GetCommodityConsumptionSummary
(   
    @ProductCode VARCHAR(30) =NULL,
    @ProductType TINYINT,
    @StartTime DATETIME =NULL,
    @EndTime DATETIME =NULL,
    @OrderString VARCHAR(200),
    @PageSize INT,
    @PageIndex INT,
    @TotalRecords INT OUTPUT   
   
)
AS
BEGIN
   
    IF @ProductType = 0 SET @ProductType = NULL   

    DECLARE @StartIndex int
    DECLARE @EndIndex int
   
    SET @StartIndex = @PageSize * @PageIndex
    SET @EndIndex = @PageSize * (@PageIndex + 1)
   
    SELECT
        SUM (quantity)AS TotalQuantity,
        TAB.ProductCategoryName,
        TAB.Productcode,
        TAB.ProductName,
        TAB.ProductId
    INTO #TempTable
    FROM
    (
        SELECT
            dbo.Product.ProductId AS ProductId,
            dbo.ConsumptionItem.Quantity AS Quantity,       
            dbo.Product.ProductCode AS Productcode,
            dbo.Product.ProductName AS ProductName,
            dbo.DictionaryItem.ItemName AS ProductCategoryName   
        FROM dbo.ConsumptionItem with(NOLOCK)
        INNER JOIN dbo.Consumption
            ON dbo.ConsumptionItem.ConsumptionId = dbo.Consumption.ConsumptionId
        INNER JOIN dbo.Product
            ON dbo.Product.ProductId = ConsumptionItem.ProductId
        INNER JOIN dbo.DictionaryItem
            ON DictionaryItem.ItemValue = Product.CategoryId
            AND dbo.DictionaryItem.DictionaryName='ProductCategory'   
        WHERE (@ProductCode IS NULL OR dbo.Product.ProductCode=@ProductCode)
        AND (@ProductType IS NULL OR dbo.Product.CategoryId=@ProductType)
        AND (@StartTime IS NULL OR dbo.Consumption.CreateDate>=@StartTime)
        AND (@EndTime IS NULL OR dbo.Consumption.CreateDate<=@EndTime)
    )AS TAB
    GROUP BY TAB.ProductCategoryName,TAB.Productcode,TAB.ProductName,TAB.ProductId
   
    DECLARE @Sql VARCHAR(2000)

    SET @Sql = '
    SELECT
        *
    FROM
    (
        SELECT
            Row_Number() OVER (ORDER BY + '+ @OrderString + ') AS RowNumber,
            *
        FROM #TempTable       
    ) AS Tab
    WHERE Tab.RowNumber > ' + RTRIM(@StartIndex)+
    ' AND Tab.RowNumber <=' + RTRIM(@EndIndex)

    EXEC(@Sql)   

    SELECT
        @TotalRecords = COUNT(1)
    FROM #TempTable
   
    DROP TABLE #TempTable
END
GO

 

==================================================================================

代码如上，分别讲述：

 

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('GetCommodityConsumptionSummary') AND sysstat & 0xf = 4)    DROP PROCEDURE GetCommodityConsumptionSummary

 

 

SYSOBJECTS  为何？

 

sysobjects   是Sql   server   任一数据库实例都有的一个系统表，记录所有本实例内所有OBJECT的属性。object_id   是系统函数，取得某一OBJECT的全局ID。

 

sysstat & 0xf = 4为何？

sysstat是字段名
&是位操作符，是 位and 操作
0xf是16进制数 0xf=0b1111=15

sysstat  &  0xf  =  4
sysstat 与 16进制数0xf按位与的值为4时
即sysstat 与二进制数1111111按位与的值为4时
也就是sysstat的值为4时 即0000100时

 

GO 不能缺失，因为，CREATE PROCEDURE 必须是一个批处理的以第一个语句。

 

 

存储过程带参数。

1.必须是以@开头，参数可以赋初值，以备当传入参数为空的时候该存储过程的执行。

2，output 参数类型怎样在SQL里面检测：

语法如下：

DECLARE  @YY  [参数类型]

EXECUTE  [存储过程名] @i,@b,@YY OUTPUT

SELECT @YY 

3.分页的功能，pagesize 为每一页的条数，pageindex为当前页数，设定一个rownumber 当rownumber 介于某两个数之间的时候，来判断该记录位于哪一页。

4.with(NOLOCK) 详解。

 参照页面：http://www.cnblogs.com/neil-zhao/articles/1259105.html

另外，关于SQL中的锁，需要学习的东西太多，我也还有一个问题需要解决，页面如下：http://topic.csdn.net/t/20060905/14/4999881.html

 

恩，暂时就这么多，单纯的讲述在SQL中建立一个存储过程。至于怎么在VS中调用存储过程，以后在讨论。