sql execute的使用方法与实例教程

执行 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:
4>
5> CREATE PROCEDURE spRunSQL
6>     @Statement VarChar(2000) -- Input param. accepts any SQL statement.
7> AS
8>     DECLARE   @StartTime DateTime
9>             , @EndTime DateTime
10>             , @ExecutionTime Int
11>             , @ErrNum Int
12>     SET @StartTime = GetDate()
13>     EXECUTE (@Statement)
14>     SET @ErrNum = @@Error
15>     IF @ErrNum = 207      -- Bad column
16>        RAISERROR 50001 'Bad column name'
17>     ELSE IF @ErrNum = 208     -- Bad object
18>        RAISERROR 50002 'Bad object name'
19>     ELSE IF @ErrNum = 0   -- No error. Resume.
20>        BEGIN
21>         SET @EndTime = GetDate()
22>         SET @ExecutionTime = DateDiff(MilliSecond, @StartTime, @EndTime)
23>         RETURN @ExecutionTime -- Return execution time in milliseconds
24>       END
25> GO
1>
2> EXEC spRunSQL ' 1 GO'
3> GO
GO
-----------
          1
1>
2> EXEC spRunSQL 'selet 1 GO'
3> GO
Msg 102, Level 15, State 1, Server JAVA2SSQLEXPRESS, Line 1
Incorrect syntax near 'GO'.
1>
2>
3> drop procedure spRunSQL
4> GO
1>
2>

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> GO
1>
2> insert into employee (ID, name,    salary, start_date, city,       region
3>               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,       region
2>               values (2,  'Robert',14420,  '01/02/95', 'Vancouver','N')
3> GO

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

(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region
2>               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,       region
2>               values (5,  'David', 80026,  '10/05/98', 'Vancouver','W')
3> GO

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

(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region
2>               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,       region
2>               values (8,  'Chris', 26020,  '07/08/01', 'Vancouver','N')
3> GO

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

(1 rows affected)
1>
2> select * from employee
3> GO
ID          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)
1>
2> -- example to execute the store procedure - valid
3>
4> IF EXISTS (SELECT name
5>       FROM   sysobjects
6>       WHERE  name = N'sp_Output_Salary'
7>       AND      type = 'P')
8>     DROP PROCEDURE sp_Output_Salary
9> GO
1>
2> CREATE PROCEDURE sp_Output_Salary
3>    @ID int,
4>    @OutSalary money OUTPUT
5> AS
6>    SELECT @OutSalary = salary
7>       FROM employee
8>       WHERE Id = @ID
9>
10>    IF @@ROWCOUNT = 1
11>       RETURN
12>    SET @OutSalary = 0
13>    RETURN 1
14> GO
1>
2>
3> GRANT EXECUTE ON sp_Output_Salary TO PUBLIC
4> GO
1>
2>
3> DECLARE @myMoney money
4> DECLARE @Ret_Status int
5> EXECUTE @Ret_Status = sp_Output_Salary 1, @myMoney OUTPUT
6> SELECT @myMoney
7> SELECT @Ret_Status
8> GO

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

(1 rows affected)

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

(1 rows affected)
1>
2> drop table employee
3> GO
1>

注：

运行 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 权限。