SQL Server/T-SQL Tutorial/Procedure Function/Alter procedure
Содержание
ALTER PROC Statement Using WITH RECOMPILE
12>
13>
14> CREATE TABLE employee(
15> id INTEGER NOT NULL PRIMARY KEY,
16> first_name VARCHAR(10),
17> last_name VARCHAR(10),
18> salary DECIMAL(10,2),
19> start_Date DATETIME,
20> region VARCHAR(10),
21> city VARCHAR(20),
22> managerid INTEGER
23> );
24> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> Create PROC usp_GetEmployee(
5> @Start_Date AS datetime
6> )
7> WITH RECOMPILE
8> AS
9> SELECT
10> *
11> FROM
12> Employee
13> WHERE
14> Start_Date >= @Start_Date
15> GO
1>
2>
3> ALTER PROC usp_GetEmployee(
4> @Start_Date AS datetime
5> )
6> WITH RECOMPILE
7> AS
8> SELECT
9> *
10> FROM
11> Employee
12> WHERE
13> Start_Date >= @Start_Date
14> GO
1>
2> drop procedure usp_GetEmployee
3> GO
1>
2>
3> drop table employee;
4> GO
Execute as
5>
6> CREATE TABLE employee(
7> id INTEGER NOT NULL PRIMARY KEY,
8> first_name VARCHAR(10),
9> last_name VARCHAR(10),
10> salary DECIMAL(10,2),
11> start_Date DATETIME,
12> region VARCHAR(10),
13> city VARCHAR(20),
14> managerid INTEGER
15> );
16> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
1>
2>
3>
4> CREATE PROCEDURE dbo.usp_employee
5> @SchemaAndTable nvarchar(255)
6> AS
7> EXEC ("SELECT COUNT(*) FROM " + @SchemaAndTable)
8> GO
1>
2> EXEC dbo.usp_employee "Employee"
3> GO
-----------
9
1>
2> ALTER PROCEDURE dbo.usp_employee
3> @SchemaAndTable nvarchar(255)
4> WITH EXECUTE AS "YourName"
5> AS
6> EXEC ("SELECT COUNT(*) FROM " + @SchemaAndTable)
7> GO
1>
2> drop procedure dbo.usp_employee
3> GO
1>
2> drop table employee;
3> GO
1>
Modifying a Stored Procedure
5> CREATE TABLE employee(
6> id INTEGER NOT NULL PRIMARY KEY,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20),
13> managerid INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2> CREATE PROCEDURE dbo.usp_employee
3> @Name nvarchar(50),
4> @DeptCount int OUTPUT
5> AS
6> SELECT first_Name
7> FROM employee
8> WHERE city = @Name
9> SELECT @DeptCount = @@ROWCOUNT
10> GO
1>
2> DECLARE @DeptCount int
3> EXEC dbo.usp_employee "Vancouver",
4> @DeptCount OUTPUT
5> PRINT @DeptCount
6> GO
first_Name
----------
Jason
(1 rows affected)
1
1>
2> ALTER PROCEDURE dbo.usp_employee
3> @GroupName nvarchar(50)
4> AS
5> SELECT first_name
6> FROM Employee
7> WHERE city = @GroupName
8> SELECT @@ROWCOUNT DepartmentCount
9> GO
1>
2> EXEC dbo.usp_Employee "Research and Development"
3>
4>
5> drop procedure dbo.usp_Employee
6> GO
first_name
----------
(0 rows affected)
DepartmentCount
---------------
0
(1 rows affected)
1> drop table employee;
2> GO
1>
The syntax of the ALTER PROC statement
ALTER {PROC|PROCEDURE} procedure_name
[parameter declarations]
[WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
AS sql_statements
A statement that creates a procedure
13>
14>
15>
16> create table Bankers(
17> BankerID Integer,
18> BankerName VARCHAR(20),
19> BankerContactLName VARCHAR(20),
20> BankerContactFName VARCHAR(20),
21> BankerCity VARCHAR(20),
22> BankerState VARCHAR(20),
23> BankerZipCode VARCHAR(20),
24> BankerPhone VARCHAR(20)
25> )
26> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3> CREATE PROC spBankerState
4> @StateVar varchar(20)
5> AS
6> SELECT BankerName
7> FROM Bankers
8> WHERE BankerState = @StateVar
9> GO
1>
2> --A statement that changes the parameter defined by the procedure
3>
4> ALTER PROC spBankerState
5> @StateVar varchar(20) = NULL
6> AS
7> IF @StateVar IS NULL
8> SELECT BankerName
9> FROM Bankers
10> ELSE
11> SELECT BankerName
12> FROM Bankers
13> WHERE BankerState = @StateVar
14> GO
1>
2> drop PROC spBankerState;
3> GO
1>
2> drop table Bankers;
3> GO
1>
2>
3>