Pages

Men

rh

10/18/2013

Stored Procedures in Sql Server

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.

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )