SQLSERVER存储过程使用说明书
引言
首先介绍一下什么是存储过程：存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，并且这样的语句是放在数据库中的，还可以根据条件执行不同SQL语句，那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用execute,即可自动完成命令。
请大家先看一个小例子：
create proc query_book
as
select * from book
go
--调用存储过程
exec query_book
请大家来了解一下存储过程的语法。
Create PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
一、参数简介
1、procedure_name
新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。
要创建局部临时过程，可以在 procedure_name 前面加一个编号
符 (#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两
个编号符 (##procedure_name)。完整的名称（包括 # 或 ##）不能超过 128 个字符。指定过程所有者的名称是可选的。
2、;number
是可选的整数，用来对同名的过程分组，以便用一条 Drop PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。Drop PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应
在 procedure_name 前后使用适当的定界符。
3、@parameter
过程中的参数。在 Create PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2100 个参数。
使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。
4、data_type
参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。
说明：对于可以是cursor 数据类型的输出参数，没有最大数目的限制。
5、VARYING
指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。
6、default
参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。
7、OUTPUT
表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。
8、n
表示最多可以指定 2100 个参数的占位符。
9、
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIO N}
RECOMPILE 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。
ENCRYPTION 表示 SQL Server 加密 syscomments 表中包
含 Create PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作
为 SQL Server 复制的一部分发布。
说明：在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。
10、FOR REPLICATION
指定不能在订阅服务器上执行为复制创建的存储过程。.使
用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。
11、AS
指定过程要执行的操作。
12、sql_statement
过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。
13、n
是表示此过程可以包含多条 Transact-SQL 语句的占位符。
14、注释
/*和*/之间的为注释，可以包含一行和多行的说明文字。
15、其他说明
存储过程的最大大小为 128 MB。
二、存储过程的优点都有哪些呢？
1. 存储过程只在创造时进行编译即可，以后每次执行存储过程都不需再重新编译，而我们通常使用的SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2. 经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用SP来封装数据库操作。当对数据库进行复杂操作时(如对多个表进行
Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。可以极大的提高数据库的使用效率，减少程序的执行时间，这一点在较大数据量的数据库的操作中是非常重要的。在代码上看，SQL语句和程序代码语句的分离，可以提高程序代码的可读性。
3. 存储过程可以设置参数，可以根据传入参数的不同重复使用同一个存储过程，从而高效的提高代码的优化率和可读性。
4. 安全性高,可设定只有某此用户才具有对指定存储过程的使用权存储过程的种类：
（1）系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,如 sp_help就是取得指定对象的相关信息。
（2）扩展存储过程以XP_开头,用来调用操作系统提供的功能
exec master..xp_cmdshell 'ping 10.8.16.1'
（3）用户自定义的存储过程,这是我们所指的存储过程常用格式
模版：
Create procedure procedue_name [@parameter data_type][output][with]{recompil e|encryption} as sql_statement
解释：output：表示此参数是可传回的
with {recompile|encryption} recompile:表示每次执行此存储过程时都重新编译一次;encryption:所创建的存储过程的内容会被加密。
三、实例讲解
实例1：只返回单一记录集的存储过程。
要求1：查询表bankMoney的内容的存储过程
create procedure sp_query_bankMoney
as
select * from bankMoney
go
exec sp_query_bankMoney
注* 在使用过程中只需要把中的SQL语句替换为存储过程名，就可以了很方便吧！
实例2（向存储过程中传递参数）：
加入一笔记录到表bankMoney，并查询此表中userID= Zhangsan的所有存款的总金额。
Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar (20),@param4 int,@param5 int output
with encryption ---------加密
as
insert bankMoney (id,userID,sex,Money) Values(@param1,@param2,@param3, @p aram4)
select @param5=sum(Money) from bankMoney where userID='Zhangsan' go
在SQL Server查询分析器中执行该存储过程的方法是：
declare @total_price int
exec insert_bank '004','Zhangsan','男',100,@total_price output
print '总余额为'+convert(varchar,@total_price)
go
在这里再啰嗦一下存储过程的3种传回值（方便正在看这个例子的朋友不用再去查看语法内容）:
1.以Return传回整数
2.以output格式传回参数
3.Recordset
传回值的区别:
output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。
实例3：使用带有复杂 Select 语句的简单过程
下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。
USE pubs
IF EXISTS (Select name FROM sysobjects
Where name = 'au_info_all' AND type = 'P')
Drop PROCEDURE au_info_all
GO
Create PROCEDURE au_info_all
AS
Select au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
au_info_all 存储过程可以通过以下方法执行：
EXECUTE au_info_all
-- or
EXEC au_info_all
如果该过程是批处理中的第一条语句，则可使用：
au_info_all
实例4：使用带有参数的简单过程
Create PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
Select au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
Where au_fname = @firstname
AND au_lname = @lastname
GO
au_info 存储过程可以通过以下方法执行：
EXECUTE au_info 'Dull', 'Ann'
-- or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- or
EXEC au_info 'Dull', 'Ann'
-- or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'
如果该过程是批处理中的第一条语句，则可使用：
au_info 'Dull', 'Ann'
-- or
au_info @lastname = 'Dull', @firstname = 'Ann'
-- or
au_info @firstname = 'Ann', @lastname = 'Dull'
实例5：使用带有通配符参数的简单过程
Create PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
Select au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
Where au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
au_info2 存储过程可以用多种组合执行。下面只列出了部分组合：EXECUTE au_info2
-- or
EXECUTE au_info2 'Wh%'
-- or
EXECUTE au_info2 @firstname = 'A%'
-- or
EXECUTE au_info2 '[CK]ars[OE]n'
-- or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- or
EXECUTE au_info2 'H%', 'S%'
四、系统存储过程
用户存储过程: 用户也可以编写自己的存储过程，并把它存放在数据库中，供客户端调用。
以上主要是用户存储过程，下面介绍一下系统存储过程。
系统存储过程: SQL Server本身提供了一些存储过程，用于管理有关数据库和用户的信息。
它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任务或其它的系统管理任务。
系统存储过程可以在任意一个数据库中执行。创建并存放于系统数据库master中，并且名称以sp_或者xp_开头。
部分系统存储过程：
sp_addtype：用于定义一个用户定义数据类型。
sp_configure：用于管理服务器配置选项设置。
xp_sendmail：用于发送电子邮件或寻呼信息。
sp_stored_procedures：用于返回当前数据库中的存储过程的清单。
sp_help：用于显示参数清单和其数据类型。
sp_helptext：用于显示存储过程的定义文本。
sp_rename：用于修改当前数据库中用户对象的名称。
Sp_who:用于显示使用数据库的当前用户
sp_help：用于显示参数清单和其数据类型。
sp_depends：用于显示存储过程依据的对象或者依据存储过程的对象
sp_helptext：用于显示存储过程的定义文本。
一个调用系统存储过程的例子：
exec sp_helptext query_book
五、注意事项:
存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象的语句，
即在存储过程中一般不能含有以下语句：
CREATE TABLE ；CREATE VIEW ；CREATE DEFAULT ；
CREATE RULE ；CREATE TRIGGER ；CREATE PROCEDURE
六、返回值和状态信息
无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。
如果存储过程执行成功，返回的结果码是0；如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。
我们在创建存储过程时，也可以定义自己的状态码和错误信息。
执行存储过程：
例：执行带参数的存储过程,查询大于岁的学生
create proc show;3 ( @pno char(6) )
as
select * from person where Pno = @pno
exec show;3 4
例：CREATE Procedure sp_getstu;1
AS
SELECT * FROM 学生
例：带参数的存储过程,查询大于指定年龄的学生
CREATE proc sp_getstu;2 (@sage int)
AS
SELECT * FROM 学生WHERE 年龄> @sage
例: 带输出参数的存储过程,查询指定学生的年龄
CREATE proc sp_getstu;3 ( @name char(10) , @age int output ) AS
SELECT @age=年龄FROM 学生WHERE 姓名= @name
Declare @sage int
Exec sp_getstu;3 '张三',@sage
Print @sage
例：带参数和返回状态值的存储过程。
CREATE PROCedure sp_getstu;3 (@sage int =NULL )
AS
IF @sage IS NULL
BEGIN
PRINT '必须提供一个数值作参数！'
RETURN 13
END
IF NOT EXISTS (SELECT * FROM student WHERE sage > @sage) BEGIN
PRINT '没有满足条件的记录！'
RETURN -103
END
SELECT * FROM student WHERE sage > @sage
RETURN 0
DECLARE @status int
EXECUTE @status=sp_getstu;3 22
print @status
七、存储过程中游标的使用
1、需要游标的数据操作
当select语句的结果中包含多个元组时，使用游标可以逐个存取这些元组活动集：select语句返回的元组的集合
当前行：活动集中当前处理的那一行。游标即是指向当前行的指针。
2、游标分类
滚动游标：游标的位置可以来回移动，可在活动集中取任意元组。
非滚动游标：只能在活动集中顺序地取下一个元组。
更新游标：数据库对游标指向的当前行加锁，当程序读下一行数据时，本行数据解锁，下一行数据加锁。
3、定义与使用游标的语句
declare ：
declare 游标名[scroll] cursor for select语句[for update [of列表名]] 定义一个游标，使之对应一个select语句
for update任选项，表示该游标可用于对当前行的修改与删除open
打开一个游标，执行游标对应的查询，结果集合为该游标的活动集
open 游标名
fetch
在活动集中将游标移到特定的行，并取出该行数据放到相应的变量中
fetch [next | prior | first | last | current | relative n | absolute m] 游标名into [变量表]
close
关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open 语句
close 游标名
deallocate
删除游标，以后不能再对该游标执行open语句
deallocate 游标名
@@FETCH_STATUS
返回被FETCH 语句执行的最后游标的状态.
0 fetch语句成功
-1 fetch语句失败
-2 被提取的行不存在
4、游标实例
例:查询电子商务系学生信息,性别为女输出为female,否则输出为male？
declare c1 cursor for select sno,sname,ssex from student where sdept='ec'
declare @sno char(10),@sname char(10),@ssex char(2)
Open c1
Fetch c1 into @sno,@sname,@ssex
While @@fetch_status==0
Begin
if @ssex='女'
begin set @ssex='female' end
else
begin set @ssex='male' end
Select @sno,@sname ,@ssex
Fetch c1 into @sno,@sname,@ssex
end
例：
ALTER PROC [dbo].[dnt_UserRecoveryByUserName]
@username NVARCHAR(50)
AS
BEGIN
DECLARE @uid INT;
DECLARE @tid INT;
DECLARE @replies INT;
DECLARE @temp varchar(50);
SET @uid = (SELECT TOP(1) uid FROM dnt_users WHERE username = '@username');
SET @tid = 0;
SET @replies = 0;
UPDATE dnt_users SET accessmasks = 0 WHERE uid = @uid;
UPDATE dnt_userforum SET groupid = 5 WHERE groupid = 4 AND uid = @uid;
UPDATE dnt_posts SET invisible = 0 WHERE invisible = -1 AND posterid = @uid;
-- 定义一游标
DECLARE Ctemp CURSOR FOR SELECT tid FROM dnt_topics WHERE posterid = @uid FOR READ only -- FOR UPDATE
OPEN Ctemp
FETCH next FROM Ctemp INTO @tid;
WHILE (@@fetch_status = 0)
BEGIN
SET @replies = (SELECT COUNT(1) FROM dnt_posts WHERE tid = @tid AND layer > 0);
UPDATE dnt_topics SET replies = @replies WHERE posterid = @uid AND tid = @tid;
FETCH next FROM Ctemp INTO @tid;
END
CLOSE Ctemp;
DEALLOCATE Ctemp; END
SQLServer 存储过程返回值总结 1. 存储过程没有返回值的情况 (即存储过程语句中没有 return 之类的语句用方法 int count = ExecuteNonQuery(..执行存储过程其返回值只有两种情况 (1假如通过查询分析器执行该存储过程,在显示栏中假如有影响的行数,则影响几行 count 就是几 (2假如通过查询分析器执行该存储过程, 在显示栏中假如显示 ' 命令已成功完成。 ' 则 count = -1;在显示栏中假如有查询结果,则 count = -1 总结:A.ExecuteNonQuery(该方法只返回影响的行数,假如没有影响行数,则该方法的返回值只能是 -1,不会为 0。 B.不论 ExecuteNonQuery(方法是按照 CommandType.StoredProcedure 或者 CommandType.Text 执行, 其效果和 A 一样。 --------------------------------------------------------------------------------------------------------------------------------------------------- 2. 获得存储过程的返回值 --通过查询分析器获得 (1不带任何参数的存储过程 (存储过程语句中含有 return ---创建存储过程 CREATE PROCEDURE testReturn AS return 145 GO ---执行存储过程
DECLARE @RC int exec @RC=testReturn select @RC ---说明 查询结果为 145 (2带输入参数的存储过程 (存储过程语句中含有 return ---创建存储过程 create procedure sp_add_table1 @in_name varchar(100, @in_addr varchar(100, @in_tel varchar(100 as if(@in_name = '' or @in_name is null return 1 else begin insert into table1(name,addr,tel values(@in_name,@in_addr,@in_tel return 0
动态语句基本语法 1 :普通SQL语句可以用exec执行 Select * from tableName exec('select * from tableName') exec sp_executesqlN'select * from tableName' -- 请注意字符串前一定要加N 2:字段名，表名，数据库名之类作为变量时，必须用动态SQL declare @fnamevarchar(20) set @fname = 'FiledName' Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。exec('select ' + @fname + ' from tableName') -- 请注意加号前后的单引号的边上加空格 当然将字符串改成变量的形式也可 declare @fnamevarchar(20) set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) set @s = 'select ' + @fname + ' from tableName' exec(@s) -- 成功 exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) set @s = 'select ' + @fname + ' from tableName' exec(@s) -- 成功 exec sp_executesql @s -- 此句正确 3. 输出参数 declare @numint, @sqlsnvarchar(4000) set @sqls='select count(*) from tableName' exec(@sqls) --如何将exec执行结果放入变量中？ declare @numint, @sqlsnvarchar(4000) set @sqls='select @a=count(*) from tableName ' execsp_executesql @sqls,N'@aint output',@num output select @num 1 :普通SQL语句可以用Exec执行例: Select * from tableName Exec('select * from tableName')
SQL Server在存储过程中编写事务处理代码的三种方法 SQL Server中数据库事务处理是相当有用的，鉴于很多SQL初学者编写的事务处理代码存往往存在漏洞，本文我们介绍了三种不同的方法，举例说明了如何在存储过程事务处理中编写正确的代码。希望能够对您有所帮助。 在编写SQL Server 事务相关的存储过程代码时，经常看到下面这样的写法： begin tran update statement 1 ... update statement 2 ... delete statement 3 ... commit tran 这样编写的SQL存在很大隐患。请看下面的例子： create table demo(id int not null) go begin tran insert into demo values (null) insert into demo values (2) commit tran go 执行时会出现一个违反not null 约束的错误信息，但随后又提示(1 row(s) affected)。我们执行select * from demo 后发现insert into demo values(2) 却执行成功了。这是什么原因呢? 原来SQL Server在发生runtime 错误时，默认会rollback引起错误的语句，而继续执行后续语句。 如何避免这样的问题呢？ 有三种方法：
1. 在事务语句最前面加上set xact_abort on set xact_abort on begin tran update statement 1 ... update statement 2 ... delete statement 3 ... commit tran go 当xact_abort 选项为on 时，SQL Server在遇到错误时会终止执行并rollback 整个事务。 2. 在每个单独的DML语句执行后，立即判断执行状态，并做相应处理。 begin tran update statement 1 ... if@@error<>0 begin rollback tran goto labend end delete statement 2 ... if@@error<>0 begin rollback tran goto labend end commit tran labend: go 3. 在SQL Server 2005中，可利用try...catch 异常处理机制。
SqlServer 使用存储过程导出为Excel 一个脱离office组件的可以将语句结果导出到Excel的过程 --1.执行时所连接的服务器决定文件存放在哪个服务器 [sql]view plain copy print? 1.CREATE PROC ExportFile 2. @QuerySql VARCHAR(max) 3. ,@Server VARCHAR(20) 4. ,@User VARCHAR(20) = 'sa' 5. ,@Password VARCHAR(20) 6. ,@FilePath NVARCHAR(100) = 'c:\ExportFile.csv' 7.AS 8.DECLARE @tmp VARCHAR(50) = '[##Table' + CONVERT(VARCHAR(36),NEWID())+']' 9.BEGIN TRY 10.DECLARE @Sql VARCHAR(max),@DataSource VARCHAR(max)=''; 11.--判断是否为远程服务器 12. IF @Server <> '.'AND @Server <> '127.0.0.1' 13.SET @DataSource = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source='+@Server+';User ID='+@Us er+';Password='+@Password+''').' 14.--将结果集导出到指定的数据库 15.SET @Sql = REPLACE(@QuerySql,' from ',' into '+@tmp+ ' from ' + @DataSource) 16. PRINT @Sql 17.EXEC(@Sql) 18. 19.DECLARE @Columns VARCHAR(max) = '',@Data NVARCHAR(max)='' 20.SELECT @Columns = @Columns + ',''' + name +''''--获取列名（xp_cmdshell导出文件没有列名） 21. ,@Data = @Data + ',Convert(Nvarchar,[' + name +'])'--将结果集所在的字段更新为nvarchar（避 免在列名和数据union的时候类型冲突） 22.FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..'+@tmp) 23.SELECT @Data = 'SELECT ' + SUBSTRING(@Data,2,LEN(@Data)) + ' FROM ' + @tmp 24.SELECT @Columns = 'Select ' + SUBSTRING(@Columns,2,LEN(@Columns)) 25.--使用xp_cmdshell的bcp命令将数据导出 26.EXEC sp_configure 'xp_cmdshell',1 27. RECONFIGURE 28.DECLARE @cmd NVARCHAR(4000) = 'bcp "' + @Columns+' Union All ' + @Data+'" queryout ' + @Fi lePath + ' -c' + CASE WHEN RIGHT(@FilePath,4) = '.csv'THEN' -t,'ELSE''END + ' -T' 29. PRINT @cmd 30.exec sys.xp_cmdshell @cmd 31.EXEC sp_configure 'xp_cmdshell',0 32. RECONFIGURE 33.EXEC('DROP TABLE ' + @tmp) 34.END TRY 35.BEGIN CATCH
列出SQLServer数据库中所有的存储过程 Dim cn As rdoConnection Dim ps As rdoPreparedStatement Dim rs As rdoResultset Dim strConnect As String Dim strSQL As String '利用 DSNless 连接到 pubs 数据库 '改变参数以适合你自己的 SQL Server strConnect = "Driver={SQL Server}; Server=myserver; " & "Database=pubs; Uid=sa; Pwd=" Set cn = rdoEnvironments(0).OpenConnection(dsName:="", Prompt:=rdDriverNoPrompt, ReadOnly:=False, Connect:=strConnect) strSQL = "Select https://www.doczj.com/doc/3e5057507.html,,https://www.doczj.com/doc/3e5057507.html,,https://www.doczj.com/doc/3e5057507.html,,sc.length " & "FROM syscolumns sc,master..systypes st,sysobjects so " & "WHERE sc.id in (select id from sysobjects where type ='P')" & " AND so.type ='P' " & "AND sc.id = so.id " & "AND sc.type = st.type " & "AND sc.type <> 39" Set ps = cn.CreatePreparedStatement("MyPs", strSQL) Set rs = ps.OpenResultset(rdOpenStatic) list1.AddItem "SP Name,Param Name,Data Type,Length" While Not rs.EOF list1.AddItem rs(0) & " , " & rs(1) & " , " & rs(2) & " , " & rs(3) rs.MoveNext Wend rs.Close Set rs = Nothing cn.Close Set cn = Nothing 【
Java调用SQL Server存储过程 Java调用SQL Server的存储过程详解，主要内容： ●使用不带参数的存储过程 ●使用带有输入参数的存储过程 ●使用带有输出参数的存储过程 ●使用带有返回状态的存储过程 ●使用带有更新计数的存储过程 1.使用不带参数的存储过程 使用JDBC 驱动程序调用不带参数的存储过程时，必须使用call SQL 转义序列。不带参数的call 转义序列的语法如下所示： 实例：在SQL Server 2005 AdventureWorks示例数据库中创建以下存储过程： 此存储过程返回单个结果集，其中包含一列数据（由Person.Contact 表中前十个联系人的称呼、名称和姓氏组成）。 在下面的实例中，将向函数传递AdventureWorks示例数据库的打开连接，然后使用executeQuery方法调用GetContactFormalNames存储过程。
2.使用带有输入参数的存储过程 使用JDBC 驱动程序调用带参数的存储过程时，必须结合SQLServerConnection 类的prepareCall方法使用call SQL转义序列。带有IN参数的call转义序列的语法如下所示： 构造call转义序列时，请使用?（问号）字符来指定IN参数。此字符充当要传递给该存储过程的参数值的占位符。可以使用SQLServerPreparedStatement类的setter方法之一为参数指定值。可使用的setter方法由IN参数的数据类型决定。 向setter方法传递值时，不仅需要指定要在参数中使用的实际值，还必须指定参数在存储过程中的序数位置。例如，如果存储过程包含单个IN参数，则其序数值为1。如果存储过程包含两个参数，则第一个序数值为1，第二个序数值为2。 作为调用包含IN参数的存储过程的实例，使用SQL Server 2005 AdventureWorks示例数据库中的uspGetEmployeeManagers存储过程。此存储过程接受名为EmployeeID的单个输入参数（它是一个整数值），然后基于指定的EmployeeID返回雇员及其经理的递归列表。下面是调用此存储过程的Java代码：
USE[zhuhaioa7-4] GO /****** Object: StoredProcedure [dbo].[proc_records] Script Date: 12/26/2014 20:31:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure[dbo].[proc_records] as DECLARE@map_table table(r_key varchar(100),r_value varchar(50)) DECLARE@type_id varchar(40) DECLARE@type_name varchar(50) DECLARE@project_id varchar(40) DECLARE@project_name varchar(50) DECLARE@payTypeMoney numeric(16, 2) DECLARE@sumPayTypeMoney numeric(16, 2) BEGIN set@payTypeMoney= 0 set@sumPayTypeMoney= 0 --查询项目列表 DECLARE project_cursor CURSOR for select ep_id,ep_name from AB_engineeringPhase where account_id='2' open project_cursor fetch next from project_cursor into@project_id,@project_name while@@FETCH_STATUS= 0 begin
SQLSERVER存储过程使用说明书 引言 首先介绍一下什么是存储过程：存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，并且这样的语句是放在数据库中的，还可以根据条件执行不同SQL语句，那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用execute,即可自动完成命令。 请大家先看一个小例子： create proc query_book as select * from book go --调用存储过程 exec query_book 请大家来了解一下存储过程的语法。 Create PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ] 一、参数简介 1、procedure_name 新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。 要创建局部临时过程，可以在 procedure_name 前面加一个编号 符 (#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两
个编号符 (##procedure_name)。完整的名称（包括 # 或 ##）不能超过 128 个字符。指定过程所有者的名称是可选的。 2、;number 是可选的整数，用来对同名的过程分组，以便用一条 Drop PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。Drop PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应 在 procedure_name 前后使用适当的定界符。 3、@parameter 过程中的参数。在 Create PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2100 个参数。 使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。 4、data_type 参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。 说明：对于可以是cursor 数据类型的输出参数，没有最大数目的限制。 5、VARYING 指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。 6、default 参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。 7、OUTPUT
SqlServer 使用存储过程实现插入或更新语句 存储过程的功能非常强大，在某种程度上甚至可以替代业务逻辑层， 接下来就一个小例子来说明，用存储过程插入或更新语句。 1、数据库表结构 2、创建存储过程 1Create proc[dbo].[sp_Insert_Student] 2@No char(10), 3@Name varchar(20), 4@Sex char(2), 5@Age int, 6@rtn int output 7as 8declare 9@tmpName varchar(20), 10@tmpSex char(2), 11@tmpAge int 12 13if exists(select*from Student where No=@No) 14begin 15select@tmpName=Name,@tmpSex=Sex,@tmpAge=Age from Student where No=@No 16if ((@tmpName=@Name) and (@tmpSex=@Sex) and (@tmpAge=@Age)) 17begin 18set@rtn=0 19end 20else 21begin 22update Student set Name=@Name,Sex=@Sex,Age=@Age
where No=@No 23set@rtn=2 24end 25end 26else 27begin 28insert into Student values(@No,@Name,@Sex,@Age) 29set@rtn=1 30end 3、调用存储过程 1declare@rtn int 2exec sp_Insert_Student '1101','张三','男',23,@rtn output 3 4if@rtn=0 5print'已经存在相同的。' 6else if@rtn=1 7print'插入成功。' 8else 9print'更新成功'
SQL Server存储过程的基本概念以及语法【转】 存储过程的概念 SQL Server提供了一种方法,它可以将一些固定的操作集中起来由SQL Server 数据库服务器来完成,以实现某个任务,这种方法就是存储过程。 存储过程是SQL语句和可选控制流语句的预编译集合,存储在数据库中,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行以及其他强大的编程功能。 在SQL Server中存储过程分为两类:即系统提供的存储过程和用户自定义的存储过程。 可以出于任何使用SQL语句的目的来使用存储过程,它具有以下优点: 可以在单个存储过程中执行一系列SQL语句。 可以从自己的存储过程内引用其他存储过程,这可以简化一系列复杂语句。 存储过程在创建时即在服务器上进行编译,所以执行起来比单个SQL语句快,而且减少网络通信的负担。 安全性更高。 创建存储过程 在SQL Server中,可以使用三种方法创建存储过程: ①使用创建存储过程向导创建存储过程。 ②利用SQL Server 企业管理器创建存储过程。 ③使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。
下面介绍使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程 创建存储过程前,应该考虑下列几个事项: ①不能将 CREATE PROCEDURE 语句与其它SQL语句组合到单个批处理中。 ②存储过程可以嵌套使用,嵌套的最大深度不能超过32层。 ③创建存储过程的权限默认属于数据库所有者,该所有者可将此权限授予其他用户。 ④存储过程是数据库对象,其名称必须遵守标识符规则。 ⑤只能在当前数据库中创建存储过程。 ⑥一个存储过程的最大尺寸为128M。 使用CREATE PROCEDURE创建存储过程的语法形式如下: QUOTE: CREATE PROC[EDURE]procedure_name[;number][;number] [{@parameter data_type}[VARYING][=default][OUTPUT]][,...n] WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FOR REPLICATION] AS sql_statement [ ...n ] 用CREATE PROCEDURE创建存储过程的语法参数的意义如下:
理论知识：开始 一、TRUNCATE 快速地从一堆表中删除所有行。它和在每个表上进行无条件的DELETE 有同样的效果，不过因为它不做表扫描，因而快得多。在大表上最有用。 二、Select INTO 建表 把一个表中的数据复制到另外一个表中。 三、Insert INTO Select 四、补充：临时表 临时表存储在系统数据库tempdb中 临时表会被系统隐式地丢弃 五、存储过程(**) 一、简介： 存储过程（Stored Procedure），是一组为了完成特定功能的SQL 语句，集经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数，如果该存储过程带有参数来执行它， 在SQL Server 的系列版本中，存储过程分为两类：系统提供的存储过程和用户自定义存储过程。 系统SP，主要存储master 数据库中，并以sp_为前缀并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server。用户自定义存储过程是由用户创建，并能完成某一特定功能，如：查询用户所需数据信息的存储过程。 存储过程具有以下优点: 1.存储过程允许标准组件式编程(模块化设计) 存储过程在被创建以后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句，而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响。因为应用程序源代码只包含存储过程的调用语句，从而极大地提高了程序的可移植性。 2.存储过程能够实现快速的执行速度 如果某一操作包含大量的Transaction-SQL 代码,，或分别被多次执行，那么存储过程要比批处理的执行速度快很多，因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析优化，并给出最终被存在系统表中的执行计划，而批处理的Transaction-SQL 语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。 3.存储过程能够减少网络流量 对于同一个针对数据数据库对象的操作，如查询修改，如果这一操作所涉及到的Transaction-SQL 语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL 语句从而大大增加了网络流量降低网络负载。 4.存储过程可被作为一种安全机制来充分利用系统管理员通过，对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制。
★首先要感谢网友[来自Sql Server 技术空间群]简单爱的大力支持... 需要注意的问题NULL, ' ' 的区别 SELECT *FROM 表WHERE Fdate > ' 2007-6-7' 字符之间的比较,还是时间之间的比较.......... ------------表中的字段--------------- CREATE TABLE [dbo].[stuInfo] ( [FNumber] [int] IDENTITY(1,1) NOT NULL , [FName] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL , [FNameen] [varchar] (35) COLLATE Chinese_PRC_CI_AS , [FSex] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL , [FEducation] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL , [FCardID] [char] (18) COLLATE Chinese_PRC_CI_AS NOT NULL , [FBirDate] [datetime] NOT NULL , [FTech] [nvarchar](50) COLLATE Chinese_PRC_CI_AS , [FSubject] [nvarchar](50) COLLATE Chinese_PRC_CI_AS, [FJiF] [nvarchar](100)COLLATE Chinese_PRC_CI_AS, [FJobAdd][nvarchar](100)COLLATE Chinese_PRC_CI_AS, [FExamNum][varchar](30)COLLATE Chinese_PRC_CI_AS NOT NULL, [FServerNum] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL , [FExamDate] [datetime] NOT NULL , [FIsAE] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY] GO ----------存储过程------------------------- IF EXISTS(SELECT *FROM SYSOBJECTS WHERE NAME='PROC_Look') DROP PROC PROC_Look GO CREATE PROC PROC_Look @FName NVARCHAR(30)=null, @FNameen VARCHAR(35)=null, @FCardID CHAR(18)=null, @FExamNum NVARCHAR(30)=null, @FServerNum NVARCHAR(30)=null, @FSex CHAR(1)=null, @FEducation NVARCHAR(20)=null, @FIsAE CHAR(1)=null, @FTech NVARCHAR(50)=null, @FSubject NVARCHAR(50)=null, @FJiF NVARCHAR(100)=null, @FJobAdd NVARCHAR(100)=null, @FStartTime VARCHAR(20)=null, @FEndTime VARCHAR(20)=null AS
SQL server 存储过程语法大全 SQLSERVER存儲過程的寫法格式規格 ***************************************************** *** author：Susan *** date:2005/08/05 *** expliation:如何寫存儲過程的格式及例子，有游標的用法！ *** 本版:SQL SERVER 版！ ******************************************************/ 在存儲過程中的格式規格： CREATE PROCEDURE XXX 參數1， 參數2…………… As Declare 參數1， 參數2…………… Set參數1的初始值 Set參數2的初始值………… Begin trascation
Commit trascation Return 下面是一個例子 CREATE PROCEDURE TEST_2 @strTO VARCHAR(3) AS DECLARE @strUNIT_NAME VARCHAR(800), @strSQL VARCHAR(8000), @Link VARCHAR(1), @Link1 VARCHAR(1) SET @strUNIT_NAME='' SET @strSQL='' SET @Link='' SET @Link1='' BEGIN TRANSACTION IF @strTO<>'' BEGIN
UPDATE UNIT SET UNIT_NAME=REPLACE(UNIT_NAME,'*','') WHERE UNIT_CODE=@strTO END ELSE BEGIN UPDATE UNIT SET UNIT_NAME=UNIT_NAME+'*' WHERE UNIT_CODE='011' END IF @strTO<>'' BEGIN declare db cursor for --必需聲明在查詢的前面 SELECT UNIT_NAME FROM UNIT WHERE LEFT(UNIT_CODE,2)=LEFT(@strTO,2)---取到相關信息 END ELSE BEGIN declare db cursor for --必需聲明在查詢的前面 SELECT UNIT_NAME FROM UNIT WHERE LEFT(UNIT_CODE,2)=LEFT('011',2)---取到相關信息 END open db ---開起取到的信息
sqlServer建立存储过程（图文并茂教你） 在代码中使用SQL语句已经过时，现在越来越多人都喜欢使用存储过程，把SQL语句封装在SQL数据中，这样做的好处我就不说，你在网上可以查到一大堆。 现在就从我的经历说起吧。对于初学者来说，遇到一个问题，哪怕是一个很小的问题，也会让他纠结若干长时间。当然，我也算是一个初学者，但也有2年的经验。开始我写一个很大的程序都是通过直接在程序中插入SQL语句做的，但随着历练的越来越多，发现有很多问题存在，于是开始学习存储过程，在网上你去搜索，开始都是给你一大堆得代码，啊！我看了就要疯了，我要的不是那些，我要的是循序渐进的，我要最简单，因为只有从最简单的开始，你慢慢会领悟那一大堆的代码。于是我开始建立存储过程，但是当我建立好后不知道存储在哪里，也不知道怎么执行（验证存储过程是否正确），当我点保存的时候，它会弹出对话框。呵呵，我想这些事我遇到，也是你遇到的。 当然我写这篇文章不是对于那些高手或有经验的开发者，这里仅仅针对于开始学习存储过程的“童鞋”们。 写在最后，我希望有志同道合者一起工作，完成我们的兴趣爱好。 （由于一时兴起，所有文字和图片都是现做的，难免文字中有些错误，请见谅！） 进入正题：（数据库版本：sqlServer2005） 1、启动管理器（如图） 2.进入主界面，展开你的数据库（如图）
3、展开数据库后可以看到存储过程，右键单击，选择“新建存储过程”（如图）
4、此时右边会这样的代码（如下图）
5、我用图来解释下代码，并说明哪些你把删除（如图） 6、删除后的代码如下图： 7、写上自己的代码，我写的如下图：例如“学生信息表”
sql存储过程的加密和解密 ★加密存储过程： IF EXISTS (SELECT name FROM sysobjects WHERE name = 'encrypt_this' AND type = 'P') DROP PROCEDURE encrypt_this GO USE pubs GO CREATE PROCEDURE encrypt_this WITH ENCRYPTION ---只需要在创建时加入WITH ENCRYPTION即可 AS SELECT * FROM authors GO --查看存储过程文本： EXEC sp_helptext encrypt_this 下面是结果集： The object's comments have been encrypted. ----对象已经被加密 ★解密存储过程： CREATE PROCEDURE sp_decrypt(@objectName varchar(50)) AS begin set nocount on --CSDN：j9988 copyright:2004.04.15 --V3.1 --破解字节不受限制，适用于SQLSERVER2000存储过程，函数，视图，触发器 --修正上一版视图触发器不能正确解密错误 --发现有错，请E_MAIL：CSDNj9988@https://www.doczj.com/doc/3e5057507.html, begin tran declare @objectname1 varchar(100),@orgvarbin varbinary(8000)
declare @sql1 nvarchar(4000),@sql2 varchar(8000),@sql3 nvarchar(4000),@sql4 nvarchar(4000) DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000) declare @i int,@status int,@type varchar(10),@parentid int declare @colid int,@n int,@q int,@j int,@k int,@encrypted int,@number int select @type=xtype,@parentid=parent_obj from sysobjects where id=object_id(@ObjectName) create table #temp(number int,colid int,ctext varbinary(8000),encrypted int,status int) insert #temp SELECT number,colid,ctext,encrypted,status FROM syscomments WHERE id = object_id(@objectName) select @number=max(number) from #temp set @k=0 while @k<=@number begin if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k) begin if @type='P' set @sql1=(case when @number>1 then 'ALTER PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS ' else 'ALTER PROCEDURE '+ @objectName+' WITH ENCRYPTION AS ' end) if @type='TR' begin declare @parent_obj varchar(255),@tr_parent_xtype varchar(10) select @parent_obj=parent_obj from sysobjects where id=object_id(@objectName) select @tr_parent_xtype=xtype from sysobjects where id=@parent_obj if @tr_parent_xtype='V' begin set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTERD OF INSERT AS PRINT 1 ' end else begin set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 ' end end
如果你想更好的了解SQL Server 2005列出所有存储过程的实际操作的相关内容的话，如果你想更好的了解SQL Server 2005列出所有存储过程的实际操作的相关内容的话，下面的文章你不妨浏览，望你能会获得自己想要的东西。 对于数据库管理员来说，可以经常想了解一些之前未听说过的存储过程，特别是无文档类型的存储过程。或许是用这些存储过程，能够简化日常的数据管理。 对于数据库管理员来说，可以经常想了解一些之前未听说过的存储过程，特别是无文档类型的存储过程。或许是用这些存储过程，能够简化日常的数据管理。 为了查找这些存储过程，你可以花时间在互联网搜索，查看一些你还未知道的存储过程，也许在一两个小时您可能会发现你想要...也许你很幸运的找到，其他人在他们的文章中列出所有的存储过程，函数和视图，并介绍了如何使用这些存储过程。 但其实，您可以在一分钟之内就可以自己列出这些存储过程、函数和视图的清单!这份名单甚至包括SQL Server中所有无文档的存储过程。通过这个清单，你就可以确定你所想要找的存储过程。 SQL Server 2005实际上保存了所有存储过程的列表，包括有文档的、无文档的，甚至是用户自定义的!所有这些信息，都包含在系统表中。最简单的方法是使用一个系统视图，特别是sys.all_objects这个视图来查阅。 您也可以使用sys.procedures目录视图，但我的测试结果，发现这个视图会过滤掉一些储存过程。 您也可以使用系统储存过程sp_stored_procedures返回当前环境中的存储过程列表，但这个存储过程同样也限制了存储过程返回值。 通过对比，我觉得：如果想获得SQL Server 2005中所有的储存程序，建议使用sys.all_objects 这个系统视图，sys.Procedures或sp_Stored_Procedures这两个视图会因为某些未知原因，过滤掉一些内容，造成信息不全。 存储过程信息是存储在各自用户数据库中的系统表中的。SQL Server 2005保存了存储过程的唯一标识信息，如存储过程的名称、创建时间、修改时间、是否来自微软等等。 如何确保所有的用户数据库都能够自动创建这些存储过程呢? 当SQL Server部署完成后，微软提供的存储过程，是保存在master数据库中的。当您新建一个数据库时，master数据库将作为模板数据库，因此，master数据库中的所有存储过程将自动创建到你所新建的数据库中。 如果你想创建一个存储过程，并希望能够自动分发到所有的数据库中，你可以在master数据库中建立该存储过程，这样之后新创建的数据库中，将自动包含你新建的这个存储过程;但对于之前已经存在的数据库，你仍需要到每个数据库中手动创建这个存储过程。