Stored Procedures
What Stored Procedure means
A Stored procedure is a database object that contains one or more SQL statements. In this article you will get an idea on how to create and use stored procedures and also highlighted on how to use stored procedure.
The first time a stored procedure is executed, each SQL statement it contains is compiled and executed to create an execution plan. Then procedure is stored in compiled form with in the database. For each subsequent execution, the SQL statements are executed without compilation, because they are pre-compiled. This makes the execution of stored procedure faster than the execution of an equivalent SQL script.
To execute a stored procedure you can use EXEC statement.
CREATE PROC spGetAuthors
AS
SELECT * FROM AUTHORS
When you run this script in Pubs database you will get the following message in Query Analyzer:
The Command(s) completed successfully.
Now you are ready to call/execute this procedure from a Query Analyzer.
EXEC spGetAuthors
This stored procedure creates a result set and returns to the client.
You can call a stored procedure from within another stored procedure. You can even call a stored procedure from within itself. This technique is called a recursive call in programming. One of the advantages of using stored procedures is that application programmers and end users don’t need to know the structure of the database or how to code SQL. Another advantage of it is they can restrict and control access to a database.
Nowadays every one is familiar with SQL Injection Attack I think stored are the way this can be prevented from this malicious attack.
How to Create a Stored Procedure
When the CREATE PROCEDURE statement is executed, the syntax of the SQL statements within the procedure is checked. If there is any coding error the system responds with an appropriate message and the procedure will not be created.
The Syntax for a CREATE PROCEDURE statement is
CREATE {PROC|PROCEDURE} Procedure_name
[Parameter_declaration]
[WITH {RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION}]
AS sql_statements
You can use CREATE PROCEDURE statement to create a stored procedure in the database. The name of the stored procedure can be up to 128 characters and is typically prefixed with the letters sp. The AS clause contains the SQL statements to be executed by the stored procedure.
Recompile is used when you want to compile the stored procedure every time when you call. This comes into picture when one doesn’t want to catch the execution plan of stored procedures in the database. Encryption implies that you want to hide this code so that no one can see it. This is very important when you want to distribute the code across the globe or when you want to sell this code to other vendors. But make sure you have the original copy.
Apart from the stored procedure that are stored in the database as permanent entities, you can also create stored procedure for single sessions. That means as long the as the session is alive the stored procedure is available in the memory means in the database. Once the session ends the stored procedure vanishes. This actually depends on what type of stored procedure you have chosen to create it.
Stored procedure provide for two different types of parameters: Input parameters and Output Parameters. An input Parameter is passed to the stored procedure from the calling program. An output parameter is returned to the calling program from the stored procedure. You can identify an output parameter with the OUTPUT keyword. If this keyword is omitted the parameter is assumed to be an input parameter (by default).
You can declare an input parameter so that it requires a value or so that its value is optional. The value of a required parameter must be passed to the stored procedure from the calling program on an error occurs. The value of an optional parameter doesn’t need to be passed from the calling program. You identify an optional parameter by assigning a default value to it. Then if a value isn’t passed from the calling program, the default value is used. You can also use output parameter as input parameters. That is you can pass a value from the calling program to the stored procedure through an output parameter. However is not advisable to pass parameters to Output parameters.
The syntax for declaring the parameters
@Parameter_name_1 data_type [= default] [OUTPUT]
[, @Parameter_name_2 data_type [= default] [OUTPUT]…
Parameter declarations
@FirstName varchar(50) -- Input parameter that accepts a string.
@LastName varchar(50) -- Output Parameter that returns a string.
Create Procedure statement that uses an input and an output parameter.
CREATE PROC spGetAuthors
@FirstName varchar(50),
@LastName varchar(50)
AS
SELECT @LastName= ln_Name
FROM AUTHORS
WHERE fn_name = @FirstName
Create procedure statement that uses an optional parameter.
CREATE PROC spGetAuthors
@LastName varchar(50),
@FirstName varchar(50) = ‘vijay’
AS
SELECT @LastName= ln_Name
FROM AUTHORS
WHERE fn_name = @FirstName
A stored procedure can declare up to 2100 parameters. If you declare two or more parameters, the declarations should be separated by commas.
Calling stored procedure with Parameters
To pass parameter values to a stored procedure, you code the values in the EXEC statement after the procedure name. You can pass the parameters either by position or by name.
Passing parameters by Name:
Write the following code in Query Analyzer
DECLARE @LN VARCHAR(100)
EXEC spGetAuthors @FirstName = ‘krishna’, @LastName = @LN OUTPUT
Passing parameters by Position:
DECLARE @LN VARCHAR(100)
EXEC spGetAuthors @LN OUTPUT, ‘krishna’
In fact you can use both notations to pass parameters to stored procedures when you are calling. To pass parameters by position, list them in the same order as they appear in the CREATE PROCEDURE statement and separate them with commas. When you use this technique, you can omit optional parameters only if they are declared after any required parameters.
To use an output parameter in the calling program, you must declare a variable to store its value. Then you use the name of the variable in the EXEC statement and you code the OUTPUT keyword after it to identify it as an output parameter.
Handling error in stored procedure
In addition to passing output parameters back to the calling program, stored procedures also return values. By default, this value is zero. If an error occurs during the execution of a stored procedure you may want to pass a value back to the calling environment that indicates the error that occurred. To do that you, use the RETURN statement and the @@ERROR function.
The @@ERROR system function returns the error number that’s generated by the execution of the most recent SQL statement. If the value is zero, it means that no error has occurred. The stored procedure listed below uses this function to test whether a DELETE statement that deletes a row from authors table is successful.
CREATE PROC spDeleteAuthors @FirstName varchar(50)
As
DECLARE @ErrorVar int
DELETE FROM AUTHORS WHERE fn_name = @FirstName
SET @ErrorVar = @ERROR
IF @ErrorVar <> 0
BEGIN
PRINT ‘An Unknown Error Occurred’
RETURN @ErrorVar
END
RETURN statement immediately exists the procedure and returns an optional integer value to the calling environment. If you don’t specify the value in this statement the return value is zero.
How to delete or change a stored procedure
You use DROP PROC statement to delete one or more stored procedures from database. To redefine the stored procedure you use ALTER PROC.
The syntax of the DROP PROC statement
DROP {PROC|PROCEDURE} Procedure_name [, …]
The syntax of the ALTER PROC statement
ALTER {PROC|PROCEDURE} Procedure_name
[Parameter_declaration]
[WITH {RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION}]
AS sql_statements
When you delete a procedure any security permission that are assigned to the procedure are also deleted. In that case you will want to use the ALTER PROC statement to modify the procedure and preserve permissions.
I will discuss the System stored procedure and other types of stored procedures in my next article.
|
| Author: Brainstorming Guy 11 Mar 2005 | Member Level: Diamond Points : 0 |
Hai,
Vijay. Good one to start with. Hope a lot from you.
Regards,
Brainstorming Guys
|
| Author: Choudhary G. 29 Aug 2005 | Member Level: Bronze Points : 0 |
Hi,
Vijay....
this artilce is very nice
regards,
Choudhary
|
| Author: Visweswara Sriadibhatla 07 Sep 2006 | Member Level: Bronze Points : 0 |
Vijay,
I am new to SQL Server my requirement is I want to create a SP which takes IN parameters and my OUT parameters are more than one refcursor. If possible can u give me some sample code.
kalajee@gmail.com
|
| Author: Mohan Chug 24 Jun 2007 | Member Level: Bronze Points : 0 |
Thanks Vijay
|
| Author: archana 03 Feb 2008 | Member Level: Silver Points : 0 |
Good starting boost for beginners...
Thanks a lot,
Archana
|
| Author: Sandhya 18 Jul 2008 | Member Level: Bronze Points : 1 |
Hi
This is very good article, thanks for sharing your knowledge.
regards
Sandhya
|