今天收集了最为常见的存储过程代码,拿出来和大家分享一下,希望大家能够喜欢!最长用的存储过程的写法,有输出参数
![](http://www.blue1000.com/upload/2010-06/100602161441671.gif)
![](http://www.blue1000.com/upload/2010-06/100602161441672.gif)
1 ALTER PROCEDURE CreateOrder
2 (
3 @CustomerID uniqueidentifier ,
4 @Status nvarchar ( 50 ),
5 @ShippingName nvarchar ( 200 ),
6 @ShippingState nvarchar ( 200 ),
7 @ShippingCity nvarchar ( 200 ),
8 @ShippingAddress nvarchar ( 50 ),
9 @ShippingMode nvarchar ( 50 ),
10 @PayMode nvarchar ( 50 ),
11 @PostCode nvarchar ( 50 ),
12 @Phone nvarchar ( 50 ),
13 @MobilePhone nvarchar ( 50 ),
14
15 @OrderID int OUTPUT
16 )
17 AS
18 INSERT INTO Orders
19 (
20 DateCreated,
21 CustomerID,
22 Status,
23 ShippingName,
24 ShippingState,
25 ShippingCity,
26 ShippingAddress,
27 ShippingMode,
28 PayMode,
29 PostCode,
30 Phone,
31 MobilePhone
32 )
33 VALUES
34 (
35 GetDate (),
36 @CustomerID ,
37 @Status ,
38 @ShippingName ,
39 @ShippingState ,
40 @ShippingCity ,
41 @ShippingAddress ,
42 @ShippingMode ,
43 @PayMode ,
44 @PostCode ,
45 @Phone ,
46 @MobilePhone
47 )
48
49 SELECT @OrderID = MAX (OrderID) FROM Orders WHERE CustomerID = @CustomerID
50
51
52 RETURN
临时表、表变量的用法
![](http://www.blue1000.com/upload/2010-06/100602161441671.gif)
![](http://www.blue1000.com/upload/2010-06/100602161441672.gif)
1 ALTER PROCEDURE GetOrdersByCustmerID
2 (
3 @CustmerID uniqueidentifier ,
4 @SubTotal money OUTPUT,
5 @Count int OUTPUT
6 )
7 AS
8 DECLARE @proVisionalTable1 TABLE
9 (
10 OrderID int ,
11 ShippingName nvarchar ( 50 ),
12 PayMode nvarchar ( 50 ),
13 Status nvarchar ( 50 ),
14 DateCreated datetime ,
15 SubTotal money
16 );
17
18
19 WITH provisionalTable AS
20 (
21 SELECT SUM (SubTotal) AS SubTotal,OrderID AS OrderId FROM OrderDetail GROUP BY OrderID
22 )
23
24 INSERT INTO @provisionalTable1
25 SELECT
26 Orders.OrderID,
27 Orders.ShippingName,
28 Orders.PayMode,
29 Orders.Status,
30 Orders.DateCreated,
31 provisionalTable.SubTotal
32 FROM Orders LEFT JOIN provisionalTable ON (Orders.OrderID = provisionalTable.OrderId) WHERE CustomerID = @CustmerID
33
34 SELECT * FROM @provisionalTable1
35
36 SELECT @SubTotal = SUM (SubTotal) FROM @provisionalTable1
37
38 SELECT @Count = COUNT ( * ) FROM @provisionalTable1
39 RETURN
用临时表分页
![](http://www.blue1000.com/upload/2010-06/100602161441671.gif)
![](http://www.blue1000.com/upload/2010-06/100602161441672.gif)
1 ALTER PROCEDURE GetProductByDepartmentID
2 (
3 @CurrentPage INT ,
4 @MaxImumRows INT ,
5 @DepartmentID INT ,
6 @HowManyPage INT OUTPUT
7 )
8 AS
9 BEGIN
10 DECLARE @provisionalProduct TABLE
11 (
12 ProductID INT ,
13 PublisherID INT ,
14 [ Name ] nvarchar ( 50 ),
15 Image1FileName varchar ( 50 ),
16 PublisherName nvarchar ( 200 ),
17 CategoryID int ,
18 ISBN nvarchar ( 200 ),
19 Price money ,
20 AccountPrice money ,
21 Account int ,
22 SellQuantity int ,
23 DepartmentID int ,
24 sellRank int
25 )
26
27 INSERT INTO @provisionalProduct
28 SELECT product.ProductID,
29 product.PublisherID,
30 product.Name,
31 product.Image1FileName,
32 Publisher.PublisherName,
33 ProductCategory.CategoryID,
34 product.ISBN,
35 product.Price,
36 product.AccountPrice,
37 product.Account,
38 product.SellQuantity,
39 Category.DepartmentID,
40 ROW_NUMBER() OVER ( ORDER BY SellQuantity)
41 FROM Product LEFT JOIN Publisher ON product.PublisherID = Publisher.PublisherID
42 LEFT JOIN ProductCategory ON product.ProductID = ProductCategory.ProductID
43 LEFT JOIN Category ON Category.CategoryID = ProductCategory.CategoryID WHERE (Category.DepartmentID = @DepartmentID )
44
45
46
47
48 SELECT ProductID,
49 [ Name ] ,
50 Image1FileName,
51 CategoryID,
52 PublisherName,
53 ISBN,
54 Price,
55 AccountPrice,
56 Account,
57 DepartmentID,
58 sellRank
59 FROM @provisionalProduct WHERE sellRank BETWEEN (( @CurrentPage - 1 ) * @MaxImumRows + 1 ) AND ( @CurrentPage * @MaxImumRows )
60
61
62
63 SELECT @HowManyPage = COUNT ( * ) FROM @provisionalProduct ;
64 END
65 RETURN
用表变量分页:
![](http://www.blue1000.com/upload/2010-06/100602161441671.gif)
![](http://www.blue1000.com/upload/2010-06/100602161441672.gif)
1 ALTER PROCEDURE GetProductsOnCatalogPromotion
2 (
3 @CurrentPage INT ,
4 @MaxImumRows INT
5 )
6 AS
7 WITH provisionalProduct AS
8 (
9 SELECT product.PublisherID AS PublisherID,
10 product.ProductID AS ProductID,
11 product.Name AS [ Name ] ,
12 product.Image1FileName AS Image1FileName,
13 Publisher.PublisherName AS PublisherName,
14 product.ISBN AS ISBN,
15 product.Price AS Price,
16 product.AccountPrice AS AccountPrice,
17 product.Account AS Account,
18 product.SellQuantity AS SellQuantity,
19 ROW_NUMBER() OVER ( ORDER BY SellQuantity) AS sellRank
20 FROM Product LEFT JOIN Publisher ON product.PublisherID = Publisher.PublisherID
21 )
22
23
24 SELECT [ Name ] ,
25 ProductID,
26 Image1FileName,
27 PublisherName,
28 ISBN,
29 Price,
30 AccountPrice,
31 Account,
32 sellRank
33 FROM provisionalProduct
34 WHERE sellRank BETWEEN (( @CurrentPage - 1 ) * @MaxImumRows + 1 ) AND ( @CurrentPage * @MaxImumRows )
35 RETURN
向下面的这中情况只能用临时表不能用表变量:
![](http://www.blue1000.com/upload/2010-06/100602161441671.gif)
![](http://www.blue1000.com/upload/2010-06/100602161441672.gif)
1 ALTER PROCEDURE GetProudctByCategoryID
2 (
3 @CurrentPage INT ,
4 @MaxImumRows INT ,
5 @CategroyID INT ,
6 @HowManyPage INT OUTPUT
7 )
8 AS
9 BEGIN
10 DECLARE @provisionalProduct TABLE
11 (
12 ProductID INT ,
13 PublisherID INT ,
14 [ Name ] nvarchar ( 50 ),
15 Image1FileName varchar ( 50 ),
16 PublisherName nvarchar ( 200 ),
17 CategoryID int ,
18 ISBN nvarchar ( 200 ),
19 Price money ,
20 AccountPrice money ,
21 Account int ,
22 SellQuantity int ,
23 sellRank int
24 )
25 INSERT INTO @provisionalProduct
26 SELECT product.ProductID,
27 product.PublisherID,
28 product.Name,
29 product.Image1FileName,
30 Publisher.PublisherName,
31 ProductCategory.CategoryID,
32 product.ISBN,
33 product.Price,
34 product.AccountPrice,
35 product.Account,
36 product.SellQuantity,
37 ROW_NUMBER() OVER ( ORDER BY SellQuantity)
38 FROM Product LEFT JOIN Publisher ON product.PublisherID = Publisher.PublisherID
39 LEFT JOIN ProductCategory ON product.ProductID = ProductCategory.ProductID WHERE (CategoryID = @CategroyID )
40
41
42
43 SELECT ProductID,
44 [ Name ] ,
45 Image1FileName,
46 CategoryID,
47 PublisherName,
48 ISBN,
49 Price,
50 AccountPrice,
51 Account,
52 sellRank
53 FROM @provisionalProduct WHERE sellRank BETWEEN (( @CurrentPage - 1 ) * @MaxImumRows + 1 ) AND ( @CurrentPage * @MaxImumRows );
54
55
56 SELECT @HowManyPage = COUNT ( * ) FROM @provisionalProduct ;
57 END
58 RETURN
59
简单的判断型更新:
![](http://www.blue1000.com/upload/2010-06/100602161441671.gif)
![](http://www.blue1000.com/upload/2010-06/100602161441672.gif)
1 ALTER PROCEDURE ShoppingCartAddItem
2 (
3 @CartID CHAR ( 36 ),
4 @ProductID int
5 )
6 AS
7 IF EXISTS ( SELECT ProductID FROM ShoppingCart WHERE CartID = @CartID AND ProductID = @ProductID )
8 UPDATE ShoppingCart SET Quantity = Quantity + 1 WHERE CartID = @CartID AND ProductID = @ProductID
9
10 ELSE
11 INSERT INTO ShoppingCart VALUES ( @CartID , @ProductID , 1 , GETDATE ())
12 RETURN
简单搜索用到得存储过程:
{
搜索用的存储过程:
![](http://www.blue1000.com/upload/2010-06/100602161441671.gif)
![](http://www.blue1000.com/upload/2010-06/100602161441672.gif)
1 ALTER PROCEDURE SearchByAllWorlds
2 (
3 @CurrentPage int ,
4 @MaxImumRows int ,
5 @HowManyPages int output,
6 @AllWorld1 varchar ( 50 ) = null ,
7 @AllWorld2 varchar ( 50 ) = null ,
8 @AllWorld3 varchar ( 50 ) = null ,
9 @AllWorld4 varchar ( 50 ) = null ,
10 @AllWorld5 varchar ( 50 ) = null
11 )
12 AS
13 BEGIN
14 DECLARE @provisionalProduct TABLE
15 (
16 Description varchar ( 5000 ),
17 ProductID INT ,
18 PublisherID INT ,
19 [ Name ] nvarchar ( 50 ),
20 Image1FileName varchar ( 50 ),
21 PublisherName nvarchar ( 200 ),
22 CategoryID int ,
23 ISBN nvarchar ( 200 ),
24 Price money ,
25 AccountPrice money ,
26 Account int ,
27 SellQuantity int ,
28 DepartmentID int ,
29 sellRank int ,
30 Rank int ,
31 RankOrder int
32
33 )
34 INSERT INTO @provisionalProduct
35 SELECT
36 * ,
37 ROW_NUMBER() OVER ( ORDER BY producttemp.Rank DESC ) AS RankOrder FROM
38 (
39 SELECT Product.Description,
40 product.ProductID,
41 product.PublisherID,
42 product.Name,
43 product.Image1FileName,
44 Publisher.PublisherName,
45 ProductCategory.CategoryID,
46 product.ISBN,
47 product.Price,
48 product.AccountPrice,
49 product.Account,
50 product.SellQuantity,
51 Category.DepartmentID,
52 ROW_NUMBER() OVER ( ORDER BY SellQuantity) AS sellRank,
53 (
54 3 * dbo.wordCount( @AllWorld1 ,product.Name) + dbo.WordCount( @AllWorld1 ,Product.Description) +
55 3 * dbo.WordCount( @AllWorld2 ,product.Name) + dbo.WordCount( @AllWorld2 ,Product.Description) +
56 3 * dbo.WordCount( @AllWorld3 ,product.Name) + dbo.WordCount( @AllWorld3 ,Product.Description) +
57 3 * dbo.WordCount( @AllWorld4 ,product.Name) + dbo.WordCount( @AllWorld4 ,Product.Description) +
58 3 * dbo.WordCount( @AllWorld5 ,product.Name) + dbo.WordCount( @AllWorld5 ,Product.Description)
59 ) AS Rank
60 FROM Product LEFT JOIN Publisher ON product.PublisherID = Publisher.PublisherID
61 LEFT JOIN ProductCategory ON product.ProductID = ProductCategory.ProductID
62 LEFT JOIN Category ON Category.CategoryID = ProductCategory.CategoryID
63 ) AS producttemp
64
65
66
67
68
69
70
71
72 SELECT Description,
73 ProductID,
74 [ Name ] ,
75 Image1FileName,
76 CategoryID,
77 PublisherName,
78 ISBN,
79 Price,
80 AccountPrice,
81 Account,
82 DepartmentID,
83 sellRank,
84 Rank,
85 RankOrder
86 FROM
87 @provisionalProduct
88 WHERE (RankOrder BETWEEN (( @CurrentPage - 1 ) * @MaxImumRows + 1 ) AND ( @CurrentPage * @MaxImumRows )) And (Rank > 0 )
89
90 SELECT @HowManyPages = COUNT ( * ) FROM @provisionalProduct Where Rank > 0 ;
91
92
93 END
94 RETURN
上面存储过程中用到的标量函数:
![](http://www.blue1000.com/upload/2010-06/100602161441671.gif)
![](http://www.blue1000.com/upload/2010-06/100602161441672.gif)
1 ALTER FUNCTION dbo.WordCount
2
3 ( @Word VARCHAR ( 15 ), -- Java
4
5 @Phrase VARCHAR ( 1000 )) -- java程序设计
6
7 RETURNS SMALLINT
8
9 AS
10
11 BEGIN
12
13
14 IF @Word IS NULL OR @Phrase IS NULL RETURN 0
15
16 /* @BiggerWord 比@Word长一个字符 */
17
18 DECLARE @BiggerWord VARCHAR ( 21 )
19
20 SELECT @BiggerWord = @Word + ' x ' -- javax
21
22 /* 在 @Phrase用@BiggerWord替换@Word */
23
24 DECLARE @BiggerPhrase VARCHAR ( 2000 )
25
26 SELECT @BiggerPhrase = REPLACE ( @Phrase , @Word , @BiggerWord )
27 -- javax程序设计 --java程序设计 --java --javax
28 /* 相减结果就是出现的次数了 */
29
30 RETURN LEN ( @BiggerPhrase ) - LEN ( @Phrase )
31 -- javax程序设计9 --java程序设计8
32 END
}