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 precompiled. 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 spGetShashi
AS
SELECT * FROM SHASHI
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 Query Analyzer.
EXEC spGetShashi
This stored procedure creates a result set and returns to 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.
Now days every one is familiar with SQL Injection Attack I think stored are the way this can be prevented from this malicious attack.
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)
Handling error in stored procedure
In addition to passing output parameters back to the calling program, stored procedures also pass back a return value. 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 thats 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 spDeleteShashi @FirstName varchar(50)
As
DECLARE @ErrorVar int
DELETE FROM SHASHI 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.
The classification of stored procedures is depends on the Where it is Stored. Based on this you can divide it in 4 sections.
System stored procedures
Local stored procedures
Temporary stored procedures
Extended stored procedures
System Stored Procedures:
System stored procedures are stored in the Master database and are typically named with a sp_ prefix. They can be used to perform variety of tasks to support SQL Server functions that support external application calls for data in the system tables, general system procedures for database administration, and security management functions.
For example, you can view the contents of the stored procedure by calling
sp_helptext [StoredProcedure_Name].
Local stored procedures
Local stored procedures are usually stored in a user database and are typically designed to complete tasks in the database in which they reside. While coding these procedures don’t use sp_ prefix to you stored procedure it will create a performance bottleneck. The reason is when you can any procedure that is prefixed with sp_ it will first look at in the mater database then comes to the user local database.
Temporary stored procedures
A temporary stored procedure is all most equivalent to a local stored procedure, but it exists only as long as SQL Server is running or until the connection that created it is not closed. The stored procedure is deleted at connection termination or at server shutdown. This is because temporary stored procedures are stored in the TempDB database. TempDB is re-created when the server is restarted.
There are three types of temporary stored procedures: local , global, and stored procedures created directly in TempDB.
A local temporary stored procedure always begins with #, and a global temporary stored procedure always begins with ##. The execution scope of a local temporary procedure is limited to the connection that created it. All users who have connections to the database, however, can see the stored procedure in Query Analyzer. There is no chance of name collision between other connections that are creating temporary stored procedures. To ensure uniqueness, SQL Server appends the name of a local temporary stored procedure with a series of underscore characters and a connection number unique to the connection. Privileges cannot be granted to other users for the local temporary stored procedure. When the connection that created the temporary stored procedure is closed, the procedure is deleted from TempDB.
Any connection to the database can execute a global temporary stored procedure. This type of procedure must have a unique name, because all connections can execute the procedure and, like all temporary stored procedures, it is created in TempDB. Permission to execute a global temporary stored procedure is automatically granted to the public role and cannot be changed. A global temporary stored procedure is almost as volatile as a local temporary stored procedure. This procedure type is removed when the connection used to create the procedure is closed and any connections currently executing the procedure have completed.
Temporary stored procedures created directly in TempDB are different than local and global temporary stored procedures in the following ways:
You can configure permissions for them.
They exist even after the connection used to create them is terminated.
They aren't removed until SQL Server is shut down.
Because this procedure type is created directly in TempDB, it is important to fully qualify the database objects referenced by Transact-SQL commands in the code. For example, you must reference the Authors table, which is owned by dbo in the Pubs database, as pubs.dbo.authors.
--create a local temporary stored procedure.
CREATE PROCEDURE #tempShashi
AS
SELECT * from [pubs].[dbo].[shashi]
--create a global temporary stored procedure.
CREATE PROCEDURE ##tempShashi
AS
SELECT * from [pubs].[dbo].[shasi]
--create a temporary stored procedure that is local to tempdb.
CREATE PROCEDURE directtemp
AS
SELECT * from [pubs].[dbo].[shashi]
Extended Stored Procedures
An extended stored procedure uses an external program, compiled as a 32-bit dynamic link library (DLL), to expand the capabilities of a stored procedure. A number of system stored procedures are also classified as extended stored procedures. For example, the xp_sendmail program, which sends a message and a query result set attachment to the specified e-mail recipients, is both a system stored procedure and an extended stored procedure. Most extended stored procedures use the xp_ prefix as a naming convention. However, there are some extended stored procedures that use the sp_ prefix, and there are some system stored procedures that are not extended and use the xp_ prefix. Therefore, you cannot depend on naming conventions to identify system stored procedures and extended stored procedures.
Use the OBJECTPROPERTY function to determine whether a stored procedure is extended or not. OBJECTPROPERTY returns a value of 1 for IsExtendedProc, indicating an extended stored procedure, or returns a value of 0, indicating a stored procedure that is not extended.
USE Master
SELECT OBJECTPROPERTY(object_id('xp_sendmail'), 'IsExtendedProc')
Shashi Ray