How to use SQL Execute and tutorial on instances

Executes a command string, string, or one of the following modules in a Transact-SQL batch: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure.

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]
[;]

Call a procedure

3>--Replace The default error message and numbers with my own:
4>
5> CREATE PROCEDURE Sprunsql
6> @Statement VarChar (c)-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 =--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 ' Select 1 Go '
3> Go
Go
-----------
1
1>
2> EXEC sprunsql ' selet 1 Go '
3> Go
MSG 102, level, State 1, Server java2ssqlexpress, line 1
Incorrect syntax near ' go '.
1>
2>
3> drop procedure Sprunsql
4> Go
1>
2>

SQL Server extends the EXECUTE statement so that it can be used to send delivery commands to the linked server. In addition, you can explicitly set the context in which the string or command is executed. Look at an instance using Excute

> CREATE TABLE Employee (
2>     id           int,
3>     name        nvarchar (a),
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 (IDs, 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 (IDs, 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 (IDs, 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 (IDs, 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 (IDs, 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 (IDs, 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 (IDs, 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 (IDs, 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>

Note:

Running the EXECUTE statement requires no permissions. However, you need to have permissions on the security objects that are referenced within the EXECUTE string. For example, if the string contains an INSERT statement, the caller of the EXECUTE statement must have INSERT permission on the target table. When an EXECUTE statement is encountered, permissions are checked even if the EXECUTE statement is contained within the module.

The module's EXECUTE permission is granted to the owner of the module by default, and the owner can transfer this permission to another user. When you run a module that executes a string, the system checks the permissions in the context of the user executing the module, not in the context of the user who created the module. However, if the same user owns the calling module and the called module, execute permission checks are not performed on the latter

If a module accesses another database tutorial object, execution succeeds if you have execute permissions on the module and any of the following conditions exist:

The module is marked as EXECUTE as USER or SELF, and the module owner has appropriate permissions for the referenced object.

The module is marked as EXECUTE as CALLER, and you have appropriate permissions on the object.

The module is marked as EXECUTE as user_name, and user_name has appropriate permissions on the object.

Context Switching permissions
to specify EXECUTE as for a login, the caller must have impersonate permissions on the specified login. To specify EXECUTE as for a database user, the caller must have impersonate permissions on the specified user name. If the execution context is not specified or the EXECUTE as CALLER is specified, no impersonate permission is required.