存储过程、sp_executesql存储过程、try catch错误处理

最新推荐文章于 2022-05-29 10:48:51 发布

Q青锋

最新推荐文章于 2022-05-29 10:48:51 发布

阅读量2.1k

点赞数

分类专栏： SQL 文章标签： sqlserver

SQL 专栏收录该内容

50 篇文章 0 订阅

订阅专栏

原文出自：http://blog.csdn.net/sqlserverdiscovery/article/details/7851881

存储过程就是把一个或多个T-SQL语句组合到一个逻辑单元中，在SQL Server数据库中保存为一个对象。在创建完成后，定义会保存在sys.sql_modules系统目录视图中。

存储过程的有点：

1、存储过程是在数据层汇集的T-SQL代码，可以促进代码的重复使用，同时有助于调试、寻找bug。也就是说这些代码就存放在数据库里，而不是嵌入在应用程序中的SQL代码，再由这些代码连接起来，最后发送到服务器上来执行的。

2、存储过程相对于大的即席查询，能够减少网络流量。

3、存储过程则更安全，因为内嵌的即席查询容易进行SQL注入。

4、相对于函数、视图，存储过程能使用流控制、临时表、表变量等。

5、可以淡化数据获取的方法。一旦底层的表有变化，不用去修改嵌入到应用程序中的sql代码，只要修改存储过程就可以了。

6、存储过程的查询响应时间比较稳定。存储过程提供了更加可靠的查询计划缓存，因此可以重用，而即席查询完全依赖于执行计划被缓存的环境（语句中的参数）。

另外，需要注意的：'CREATE/ALTER PROCEDURE'不允许将数据库名称指定为对象名前缀

[sql]view plaincopy 
   
 --1.1创建无参数存储过程  
 --这个存储过程的定义很像视图定义  
 create procedure dbo.usp_wct  
 as  
   
 select wcId,  
        wcV,  
        wcDate  
 from wcT  
 go  
   
 --1.2执行存储过程  
 exec dbo.usp_wct  
   
   
   
 --2.1创建带参数的存储过程  
 create procedure dbo.usp_param_wct  
 (@wcId bigint =1,        --默认值为1  
  @wcDate datetime =null) --默认值为null  
 as  
   
 select wcV  
 from dbo.wcT  
 where wcId = @wcId  
       and isnull(@wcDate,1)=case when @wcdate is null  
                                       then 1   
                                  else wcDate  
                             end  
   
 --返回状态值,一般不用于返回计算结果,而是用来返回表明存储过程执行的状态        
 return 1   
   
 go  
   
   
 --2.2.1调用存储过程  
 exec dbo.usp_param_wct '1','2012-07-01'  
   
 --2.2.2调用存储过程,带参数名  
 exec dbo.usp_param_wct @wcid='1',  
                        @wcdate='2012-07-01'  
   
 --2.2.3调用存储过程,带参数名,只输入一个参数值,另一个会使用默认值  
 exec dbo.usp_param_wct @wcid='1'  
   
 --2.2.4调用存储过程,带参数名,不输入参数,2个参数都会使用默认值  
 exec dbo.usp_param_wct   
   
   
   
 --3.1创建带output参数的存储过程  
 create procedure dbo.usp_output_param_wct  
 (@wcId bigint =1,            --默认值为1  
  @wcDate datetime =null,     --默认值为null  
  @wcV varchar(50) output)    --输出参数,用来返回计算结果  
 as  
   
 select @wcV = wcV  
 from dbo.wcT  
 where wcId = @wcId  
       and isnull(@wcDate,1)=case when @wcdate is null  
                                       then 1   
                                  else wcDate  
                             end  
   
 --返回状态值,一般不用于返回计算结果,而是用来返回表明存储过程执行的状态        
 return 1   
   
 go  
   
   
 --3.2调用存储过程  
 declare @output varchar(50)  
   
 exec dbo.usp_output_param_wct @wcId =1,         --输入参数  
                               --@wcDate = null,  
                               @wcv = @output output       --输出参数，存放返回值  
   
 print @output    
   
   
   
 --4.1修改存储过程  
 create procedure dbo.usp_output_param_wct  
 (@wcId bigint =1,            --默认值为1  
  @wcDate datetime =null,     --默认值为null  
  @wcV varchar(50) output)    --输出参数,用来返回计算结果  
 with encryption --加密存储过程  
 as  
   
 select @wcV = wcV  
 from dbo.wcT  
 where wcId = @wcId  
       and isnull(@wcDate,1)=case when @wcdate is null  
                                       then 1   
                                  else wcDate  
                             end  
   
 --返回状态值,一般不用于返回计算结果,而是用来返回表明存储过程执行的状态        
 return 1   
   
 go        
   
 --4.2查看定义  
 select object_name(object_id),  
        definition                --显示为NULL  
 from sys.sql_modules  
 where object_id = object_id('dbo.usp_output_param_wct')   
   
   
   
 --5.在SQL Server启动时自动执行存储过程  
 create procedure dbo.create_table    
 as  
    select * into wc.dbo.wcX  
    from wc.dbo.wcT  
 go  
   
 --5.1设置为true,在SQL Server启动时自动执行存储过程  
 exec sp_procoption @procname   = 'dbo.create_table',  
                    @optionname = 'startup',  
                    @optionvalue= 'true'  
                      
 --5.2设置为false来禁止  
 --只有dbo拥有的master数据库中的对象才能更改启动设置。  
 use master  
 go  
   
 exec sp_procoption @procname   = 'dbo.create_table',  
                    @optionname = 'startup',  
                    @optionvalue= 'false'  
   
   
   
 --6.1每次存储过程执行时都重新编译  
 --需要注意的是:当表、索引数据大量更新时，会进行语句级别的重新编译  
 create procedure dbo.usp_backupset_info  
 (@startDate datetime,  
  @finishDate datetime)  
 with recompile     --不保存执行计划，每次都是重新编译  
 as  
   
 select SUM(backup_size)/1024/1024/1024 as 'GB'  
 from msdb.dbo.backupset  
 where backup_start_date >= @startDate  
       and backup_finish_date <= @finishDate  
       and type = 'D'  --数据库备份，不是日志备份或差异备份  
 go  
   
   
 --6.2清空过程缓存  
 select *  
 from sys.dm_exec_cached_plans  
   
 dbcc freeproccache      --清空过程缓存  
   
 --再次查询发现都已经清空  
 select *  
 from sys.dm_exec_cached_plans  
   
   
   
 --7.刷新存储过程，查看元数据  
 exec sp_refreshsqlmodule @name ='dbo.usp_output_param_wct'  
   
 select p.name,           --存储过程名称  
        p.object_id,  
          
        pp.parameter_id,     
        pp.name,          --参数名称  
        pp.default_value, --好像不起作用  
        t.name            --参数列的类型  
 from sys.procedures p  
 inner join sys.parameters pp  
         on pp.object_id = p.object_id  
 inner join sys.types t  
         on t.system_type_id = pp.system_type_id  
 where p.object_id =965578478  
   
   
 --8.删除存储过程  
 DROP PROCEDURE dbo.usp_output_param_wct  
               

