今天整理机器,发现以前写的一个小工具,用来生成MS SQL Server 的存储过程,把它放到这里,或许可以帮助写朋友减轻些工作,功能介绍:
1、自动根据数据库表结构及关联关系创建存储过程,您只要选择表,然后点击一个按钮便会生成常用的存储过程。
2、可以生成分页的查询存储过程,分页存储过程按照主键、索引、外键、以及全部字段进行分页。
3、根据数据库表主键,索引,外键创建delete、update的存储过程。
4、生成Insert存储过程。
运行环境:.NET framework 1.1,Ms SQL Server 2000
以pubs数据库中的authors表为例,生成存储过程如下:
CREATE
PROCEDURE
DBO.Get_authors_All
AS
SELECT
au_id,
au_lname,
au_fname,
phone,
address,
city,
state,
zip,
contract
FROM
authors
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Get_authors_Count
AS
SELECT
COUNT
(
*
)
FROM
authors
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Get_authors_All_Paged
@__intPageNumber
int
,
@__intPageSize
int
AS
DECLARE
@intStart
int
, @intEnd
int
SET
@intStart
=
(((@__intPageNumber
-
1
)
*
@__intPageSize)
+
1
)
SET
@intEnd
=
(@intStart
+
@__intPageSize
-
1
)
DECLARE
@TemporaryTable
TABLE
(Row
INT
IDENTITY
(
1
,
1
)
PRIMARY
KEY
,
au_id ID
NOT
NULL
)
![None.gif](/Images/OutliningIndicators/None.gif)
INSERT
INTO
@TemporaryTable
SELECT
au_id
FROM
authors
![None.gif](/Images/OutliningIndicators/None.gif)
SELECT
b.au_id,
b.au_lname,
b.au_fname,
b.phone,
b.address,
b.city,
b.state,
b.zip,
b.contract
FROM
@TemporaryTable a
INNER
JOIN
authors b
ON
a.au_id
=
b.au_id
WHERE
(a.Row
>=
@intStart)
AND
(a.Row
<=
@intEnd)
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Get_authors_ByPrimaryKey
@au_id ID
![None.gif](/Images/OutliningIndicators/None.gif)
AS
SELECT
au_id,
au_lname,
au_fname,
phone,
address,
city,
state,
zip,
contract
FROM
authors
WHERE
au_id
=
@au_id
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Get_authors_All_By_aunmind
@au_lname
VARCHAR
(
40
),
@au_fname
VARCHAR
(
20
) ![None.gif](/Images/OutliningIndicators/None.gif)
AS
SELECT
au_id,
au_lname,
au_fname,
phone,
address,
city,
state,
zip,
contract
FROM
authors
WHERE
au_lname
=
@au_lname
AND
au_fname
=
@au_fname
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Get_authors_Count_By_aunmind
@au_lname
VARCHAR
(
40
),
@au_fname
VARCHAR
(
20
)
AS
SELECT
COUNT
(
*
)
FROM
authors
WHERE
au_lname
=
@au_lname
AND
au_fname
=
@au_fname
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Get_authors_Paged_By_aunmind
@__intPageNumber
int
,
@__intPageSize
int
,
@au_lname
VARCHAR
(
40
),
@au_fname
VARCHAR
(
20
)
AS
DECLARE
@intStart
int
, @intEnd
int
SET
@intStart
=
(((@__intPageNumber
-
1
)
*
@__intPageSize)
+
1
)
SET
@intEnd
=
(@intStart
+
@__intPageSize
-
1
)
DECLARE
@TemporaryTable
TABLE
(Row
INT
IDENTITY
(
1
,
1
)
PRIMARY
KEY
,
au_id ID
NOT
NULL
)
![None.gif](/Images/OutliningIndicators/None.gif)
INSERT
INTO
@TemporaryTable
SELECT
au_id
FROM
authors
WHERE
au_lname
=
@au_lname
AND
au_fname
=
@au_fname
![None.gif](/Images/OutliningIndicators/None.gif)
SELECT
b.au_id,
b.au_lname,
b.au_fname,
b.phone,
b.address,
b.city,
b.state,
b.zip,
b.contract
FROM
@TemporaryTable a
INNER
JOIN
authors b
ON
a.au_id
=
b.au_id
WHERE
(a.Row
>=
@intStart)
AND
(a.Row
<=
@intEnd)
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Delete_authors_All
AS
DELETE
FROM
authors
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Delete_authors_ByPrimaryKey
@au_id ID
AS
DELETE
FROM
authors
WHERE
au_id
=
@au_id
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Delete_authors_By_aunmind
@au_lname
VARCHAR
(
40
),
@au_fname
VARCHAR
(
20
)
AS
DELETE
FROM
authors
WHERE
au_lname
=
@au_lname
AND
au_fname
=
@au_fname
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Update_authors_All
@au_id ID,
@au_lname
VARCHAR
(
40
),
@au_fname
VARCHAR
(
20
),
@phone
CHAR
(
12
),
@address
VARCHAR
(
40
)
=
NULL
,
@city
VARCHAR
(
20
)
=
NULL
,
@state
CHAR
(
2
)
=
NULL
,
@zip
CHAR
(
5
)
=
NULL
,
@contract
BIT
AS
UPDATE
authors
SET
au_id
=
@au_id,
au_lname
=
@au_lname,
au_fname
=
@au_fname,
phone
=
@phone,
address
=
@address,
city
=
@city,
state
=
@state,
zip
=
@zip,
contract
=
@contract
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Update_authors_ByPrimaryKey
@au_id ID,
@au_lname
VARCHAR
(
40
),
@au_fname
VARCHAR
(
20
),
@phone
CHAR
(
12
),
@address
VARCHAR
(
40
)
=
NULL
,
@city
VARCHAR
(
20
)
=
NULL
,
@state
CHAR
(
2
)
=
NULL
,
@zip
CHAR
(
5
)
=
NULL
,
@contract
BIT
AS
UPDATE
authors
SET
au_id
=
@au_id,
au_lname
=
@au_lname,
au_fname
=
@au_fname,
phone
=
@phone,
address
=
@address,
city
=
@city,
state
=
@state,
zip
=
@zip,
contract
=
@contract
WHERE
au_id
=
@au_id
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Update_authors_By_aunmind
@au_id ID,
@au_lname
VARCHAR
(
40
),
@au_fname
VARCHAR
(
20
),
@phone
CHAR
(
12
),
@address
VARCHAR
(
40
)
=
NULL
,
@city
VARCHAR
(
20
)
=
NULL
,
@state
CHAR
(
2
)
=
NULL
,
@zip
CHAR
(
5
)
=
NULL
,
@contract
BIT
AS
UPDATE
authors
SET
au_id
=
@au_id,
au_lname
=
@au_lname,
au_fname
=
@au_fname,
phone
=
@phone,
address
=
@address,
city
=
@city,
state
=
@state,
zip
=
@zip,
contract
=
@contract
WHERE
au_lname
=
@au_lname
AND
au_fname
=
@au_fname
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE
DBO.Insert_authors
@au_id ID,
@au_lname
VARCHAR
(
40
),
@au_fname
VARCHAR
(
20
),
@phone
CHAR
(
12
),
@address
VARCHAR
(
40
)
=
NULL
,
@city
VARCHAR
(
20
)
=
NULL
,
@state
CHAR
(
2
)
=
NULL
,
@zip
CHAR
(
5
)
=
NULL
,
@contract
BIT
AS
INSERT
INTO
authors
(
au_id,
au_lname,
au_fname,
phone,
address,
city,
state,
zip,
contract
)
VALUES
(
@au_id,
@au_lname,
@au_fname,
@phone,
@address,
@city,
@state,
@zip,
@contract
)
RETURN
0
![None.gif](/Images/OutliningIndicators/None.gif)
执行文件下载:http://files.cnblogs.com/mist/Generator.rar
源代码下载:http://files.cnblogs.com/mist/SqlSPGen.rar
如果有任何问题或者建议请给我发邮件,邮件地址在软件里面有,就不列到这里了!