Procedures:
A stored procedure is a precompiled group of Transact-SQL
statements, and is saved to the database (under the "Stored
Procedures" node).
Advantages of Procedures:
Reduced server/client network traffic
The commands in a procedure are executed as a single batch of
code. This can significantly reduce network traffic between the server and
client because only the call to execute the procedure is sent across the
network.
Stronger security
Multiple
users and client programs can perform operations on underlying database objects
through a procedure, even if the users and programs do not have direct
permissions on those underlying objects. The procedure controls what processes
and activities are performed and protects the underlying database objects. This
eliminates the requirement to grant permissions at the individual object level
and simplifies the security layers.
Reuse of code
The
code for any repetitious database operation is the perfect candidate for
encapsulation in procedures. This eliminates needless rewrites of the same
code, decreases code inconsistency, and allows the code to be accessed and
executed by any user or application possessing the necessary permissions.
Easier maintenance
When
client applications call procedures and keep database operations in the data
tier, only the procedures must be updated for any changes in the underlying
database
Improved performance
By
default, a procedure compiles the first time it is executed and creates an
execution plan that is reused for subsequent executions. Since the query
processor does not have to create a new plan, it typically takes less time to
process the procedure.
Types of Stored Procedures:
User-defined Procedures
A
user-defined procedure can be created in a user-defined database or in all
system databases except the Resource database. The procedure
can be developed in either Transact-SQL or as a reference to a
Microsoft .NET Framework common runtime language (CLR) method.
Temporary Stored procedure
Temporary
procedures are a form of user-defined procedures. The temporary procedures are
like a permanent procedure, except temporary procedures are stored in tempdb.
There
are two types of temporary procedures: local and global. They differ from each
other in their names, their visibility, and their availability.
Local temporary procedures have a single number sign
(#) as the first character of their names; they are visible only to the current
user connection, and they are deleted when the connection is closed.
Global temporary procedures have two number signs
(##) as the first two characters of their names; they are visible to any user
after they are created, and they are deleted at the end of the last session
using the procedure.
System Procedures
System
procedures are included with SQL Server. They are physically stored in the
internal, hidden Resource database and logically appear in
the sys schema of every system- and user-defined database. In
addition, the msdb database also contains system stored
procedures in the dbo schema that are used for scheduling
alerts and jobs. Because system procedures start with the prefix sp_,
we recommend that you do not use this prefix when naming user-defined
procedures
Extended User-Defined
Extended
procedures enable creating external routines in a programming language such as
C. These procedures are DLLs that an instance of SQL Server can
dynamically load and run.
Creating a Stored Procedure:
- In Object Explorer, connect to an instance of Database Engine and then expand that instance.
- Expand Databases, expand the database which you want to create a procedure, and then expand Programmability.
- Right-click Stored Procedures and then click New Stored Procedure.
- On the Query menu, click Specify Values for Template Parameters.
- In the Specify Values for Template Parameters dialog box, enter the following values for the parameters shown.
- Click OK.
- In the Query Editor, replace the SELECT statement with the following statement:
SELECT FirstName, LastName, Department FROM Test
- To create the procedure, from the Query menu, click Execute. The procedure is created as an object in the database.
- To see the procedure listed in Object Explorer, right-click Stored Procedures and select Refresh.
- To run the procedure, in Object Explorer, right-click the stored procedure name and select
Execute
Stored Procedure.
Using Transact-SQL
To create a procedure in Query Editor
- In Object Explorer, connect to an instance of Database Engine.
- From the File menu, click New Query.
- Copy and paste the following example into the query window and click Execute. This example creates the same stored procedure as above using a different procedure name.
USE TestData;
GO
CREATE PROCEDURE UDP_GetTestData
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, Department FROM Employee WHERE FirstName = @FirstName AND LastName = @LastName AND EndDate IS NULL;
GO
To run the
procedure, copy and paste the following example into a new query window and
click Execute. Notice that
different methods of specifying the parameter values are shown.
EXECUTE UDP_GetTestData N'Ackerman', N'Pilar';
-- Or
EXEC UDP_GetTestData @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE UDP_GetTestData @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
To modify a procedure in Management Studio
- In Object Explorer, connect to an instance of Database Engine and then expand that instance.
- Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
- Expand Stored Procedures, right-click the procedure to modify, and then click Modify.
- Modify the text of the stored procedure.
- To test the syntax, on the Query menu, click Parse.
- To save the modifications to the procedure definition, on the Query menu, click Execute.
- To save the updated procedure definition as a Transact-SQL script, on the File menu, click Save As. Accept the file name or replace it with a new name, and then click Save.
Using Transact-SQL To modify a procedure in Query Editor
- In Object Explorer, connect to an instance of Database Engine and then expand that instance.
- Expand Databases, expand the database in which the procedure belongs. Or, from the tool bar, select the database from the list of available databases. For this example, select the AdventureWorks2012 database.
- On the File menu, click New Query.
- Copy and paste the following example into the query editor. The example creates
the uspVendorAllInfo procedure, which returns the names of all the vendors in
the Adventure Works Cycles database, the products they supply, their
credit ratings, and their availability.
USE TestDb;
GO
IF OBJECT_ID ( uspVendorAllInfo', 'P' ) IS NOT NULL
DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON;
SELECT v.Name AS Vendor, p.Name AS 'Product name',
v.CreditRating AS 'Rating',
v.ActiveFlag AS Availability
FROM Vendor v
INNER JOIN ProductVendor pv
ON v.BusinessEntityID = pv.BusinessEntityID
INNER JOIN Product p
ON pv.ProductID = p.ProductID
ORDER BY v.Name ASC;
GO
- On the File menu, click New Query.
- Copy and paste the following example into the query editor. The example modifies the uspVendorAllInfo procedure. The EXECUTE AS CALLER clause is removed and the body of the procedure is modified to return only those vendors that supply the specified product. TheLEFT and CASE functions customize the appearance of the result set.
USE TestDB;
GO
ALTER PROCEDURE uspVendorAllInfo
@Product varchar(25)
AS
SET NOCOUNT ON;
SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name',
'Rating' = CASE v.CreditRating
WHEN 1 THEN 'Superior'
WHEN 2 THEN 'Excellent'
WHEN 3 THEN 'Above average'
WHEN 4 THEN 'Average'
WHEN 5 THEN 'Below average'
ELSE 'No rating'
END
, Availability = CASE v.ActiveFlag
WHEN 1 THEN 'Yes'
ELSE 'No'
END
FROM Vendor AS v
INNER JOIN ProductVendor AS pv
ON v.BusinessEntityID = pv.BusinessEntityID
INNER JOIN Product AS p
ON pv.ProductID = p.ProductID
WHERE p.Name LIKE @Product
ORDER BY v.Name ASC;
GO
- To save the modifications to the procedure definition, on the Query menu, click Execute.
- To save the updated procedure definition as a Transact-SQL script, on the File menu, click Save As. Accept the file name or replace it with a new name, and then click Save.
- To run the modified stored procedure, execute the following example.
EXEC uspVendorAllInfo N'LL Crankarm';
GO
Delete a Procedure:
Using SQL Server Management Studio To delete a procedure in Object Explorer
1. In Object Explorer, connect to an instance
of Database Engine and then expand that instance.
2. Expand Databases, expand the database in which the procedure belongs,
and then expand Programmability.
3. Expand Stored
Procedures, right-click the
procedure to remove, and then click Delete.
4. To view objects that depend on the procedure,
click Show Dependencies.
5. Confirm the correct procedure is selected, and then
click OK.
6. Remove references to the procedure from any dependent
objects and scripts.
Using Transact-SQL To delete a procedure in Query Editor
1. In Object Explorer,
connect to an instance of Database Engine and then expand that instance.
2. Expand Databases,
expand the database in which the procedure belongs, or, from the tool bar,
select the database from the list of available databases.
3. On the File menu, click New Query.
4. Obtain the name of stored procedure to remove in the
current database. From Object Explorer, expand Programmability and then expand Stored Procedures.
Alternatively, in the query editor, run the following statement.
SELECT name AS procedure_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.procedures;
Copy and paste
the following example into the query editor and insert a stored procedure name
to delete from the current database.
DROP PROCEDURE <stored procedure name>;
GO
5.
Remove references to the procedure from any dependent
objects and scripts.
Limitations and Restrictions
1. Procedure names must
comply with the rules for identifiers.
2. Renaming a stored
procedure will not change the name of the corresponding object nam
in the definition column of thesys.sql_modules catalog view. Therefore, we recommend
that you do not rename this object type. Instead, drop and re-create the stored procedure
with its new name.Changing the name or definition of a procedure can cause dependent
objects to fail when the objects are not updated to reflect the changes that have been made
to the procedure.
in the definition column of thesys.sql_modules catalog view. Therefore, we recommend
that you do not rename this object type. Instead, drop and re-create the stored procedure
with its new name.Changing the name or definition of a procedure can cause dependent
objects to fail when the objects are not updated to reflect the changes that have been made
to the procedure.
No comments :
Post a Comment