最长用的存储过程的写法，有输出参数
代码
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
