今天收集了最为常见的存储过程代码，拿出来和大家分享一下，希望大家能够喜欢！最长用的存储过程的写法，有输出参数
代码
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
}
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
