If a repetitive T-SQL task has to be executed within an application, then the best repository for it is a program called a stored
procedure, stored in SQL Server. Storing the code inside the SQL Server object gives us many advantages, like:
Security due to encryption
Performance gains due to compilation
Being able to hold the code in a central repository:
Altering the code in SQL Server without replicating in several different programs
Being able to keep statistics on the code to keep it optimized
Reduction in the amount of data passed over a network by keeping the code on the server
Hiding the raw data by allowing only stored procedures to gain access to the data
You may have executed some ad-hoc queries for tasks like inserting data, querying information in other systems, or creating
new database objects such as tables. All these tasks can be placed within a stored procedure, so that any developer can run
the same code without having to recreate the T-SQL commands. Also, generalizing the code for all values makes it generic
and reusable.
Stored procedures are more than just tools for performing repetitive tasks. There are two main types of stored procedure –
system stored procedures and user-defined stored procedures. We also have extended stored procedures that can reside as
either system or user-defined types. Extended stored procedures give functionality that is not necessarily contained within
SQL Server, like allowing DOS commands to run and working with e-mail. It is also possible to create your own extended
stored procedures.
Sample example of stored procedure:
/* DECLARE @OutPutValue VARCHAR(100) EXEC spExample 'CodeProject', @OutPutValue OUTPUT PRINT
@OutPutValue
*/ CREATE PROCEDURE [dbo].[spExample] @parameter1 VARCHAR(100) ,@parameter2 VARCHAR(200) OUTPUT AS
BEGIN DECLARE @parameter3 VARCHAR(100) SET @parameter3 = ' Your development resources.
' IF @parameter1 IS NOT NULL AND LEN(@parameter1) > 1 SELECT @parameter2 = 'The ' + @parameter1
+ @parameter3 ELSE SELECT @parameter2 = 'CodeProject is cool!' RETURN END GO
