Sqlexecute usage and example tutorial

Execute a command string or string in a Transact-SQL batch or execute one of the following modules: system stored procedures, user-defined stored procedures, scalar value user-defined functions, or extended stored procedures. Executeastoredprocedureorfunction [{EXEC | EXECUTE}] & nbsp; & nbsp ;{& nbsp; & nbsp; [Execute Command strings, strings, or execute one of the following modules in a Transact-SQL batch: system stored procedures, user-defined stored procedures, scalar value user-defined functions, or extended stored procedures.

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

Call a process

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>
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 extends the EXECUTE statement to send commands to the linked Server. In addition, you can explicitly set the context for executing strings or commands. View an instance and use 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, '2014/1/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, '2017/95', 'vancouver', 'n ')
3> GO

(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region
2> values (3, 'celia ', 24020, '2017/96', 'toronto ', 'w ')
3> GO

(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region
2> values (4, 'linda ', 40620, '2014/1/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, '2014/1/98', 'vancouver ', 'w ')
3> GO

(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region
2> values (6, 'James ', 70060, '2017/99', 'toronto', 'n ')
3> GO

(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region
2> values (7, 'alison ', 90620, '2014/1/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, '2014/1/01', 'vancouver ', 'n ')
3> GO

(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region
2> values (9, 'Mary ', 60020, '2017/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 00:00:00. 000 New York W
2 Robert 14420 00:00:00. 000 Vancouver N
3 Celia 24020 00:00:00. 000 Toronto W
4 Linda 40620 00:00:00. 000 New York N
5 David 80026 00:00:00. 000 Vancouver W
6 James 70060 00:00:00. 000 Toronto N
7 Alison 90620 00:00:00. 000 New York W
8 Chris 26020 00:00:00. 000 Vancouver N
9 Mary 60020 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>
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:

No permission is required to run the EXECUTE statement. However, you must have the permission on the security object referenced in the EXECUTE string. For example, if a string contains an INSERT statement, the caller of the EXECUTE statement must have the INSERT permission on the target table. When an EXECUTE statement is encountered, the permission is checked even if the EXECUTE statement is included in the module.

The EXECUTE permission of the module is granted to the owner of the module by default. The owner can transfer the permission to other users. When running a module that executes a string, the system checks the permissions in the user context that executes the module, rather than in the user context that creates the module. However, if the same user has a call module and a called module, EXECUTE permission check is not performed for the latter.

If the module accesses other objects, the execution is successful if it has the EXECUTE permission 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 the relevant permissions on the referenced object.

The module is marked as execute as caller, and you have the corresponding permissions on the object.

The module is marked as execute as user_name, and user_name has the corresponding permissions on the object.

Context switch permission
To specify execute as for a login name, the caller must have the IMPERSONATE permission for the specified Login Name. To specify execute as for a database user, the caller must have the IMPERSONATE permission for the specified user name. If the execution context is not specified or the execute as caller is specified, the IMPERSONATE permission is not required.