常用存储过程

 

最长用的存储过程的写法，有输出参数

代码

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

 

临时表、表变量的用法

代码

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

 

 

 

用临时表分页

代码

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

 

 

 

 

用表变量分页：

 

代码

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

 

 

 

 

向下面的这中情况只能用临时表不能用表变量：

 

代码

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

 

 

 

简单的判断型更新：

 

代码

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

 

 

 

简单搜索用到得存储过程：

{

搜索用的存储过程：

 

代码

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

 

 

 

 

上面存储过程中用到的标量函数：

 

代码

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

 

 

}

 

 

楼房“批量添加”存储过程：

 

代码

CREATE PROCEDURE RoomPLAddSQL
(
@DaiMaFenGeHao varchar ( 10 ),
@BuildID BigInt ,
@FromDanYuanHao int ,
@ToDanYuanHao int ,
@FromLouCengHao int ,
@ToLouCengHao int ,
@FromFangJianHao int ,
@ToFangJianHao int ,
@CodeBefore varchar ( 50 ), -- 代码前缀
@AddressBefore varchar ( 50 ), -- 地址前缀
@HouseType varchar ( 10 ),
@HouseToward varchar ( 10 ),
@ConstArea decimal ( 18 , 2 ), -- 建筑面积
@UseArea decimal ( 18 , 2 ), -- 使用面积
@FittCond varchar ( 50 ), -- 装修情况
@TunureType varchar ( 10 ), -- 权属类型
@HouseOwnerShip varchar ( 10 ), -- 房屋归属
@UseType varchar ( 10 ), -- 用途
@Facilities varchar ( 50 ), -- 配套设施
@Estate varchar ( 20 ), -- 房产证
@EstateType varchar ( 10 ) -- 产权形式
-- @HouseNumber varchar(15),
-- @HouseUnit varchar(10),
-- @Floor int,
-- @HouseNum int,
-- @Address varchar(50),
)
AS
BEGIN TRANSACTION ROOMPLADD
DECLARE @DanYuanHao int ;
SET @DanYuanHao = @FromDanYuanHao

DECLARE @LouCengHao int ;
SET @LouCengHao = @FromLouCengHao

DECLARE @FangJianHao int ;
SET @FangJianHao = @FromFangJianHao

DECLARE @HouseNumber varchar ( 15 );
DECLARE @Address varchar ( 50 );
DECLARE @FangJianHaoString varchar ( 10 );
DECLARE @Error int

WHILE ( @DanYuanHao <= @ToDanYuanHao )
BEGIN
WHILE ( @LouCengHao <= @ToLouCengHao )
BEGIN
WHILE ( @FangJianHao <= @ToFangJianHao )
BEGIN
if ( lEN ( cast ( @FangJianHao as varchar )) = 1 )
BEGIN
SET @FangJianHaoString = ' 0 ' + cast ( @FangJianHao as varchar )
END
ELSE
BEGIN
SET @FangJianHaoString = cast ( @FangJianHao as varchar )
END
SET @HouseNumber = ( @CodeBefore + @DaiMaFenGeHao + cast ( @DanYuanHao as varchar ) + cast ( @DaiMaFenGeHao as varchar ) + cast ( @LouCengHao as varchar ) + @FangJianHaoString )
SET @Address = ( @AddressBefore + cast ( @DanYuanHao as varchar ) + ' 单元 ' + cast ( @LouCengHao as varchar ) + @FangJianHaoString )
insert into WC_House_Info
(
HouseNumber,
BuildID,
HouseType,
HouseUnit,
Floor ,
HouseNum,
ConstArea,
UseArea,
Address,
TunureType,
UseType,
HouseToward,
FittCond,
Facilities,
HouseOwnerShip,
Estate,
EstateType
)
values
(
@HouseNumber ,
@BuildID ,
@HouseType ,
@DanYuanHao ,
@LouCengHao ,
@FangJianHaoString ,
@ConstArea ,
@UseArea ,
@Address ,
@TunureType ,
@UseType ,
@HouseToward ,
@FittCond ,
@Facilities ,
@HouseOwnerShip ,
@Estate ,
@EstateType
)
SET @FangJianHao = @FangJianHao + 1
Set @Error = @@error

END
SET @FangJianHao = @FromFangJianHao
SET @LouCengHao = @LouCengHao + 1

END
SET @FangJianHao = @FromFangJianHao
SET @LouCengHao = @FromLouCengHao
SET @DanYuanHao = @DanYuanHao + 1
END
IF ( @Error > 0 )
BEGIN
ROLLBACK TRANSACTION ROOMPLADD
END
ELSE
COMMIT TRANSACTION ROOMPLADD
GO

 

 

 

 

 

转载于:https://www.cnblogs.com/qinweilong/archive/2010/06/02/1750043.html