存储过程的Execute as选项

[sql]view plaincopy 
   
 --1.创建表  
 create table dbo.wcE  
     (vid int not null primary key clustered,  
      v int not null  
     )  
   
 insert into dbo.wcE  
 values(1,100),  
       (2,200),  
       (3,300)  
   
   
 --2.创建存储过程,存储过程的架构与存储过程中引用对象的架构一样  
 create procedure dbo.usp_delete_wcE  
 as  
   
 delete from dbo.wcE  
 go  
   
   
 --3.1创建登录名  
 create login wclogin with password = 'wclogin123'  
   
   
 --3.2创建数据库用户  
 create user wclogin  
   
   
   
 --4.授予用户执行存储过程的权限  
 grant exec on dbo.usp_delete_wcE to wclogin   
   
 grant connect  to wclogin  
   
   
   
 /*====================================================  
 5.1打开另一个客户端,以wclogin登录名登进去,然后执行存储过程，  
 虽然这个用户没有访问存储过程中所引用表的权限,  
 但是由于存储过程的架构与存储过程中引用对象的架构一样,  
 同时由于存储过程中只能是insert，update，delete，select，  
 而不能是trancate等操作，这就是所有权链,所以没有报错.  
   
 特别需要注意的是:以上的所有权链对于动态的SQL是无效的.  
 ======================================================*/  
 exec dbo.usp_delete_wcE  
   
 --5.2这里改为truncate后，会报错  
 alter procedure dbo.usp_delete_wcE  
 as  
   
 truncate table dbo.wcE  
 go  
   
   
   
 --6.1使用execute as owner指定存储过程的任何调用,  
 --都是在存储过程架构的拥有者的安全上下文运行的  
 --这时再次以wclogin登录就可以执行存储过程而不会报错  
 alter procedure dbo.usp_delete_wcE  
 with execute as owner  
 as  
   
 truncate table dbo.wcE  
 go  
   
   
 --6.2以执行用户的权限来执行存储过程  
 --在以wclogin登录执行时会报错  
 alter procedure dbo.usp_delete_wcE  
 with execute as caller  
 as  
   
 truncate table dbo.wcE  
 go  
   
 --6.3以创建或最近一次更新存储过程的用户的权限来执行存储过程,  
 --在以wclogin登录执行时会报错  
 alter procedure dbo.usp_delete_wcE  
 with execute as self  
 as  
   
 truncate table dbo.wcE  
 go  
   
   
 --6.4以指定的用户的权限来执行存储过程,  
 --在以wclogin登录执行时显然不会报错  
 alter procedure dbo.usp_delete_wcE  
 with execute as 'dbo'  
 as  
   
 truncate table dbo.wcE  
 go  
   
   
   
 --6.5这个存储过程用了动态的SQL语句  
 create procedure dbo.w  
 as  
   
 exec('select * from dbo.wcE');  
 go  
   
 grant exec on dbo.w to wclogin  
   
   
 --在客户端以wclogin登录,执行时会报错  
 --拒绝了对对象'wcE'(数据库'wc'，架构'dbo')的SELECT权限。  
 exec dbo.w  

