Store procedure

As server – side code, stored procedures offer several benefits:
•    Stored procedures are compiled and are the faster possible means of executing a batch or query.
•    Executing the processing at the server instead of the desktop greatly reduces network traffic.
•    Stored procedures offer modularity and are an easy means of deploying features and code changes. If the front – end application calls a stored procedure to perform some processing, modifying a stored procedure in a single location upgrades all users.
•    Stored procedures can be an important component in database security. If all user access goes through stored procedures, direct access to the tables can be denied and all access to the data can be controlled.
                       
                Create, Alter, and Drop
   
     Stored procedures are managed by means of the data definition language (DDL) commands: create, alter, and drop.

Create must be the first command in a batch; the rumination of the batch ends the creation of the stored procedure. The following example creates a simple stored procedure that retrieves data from the ProductCategory table in the OBXKites database:

USE OBXKites;
go
CREATE PROCEDURE CategoryList
AS
SELECT ProductCategoryName, ProductCategoryDescription
FROM dbo.ProductCategory;
RETURN;

Dropping a stored procedure removes it from the database. Altering a stored procedure replaces the entire existing stored procedure with new code. When modifying a stored procedure, altering it is preferable to dropping and recreating it, because the latter method removes any permission.

Returning a Record Set
If a stored procedure is a saved batch, then whatever a batch can do a stored procedure can do. Just as a batch returns a record set from a SQL select query, a stored procedure will also return a record set from a query.

Referring back to the stored procedure that was created in the preceding section, when the CategoryList stored procedure is executed, the query within the stored procedure returns all rows from the productcategory table:

EXEX CategoryList;b

Compiling Stored Procedures
Compiling a stored procedure is an automatic process. Stored procedure compile and are stored in memory the first time they are executed. Rather more accurately, SQL Server develops query execution plans for the queries and code within the stored procedures, and these query execution plans are stored in memory.

SQL Server uses the Master.dbo.SysCacheObjects table to track compiled objects. Example: -

SELECT cast(C.sql as Char(35)) as StoredProcedure, cacheobjtype, usecounts as Count FROM Master.dbo.SysCacheObjects C JOIN Master.dbo.SysDatabase D ON C.dbid = C.dbid WHERE D.Name = DB_Name() AND ObjType = ‘Proc’ ORDER BY StoredProcedure;

A stored procedure’s query execution plan can become obsolete while it’s in memory. If the data distribution changes radically, or indexes is created or dropped, recompiling the stored procedure will result in improved performance. To manually force a recompile of a stored procedure, use the sp_recompile system stored procedure. It flag the stored procedure (or trigger) so that it will be compiled the next time it’s executed:

EXEC sp_recompile Category List;

Stored Procedure Encryption

When the stored procedure is created, the text of the stored procedure is saved in the SysComments table. The text is not stored for the encryption of the stored procedures, but only so that it may be retrieved later if the stored procedure needs to be modified.

The sp_helptext system stored procedure will extract the original text of the stored procedure:

Sp_helptext CategoryList;

If the stored procedure is created with the with encryption option, the stored procedure test in SysComments is not directly readable. It’s common practice for third- party vendors to encrypt their stored procedures. The following alter command stores the CategoryList procedure with with encryption and then attempts to read the code:

ALTER PROCEDURE CategoryList
 WITH ENCRYPTION
AS
SELECT * FROM dbo.ProductCategory;

sp_helptext CategoryList;

System Stored Procedures

The basic SQL syntax includes only 10 commands: select, insert, update, delete, create, alter, drop, grant, revoke, and deny. Microsoft performs hundreds of tasks with system stored procedures stored in the master database, to make these procedures available to all databases; special rules govern the scope of system stored procedures. Any procedures beginning with sp_ in the master database can be executed from any database. If a name conflict exists between a system stored procedure and a stored procedure in the local user database, the system stored procedure in the local database is executed.

Note: - When creating stored procedure, use a consistent naming convention other than sp_ to name your stored procedures. Using sp_can only cause name conflicts and confusion. I prefix the names of stored procedures with p, but even no prefix is better than sp_.

Passing Data to Stored Procedures

A stored procedure is more useful when it can be manipulating by parameters. The CategoryList stored procedure created previously returns all the product categories, but a procedure that performs a task on an individual row will require a method for passing the row ID to the procedure.

SQL Server stored procedures may have numerous input and output parameters (up to 2,100 to be exact).
Input Parameters

We can add input parameters that pass data to the stored procedure by listing the parameters after the procedure name in the create procedure command. Each parameter must begin with an @ sign, and becomes a local variable within the procedure. Like local variables, the parameters must be defined with valid data types. When the stored procedure is called, the parameter must be included (unless the parameter has a default value).

The following code sample creates a stored procedure that returns a single product category. The @CategoryName parameter can accept Unicode character input up to 35 characters in length. The value passed by means of the parameter is available within the stored procedure as the variable @CategoryName in the Where clause:

