当运行一个执行字符串的模块时

  • 栏目:数据 时间:2020-04-18 23:23
<返回列表

执行 Transact-SQL 批中的命令字符串、字符串或执行下列模块之一:系统存储过程、用户定义存储过程、标量值用户定义函数或扩展存储过程。

Execute a stored procedure or function[ { EXEC | EXECUTE } ] { [ @return_status = ] { module_name [ ;number ] | @module_name_var } [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] } ] [ ,...n ] [ WITH RECOMPILE ] }[;]

Execute a character string{ EXEC | EXECUTE } ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] ) [ AS { LOGIN | USER } = ' name ' ][;]

Execute a pass-through command against a linked server{ EXEC | EXECUTE } ( { @string_variable | [ N ] 'command_string [ ? ]' } [ + ...n ] [ { , { value | @variable [ OUTPUT ] } } [ ...n ] ] ) [ AS { LOGIN | USER } = ' name ' ] [ AT linked_server_name ][;]

调用一个过程

3 -- Replace the default error message and numbers with my own:45 CREATE PROCEDURE spRunSQL6 @Statement VarChar(2000) -- Input param. accepts any SQL statement.7 AS8 DECLARE @StartTime DateTime9 , @EndTime DateTime10 , @ExecutionTime Int11 , @ErrNum Int12 SET @StartTime = GetDate()13 EXECUTE (@Statement)14 SET @ErrNum = @@Error15 IF @ErrNum = 207 -- Bad column16 RAISERROR 50001 'Bad column name'17 ELSE IF @ErrNum = 208 -- Bad object18 RAISERROR 50002 'Bad object name'19 ELSE IF @ErrNum = 0 -- No error. Resume.20 BEGIN21 SET @EndTime = GetDate()22 SET @ExecutionTime = DateDiff(MilliSecond, @StartTime, @EndTime)23 RETURN @ExecutionTime -- Return execution time in milliseconds24 END25 GO12 EXEC spRunSQL 'select 1 GO'3 GOGO----------- 112 EXEC spRunSQL 'selet 1 GO'3 GOMsg 102, Level 15, State 1, Server JAVA2SSQLEXPRESS, Line 1Incorrect syntax near 'GO'.123 drop procedure spRunSQL4 GO12

SQL Server 扩展了 EXECUTE 语句,以使其可用于向链接服务器发送传递命令。此外,还可以显式设置执行字符串或命令的上下文。看一个实例使用excute