[sql]view plaincopy 
   
 --1.函数  
 if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')  
    drop function dbo.f_splitSTR  
 go  
   
 create function dbo.f_splitSTR  
 (  
     @s varchar(8000),     --要分拆的字符串  
     @split varchar(10)    --分隔字符  
 )   
 returns @re table(                      --要返回的临时表  
                      col varchar(1000)  --临时表中的列   
                  )  
 as  
 begin     
   declare @len int  
     
   set @len = LEN(@split)      --分隔符不一定就是一个字符，可能是2个字符  
     
   while CHARINDEX(@split,@s) >0  
   begin  
     insert into @re   
     values(left(@s,charindex(@split,@s) - 1))  
       
     set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'')    --覆盖：字符串以及分隔符  
   end  
     
   insert into @re values(@s)  
     
   return   --返回临时表  
 end  
 go    
   
   
 select * from dbo.f_splitSTR('123,456',',')  
   
   
   
 --2.存储过程  
   
 --创建类型  
 create type var_orgid as table  
 (  
 org_id numeric(10,0)  
 )  
   
 --创建存储过程  
 create proc usp_orgid  
 @orgid_table var_orgid readonly  
 as  
   
 select * from @orgid_table  
 go  
   
   
 --定义表变量  
 declare @orgid_table as var_orgid  
   
 insert into @orgid_table   
 values(123)  
   
 exec usp_orgid @orgid_table  

sp_executesql存储过程，可以使动态语句重用，提高效率：

[sql]view plaincopy 
   
 DECLARE @sumcount INT  
 DECLARE @tmpSQL NVARCHAR(1000)  
 DECLARE @TABLENAME  VARCHAR(50)  
 SELECT @TABLENAME = ' sys.objects ' --表名变量赋值  
   
 SELECT @tmpSQL = 'select @sum = convert(varchar(50),count(object_id)) from ' + @TABLENAME   
   
   
 /*  
 参数1: 动态语句  
 参数2：定义的参数，这个参数就是在动态语句中引用到的变量@sum  
 参数3：指明了执行结果@sum的值，放到@sumcount参数里面，  
   
 这里需要特别注意的是，动态语句中的变量，和外面定义的变量，是不同的，  
 下面的语句，实际相当于：  
   
 declare @sum int   
   
 select @sum = convert(varchar(50),count(object_id))   
 from  + 表名   
   
 所以，通过sp_executesql，可以把语句产生的结果输出到外面的变量，  
 这样我们才能知道输结果  
 */  
 exec sp_executesql @tmpsql,  
                    N'@sum int output',  
                    @sumcount out  
   
 select @sumcount  

try catch 错误处理

[sql]view plaincopy 
   
 create proc proc_t(@i int)  
 as  
   
 select 5 * 1.0 / @i  
 go  
   
   
 --1.不会报错  
 begin try  
     begin tran  
         exec proc_t 1  
         select '执行成功'  
     commit tran  
 end try  
 begin catch  
   select '执行失败'  
   if @@trancount >0   
      rollback  
 end catch  
   
   
 --2.这次会报错,因为分母为0  
 begin try  
     begin tran  
         exec proc_t 0  
         select '执行成功'  
     commit tran  
 end try  
 begin catch  
   select '执行失败'  
   if @@trancount >0   
      rollback  
 end catch  