USE OBXKites;

Go

CREATE PROCEDURE CategoryGet(@CategoryName NVARCHAR(35))
AS
SELECT ProductCategoryName, ProductCategoryDescription FROM dbo.ProductCategory WHERE ProductCategoryName = @CategoryName;

When the following code sample is executed, the string literal ‘Kite’ is passed to the stored procedure and substituted for the variable in the where clause:

EXEC CategoryGet ‘Kite’;

Parameter Defaults

We must supply every parameter when calling a stored procedure, unless that parameter has been created with a default value. We establish the default by appending an equal sign and the default to the parameter, as follows:
CREATE PROCEDURE StoredProcedure(@Variable DataType = DefaultValue);

The following code, extracted from the OBXKites sample database, demonstrates a stored procedure default. If a product category name is passed in this stored procedure, the stored procedure returns only the selected product category. However, if nothing is passed, the null default is used in the where clause to return all the product categories:

CREATE PROCEDURE pProductCategory_Fetch2(@Search NVARCHAR(50) = NULL)

-- If @Search = null then return all ProductCategories
-- If @Search is value then try to find the Name
As
SET NOCOUNT ON;
SELECT ProductCategoryName, ProductCategoryDescription
FROM dbo.ProductCategory WHERE ProductCategoryName = @Search OR @Search IS NULL;

IF @@RowCount = 0
RAISERROR(‘Product Category ‘’%s’’ Not Found.’,14,1,@Search);

Returning Data from Stored Procedures

SQL Server provides four means of returning data from a stored procedure.  A batch can return data via select statement or a raiserror command. Stored procedures inherit these form batches and add output variable and the return command.

Output Parameters

Output Parameters enable a stored procedure to return data to the calling client procedure.
The keyword output is required both when the procedure is created and when it is called. Within the stored procedure, the output parameter appears as a local variable. In the calling procedure or batch, a variable must have been created to receive the output parameter. When the stored procedure concludes, its current value is passed to the calling procedure’s local variable.

Although output parameters are typically used solely for output, they are actually two – way parameters.

Note: - Output parameters are useful for returning single units of data when a whole record set is not required. For returning a single row of information, using output parameters is blazingly faster than preparing a record set.

The next code sample uses an output parameter to return the product name for a given product code form the Product table in the OBXKites sample database. To set up for the output parameter:
1)    The batch declares the local variable @ProdName to receive the output parameter.
2)    The batch calls the stored procedure, using @ProdName in the exec call to the stored procedure.
3)    Within the stored procedure, the @ProductName output parameter/ local variable is created in the header of the stored procedure. The initial value is null.
With everything in palace, the process continues. The data path for the @ProductName output parameter is as follows:
4)    The select statement inside the stored procedure sets @ProductName to Basic Box Kite 21 inch, the product name for the product code “1001.”
5)    The stored procedure finishes and execution is passed back to the calling batch. The value is transferred to the batch’s local variable, @ProdName.
6)    The calling batch uses the print command to send @ProdName to the user.
This is the stored procedure:
USE OBXKites;
Go
CREATE PROC GetProductName (@ProductCode CHAR(10), @ProductName VARCHAR(25) OUTPUT)
AS
SELECT @ProductName = ProductName
FROM dbo.Product WHERE Code = @ProductCode;

Using the Return Command

A return command unconditionally terminates the procedure and returns a value to the calling batch or client. Technically, a return can be used with any batch, but it can only return a value from a stored procedure or a function.

A return value of 0 indicates success and is the default. Microsoft reserves -99 to -1 for SQL Server user. It’s recommended that you use -100 or lower to pass back a failure status.

Note: - Use the return value to pass back a success/fail value, but avoid using it to pass back actual data. If you only need to return a value and not a full data set, use an output parameter.

When calling a stored procedure returns a success or failure status, depending on the parameter:
CREATE PROC IsItOK(@OK VARCHAR(10))
AS
IF @OK = ‘OK’
  RETURN 0
ELSE
    RETURN -100;

The calling batch:
    DECLARE @ReturnCode INT;
    EXEC @ReturnCode = IsITOK ‘OK’;
    PRINT @ReturnCode;
    EXEC @ReturnCode = IsItOK ‘NotOK’;
    PRINT @ReturnCode;
Path and Scope of Returning Data

Any stored procedure has four possible methods of returning data (select, raiserror,output parameters, and return). Deciding which method is right for a given stored procedure depends on the quantity and purpose of the data to be returned, and the scope of the method used to return the data. The return scope for the four methods is as follows:

Return and output parameters are both passed to local variables in the immediate calling procedure or batch within SQL Server.
Raiserror and a selected record set are both passed to the end – user client application. The immediate calling procedure or batch is completely unaware of the raiserror or selected record set.