create table employee(2 ID int,3 name nvarchar (10),4 salary int,5 start_date datetime,6 city nvarchar (10),7 region char (1))8 GO12 insert into employee (ID, name, salary, start_date, city, region3 values (1, 'Jason', 40420, '02/01/94', 'New York', 'W')4 GO

(1 rows affected)1 insert into employee (ID, name, salary, start_date, city, region2 values (2, 'Robert',14420, '01/02/95', 'Vancouver','N')3 GO

(1 rows affected)1 insert into employee (ID, name, salary, start_date, city, region2 values (3, 'Celia', 24020, '12/03/96', 'Toronto', 'W')3 GO

(1 rows affected)1 insert into employee (ID, name, salary, start_date, city, region2 values (4, 'Linda', 40620, '11/04/97', 'New York', 'N')3 GO

(1 rows affected)1 insert into employee (ID, name, salary, start_date, city, region2 values (5, 'David', 80026, '10/05/98', 'Vancouver','W')3 GO

(1 rows affected)1 insert into employee (ID, name, salary, start_date, city, region2 values (6, 'James', 70060, '09/06/99', 'Toronto', 'N')3 GO

(1 rows affected)1 insert into employee (ID, name, salary, start_date, city, region2 values (7, 'Alison',90620, '08/07/00', 'New York', 'W')3 GO

(1 rows affected)1 insert into employee (ID, name, salary, start_date, city, region2 values (8, 'Chris', 26020, '07/08/01', 'Vancouver','N')3 GO

(1 rows affected)1 insert into employee (ID, name, salary, start_date, city, region2 values (9, 'Mary', 60020, '06/09/02', 'Toronto', 'W')3 GO

(1 rows affected)12 select * from employee3 GOID name salary start_date city region----------- ---------- ----------- ----------------------- ---------- ------ 1 Jason 40420 1994-02-01 00:00:00.000 New York W 2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N 3 Celia 24020 1996-12-03 00:00:00.000 Toronto W 4 Linda 40620 1997-11-04 00:00:00.000 New York N 5 David 80026 1998-10-05 00:00:00.000 Vancouver W 6 James 70060 1999-09-06 00:00:00.000 Toronto N 7 Alison 90620 2000-08-07 00:00:00.000 New York W 8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N 9 Mary 60020 2002-06-09 00:00:00.000 Toronto W

(9 rows affected)12 -- example to execute the store procedure - valid34 IF EXISTS (SELECT name5 FROM sysobjects6 WHERE name = N'sp_Output_Salary'7 AND type = 'P')8 DROP PROCEDURE sp_Output_Salary9 GO12 CREATE PROCEDURE sp_Output_Salary3 @ID int,4 @OutSalary money OUTPUT5 AS6 SELECT @OutSalary = salary7 FROM employee8 WHERE Id = @ID910 IF @@ROWCOUNT = 111 RETURN12 SET @OutSalary = 013 RETURN 114 GO123 GRANT EXECUTE ON sp_Output_Salary TO PUBLIC4 GO123 DECLARE @myMoney money4 DECLARE @Ret_Status int5 EXECUTE @Ret_Status = sp_Output_Salary 1, @myMoney OUTPUT6 SELECT @myMoney7 SELECT @Ret_Status8 GO

--------------------- 40420.0000

(1 rows affected)

----------- 0

(1 rows affected)12 drop table employee3 GO1

注:

运行 EXECUTE 语句无需权限。但是,需要对 EXECUTE 字符串内引用的安全对象具有权限。例如,如果字符串包含 INSERT 语句,则 EXECUTE 语句的调用方对目标表必须具有 INSERT 权限。在遇到 EXECUTE 语句时,即使 EXECUTE 语句包含于模块内,也将检查权限。

模块的 EXECUTE 权限默认授予该模块的所有者,该所有者可以将此权限转让给其他用户。当运行一个执行字符串的模块时,系统会在执行该模块的用户上下文中而不是在创建该模块的用户上下文中检查权限。但是,如果同一用户拥有调用模块和被调用模块,则不对后者执行 EXECUTE 权限检查

如果模块访问其他数据库教程对象,则当拥有对该模块的 EXECUTE 权限并且以下任一情况存在时,执行将成功:

模块被标记为 EXECUTE AS USER 或 SELF,并且模块所有者对被引用对象具有相应权限。

模块被标记为 EXECUTE AS CALLER,并且您对对象具有相应权限。

模块被标记为 EXECUTE AS user_name,并且 user_name 对对象具有相应权限。

上下文切换权限若要对某登录名指定 EXECUTE AS,调用方必须具有对所指定登录名的 IMPERSONATE 权限。若要对某数据库用户指定 EXECUTE AS,调用方必须具有对所指定用户名的 IMPERSONATE 权限。如果未指定执行上下文或指定了 EXECUTE AS CALLER,则无需 IMPERSONATE 权限。

上一篇:我们首先创建一数据库,Test --创建数据库 data 下一篇:定义sn+sex为唯一键,等操作有需要了解的朋友可以看看

更多阅读

定义sn+sex为唯一键,等操作有需要了解的

数据 2020-04-18
文章收藏了大量的关于sql常用语句了,大多都是基于数据查询,修改,删除,等操作有需要了...
查看全文

当运行一个执行字符串的模块时

数据 2020-04-18
执行 Transact-SQL批中的命令字符串、字符串或执行下列模块之一:系统存储过程、用户定义存储...
查看全文

我们首先创建一数据库,Test --创建数据库

数据 2020-04-17
几种SQLServer分页的存储过程写法以及性能比较存储过程的5种分页写法,下面的代码是从忘了什么...
查看全文

友情链接: 网站地图

Copyright © 2015-2019 http://www.koi-bumi.com. 韦德体育有限公司 版权所有