Stored procedure pedagogy
Stored procedures are a blessing to the database developer. They are portable and secure. They encapsulate business logic and provide a foolproof mode of execution. This article explains the basics of the same to the reader. Target audience - novice level
Stored procedure pedagogy
------------------------
A stored procedure is a group of sql statements that has been created and stored in the database.
Stored procedures are pre-compiled and present an optimized piece of code.
They have some inherent advantages :
They prevent execution of several lines of inline SQL.
This means that once the stored procedure is compiled the execution plan does not have to be regenerated at every execution.
This improves speed of execution.
They present a piece of code that can be repeatedly invoked in a calling program without any significant overhead.
They reduce network traffic as several SQL statements that would otherwise be executed over a connection are not required as the logic and SQL statements are encapsulated within the stored procedure itself.
Stored procedures can be assigned rights depending on the User identity.
Further those users who would otherwise have been denied execution rights on specific SQL.
statements can now be allowed permission to execute the stored procedure without compromising data security.
Stored procedures can be :
- System defined
- User defined
- System defined stored procedures are usually prefaced by sp_. eg) sp_who2 'active'. These are provided by the vendor.
- User defined stored procedures are developed by the User and provide business logic that can be encapsulated in the database tier.
Syntax
---------------
As an example consider the following script.
CREATE PROCEDURE xsp_GetUserName
@location varchar(10)
AS
SELECT UserName
FROM Users
WHERE city = @location
To execute the stored procedure for Delhi cityEXECUTE xsp_GetUserName 'Delhi'
This is a very basic stored procedure. It takes one parameter - the location and returns the User name for that location. (assuming that there exists one user for each location, of course). We could modify this as :
CREATE PROCEDURE xsp_GetUserName
@location varchar(10)
AS
SELECT ISNULL(UserName,'NONE') AS UserName
FROM Users
WHERE city = @location
This would overcome the above limitation.
As a further amendment we could have an OUTPUT parameter. In this case we can use the stored procedure to return a value.
CREATE PROCEDURE xsp_GetUserName
@location varchar(10)
@Userprofilename varchar(20) OUTPUT
AS
SELECT ISNULL(UserName,'NONE') AS UserName, @Userprofilename = UserProfileName
FROM Users
WHERE city = @location
To execute the stored procedure for Delhi city
DECLARE @Userprofilename varchar(20)
EXECUTE xsp_GetUserName 'Delhi', @Userprofilename = @Userprofilename OUTPUT
Tips while using stored procedures.
-----------------------------------------
1.You can embed DML statements in a stored procedure.
2.You can embed structured exception handling.i.e TRY-CATCH-FINALLY block.
3.You can have parameters of different data types as per business needs.
4.You can use temporary tables or table variables in stored procedures.
5.You can reference a view in a stored procedure.
6.You can make a nested call in a stored procedure. i.e. call one procedure in another.
7.You can pass a udt as an argument to a stored procedure. However this should be with the READONLY parameter.
8.You can drop and rename stored procedures just like other database objects.
9.Using dynamic SQL in stored procedures is not recommended. As per OWASP it constitutes a SQL injection risk and is an opportunity for hackers to exploit your system.
10. As discussed earlier, it is possible to return values from a stored procedure using the OUTPUT parameter.
11. In .NET stored procedures should be invoked by using the SQLCommand object. This provides security at execution time.
To sum up, Stored procedures provide an ideal method of executing SQL statements with minimum overhead and easy deployability and portability.
They are distinct database objects and have rights and privileges just like other objects eg)tables.
Imagine having to work with individual SQL statements...What a nightmare that would
become !!!