Stored Procedure – Basic

What Is A Stored Procedure?
.It is a best way to implement the business logic programming.
.Group of TSQL statements compiled into a single execution plan.
It means one can write the series of SQL statement that will be executed with one name in one shot.
Whenever we execute the SQL statement five steps are performed by SQL:
1) Security Checks.
2) Syntax.
3) Compilation checks.
4) Execution Plan and so forth…

.Provides encapsulation of logic & action.
.Provides additional security (Update table to which we don’t want to allow updates from other location).
.Simplifies management/administration.

Creating/Executing Stored Procedures
.Create Procedure.
.Create Proc.
.Basic Syntax:
– Create Proc
– As –Indicate that from next line TSQL statements starts.

create procedure stp_test
as
select * from test

execute stp_test
Altering And Dropping SP
.Drop stored procedures using the DROP PROCEDURE statement.
.Alter (change) stored procedures using the ALTER PROCEDURE statement.
.Preserves security settings.

alter procedure stp_test
as
select name,phone from test

When we use Drop command to drop procedure
drop procedure stp_test
Then SQL create another id for the project (object) hence loses the security setting of the procedure.
Input Parameters
.Allow the caller to pass a data value to the stored procedure for processing.
.Can be set to a constant or assigned to a variable.
.Example:
create procedure stp_test
@name as varchar(20)
as
select name,phone from test
where name = @name

execute stp_test ‘Test’
Output Parameters
.A stored procedure can return information to the calling procedure.
.To use an output parameter you specify the OUTPUT keyword in the CREATE PROC and EXEC statements.
Example:
create Proc Addition
@Num1 int,
@Num2 int,
@Sum int output
As
Set @Sum = @Num1 + @Num2

declare @Sum int
exec Addition 1,2, @Sum output
Select @Sum
Recompiling Stored Procedures
.First time execution:
– Resolution : Syntax checking and parsing.
– Optimization : Best way to execute underlying query.
– Compilation : Machine Code Conversion.
– Execution
.Subsequent executions:
– Execution.

When we create a procedure it takes all the indexes of all the objects in the query.
and if any changes occurs in the table aur database structure, procedure doesn’t take that.

.Stored procedures are compiled:
– First time stored procedure is executed (each time SQL Server is restarted).
– If an underlying table changes.
– Manually
. exec sp_recompile
– Sp will be recompiled on the next execution.
– WITH RECOMPILE at build.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s