Working with Stored Procedures

Stored procedures are stored in SQL Server databases. The simplest implication of stored procedures is to save complicated queries to the database and call them by name, so that users won’t have to enter
the SQL statements more once. As you see, stored procedures have many more applications, and you can even use them to build business rules into the database.
How to create a Stored Procedure,
As shown given below, created a Stored Procedure for Inserting records into Table call Holiday_Details, which has Code and Description fields.
In this Stored Procedure, passing two parametrs as INPUT Parameters and one OUTPUT parameter.
Normally in Stored Procedure we can pass parameters as Input / Output Stored parameters. When you define output parameters, we have to implicitly specify the OUTPUT Keyword.
Here I have shown the simple stored procedure.

CREATE PROCEDURE  [dbo].[SP_Holiday]

@Code char(3),
@Desc varchar(100),
@flag bit,
@Err  Varchar(MAX)=Null OUTPUT

AS

Begin Transaction
if @flag=0
 begin
  INSERT INTO Holiday_Details Values(@code,@Desc)
 end

if @flag=1
 begin
  UPDATE Holiday_Details SET Description=@Desc WHERE Code=@code
 end
 
If @@ERROR <>0
 Begin
    Set @Err=cast(@@Error as varchar(max))
  Rollback Transaction
  print @Err   
  return
 End
Else if @@ERROR<>0
 Begin
  Set @Err='Successfully done!!'
  print @Err
 End 
 
Commit Transaction

Here we are trying Insert/Update the records into Holiday_Details. For diferentiate the Insert and Update, we are using @flag input parameter. So when we execute this parameter we should specify the @flag whether it is Insert/Update.
@Err parametr is Output parameter for get the status of execution whether it is successfully Inserted/Updated or thrown any error.
So, if you execute this procedure, you would get the output of status.
Execute of a Stored Procedure
When you execute a Stored Procedure, you have to use either Execute/EXEC
(Exec is special case, I will explain about in another article) keyword for execute the Stored Procedure.

EXEC SP_Holiday 'bb1','ffff',0

EXECUTE SP_Holiday '332','ffff',0

Here we have not pased value for OUTPUT parameter since we have assined Null as intial value which means in case if you forget topass the paraneter, Variable would take the Intialized value.
So, once you run this stored Procedure, this would return the value of @Err parameter.
Main purposes of Using Stored Procedures
When as SQL statement, especially a complicated one, is stored in the database as stored procedure, its execution plan is designed once, cached, and is ready to be used again.
Moreover, stored procedures can be designed once, tested, and used by many usres and applications. If the same stored procedure is used by more than user, the DBMS keeps only one copy of the procedure in memory, and all users share the same instance of the procedure. This means more efficient memory utilization.
Finally, you can limit user access to database’s tables and force users to access the database throgh stored procedures. This is simple method of enforcing business rules.