SQL Server T-SQL Stored Procedures for beginners

It is important to choose the right choice to achieve an optimal performance. Here I will elaborate some key aspects of Stored procedures in T-SQL fro SQL Server, which would help to choose the right at its place.
A stored procedure is a pre-compiled batch of Transact-SQL statements stored under a name and processed as a unit that you can call from within another Transact-SQL statement or from the client applications. The major difference between batch and stored procedure is a batch is compiled at the time of execution and a stored procedure is already compiled. It can also accept parameters. Parameter name can be up to 30 characters including @. And there can be up to 2100 parameters in a SQL Server 2005 stored procedure. Stored procedures allow many users to use the same code
When a T-SQL statement is executed many things happen on server side in relational engine, like

• Parsing
• Syntax checking
• Object resolution
• Compilation
• Query plan determination
  

All this is required for every query. In case of stored procedures all this is carried out once and only. After that whenever a stored procedure is executed, nothing of above mentioned happens. This is real power of stored procedure.
Users can be given execution permission on a stored procedure that modifies data instead of permission to directly modify the table. It is very help full for security implementation.
Following T-SQL command is used to create stored procedure

CREATE PROCEDURE proc_name
AS

/*

SQL statements or batch

*/
RETURN


Created stored procedure will be stored in current database, but it will have access to all other databases. The “create procedure” command can not be combined with any other statement in a single batch. Any T-SQL statement can be included in stored procedure except the following

• create view
• create rule
• create default
• create procedure
• create trigger
• use
  

To execute the stored procedure use following command

EXECUTE proc_name
--OR
EXEC proc_name

Stored procedure can be called from

• Batches
• Other stored procedures
• Triggers
• User defined functions
  

Stored procedures may or may not return a value. Its output flexibility provides a greater edge over user defined functions. There are three ways to return data from a stored procedure.

• Result Sets
• Output variables
• Return statement
  

Following is an overview and comparison of these data return approaches.

Result Sets:
If we use data selection statement inside the stored procedure, then data will be retrieved and shown as in normal select statement, but do not forget that you will have all server and network optimization with you in this case. Following code will just do it through pubs database.

CREATE PROCEDURE testResultSet
AS
SELECT * FROM pubs..authors
GO

Output variables:
If we want to return a limited number of (mostly) calculated/aggregate values then the choice is to use output variables. These parameters will provide to handle and transport the values in any data type. Currently I am not sure the maximum number of out put parameters provided in SQL Server 2005. But in rare case one will use the maximum number. Use following code to create a SP which will return a integer value.

CREATE PROCEDURE testVars
(@firstNameCount INT OUTPUT )
AS
SELECT @FirstNameCount = COUNT(*)
FROM pubs..authors
WHERE au_fname like 'A%'
GO


And now to execute the procedure use following lines

DECLARE @counting INT
EXEC testVars
@firstNameCount = @counting OUTPUT
SELECT counting = @counting


Return Statement:
The most limited way to get data from a stored procedure is through return statement. It returns a single numeric value and is mostly used to return a status result or error code from inside a stored procedure. Use following code to provide a value to SP and simply get it back when executed.

CREATE PROC testReturnValue (@InValue int)
AS
Return @Invalue
GO

DECLARE @ReturnValue INT
EXEC @ReturnValue = TestReturnValue 3
SELECT ReturnValue=@ReturnValue
GO


Recompiling a Stored Procedure
Because the stored procedure execution plan can be outdated, for example when a large amount of data modifications are made to a table referenced by a stored procedure, you may need to recompile the execution plan. SQL Server 2000 automatically recompiles the stored procedure execution plan when one of the following conditions are met:

• Any schema changes of the objects referenced in the stored procedure were made.
• An index used by the execution plan of the stored procedure is dropped.
• A large amount of data modifications are made to a table referenced by a stored procedure.
• The new distribution statistics were generated.
• The execution plan was deleted from memory, because the memory is required for other objects.
• A table has trigger(s) and the number of rows in the inserted or deleted tables grows significantly.
  

There are three ways to cause SQL Server to recompile the stored procedure execution plan, they are:

• Including a WITH RECOMPILE clause in a CREATE PROCEDURE statement. When you include a WITH RECOMPILE clause in a CREATE PROCEDURE statement, SQL Server will not cache a plan for this procedure and the procedure will be recompiled every time it will be run. Because the stored procedure execution plan will never been cached, you should use the RECOMPILE option in a CREATE PROCEDURE statement very carefully.
• Including a WITH RECOMPILE clause in a EXECUTE statement. When you include a WITH RECOMPILE clause in a EXECUTE statement, the stored procedure execution plan will be recompiled when you run this EXECUTE statement. You can use this option if the parameters you are supplying are atypical or if the data has significantly changed.
• Using the sp_recompile system stored procedure to causes stored procedures to be recompiled the next time they are run. To cause stored procedures to be recompiled the next time they are run, you can use the sp_recompile system stored procedure. You can use the sp_recompile system stored procedure when you want your stored procedure reflects inchanges in indexes or data values.
  

Because SQL Server 2000 and SQL Server 2005 can recompile stored procedures and execution plans automatically, in most cases it is not necessary to use the sp_recompile system stored procedure or a WITH RECOMPILE clause, and you can rely on SQL Server decisions to recompile execution plan.
All these qualities make a stored procedure a very power full tool for most of optimized T-SQL processes.