Store Procedure in Sql

-

SQL Procedure:

A procedure  in Sql Server is prepared SQL code that we save so we can reuse the code again and again.  So if we think about a SQL query that we write again and again, write that query each time  so solving this we save it as a procedure and then just call the SQL procedure to execute the SQL code that the procedure.

A stored procedure is a sub routine available to applications that access a relational DBMS system. A stored procedure  also know as  proc,  StoPro, proc,StoredProc, sp, StoreProc or SP .it is  stored in the database.

Advantage of Store Proc:

  • Stored procedures can be used to control transaction management System.
  •  SQL procedures run inside a transaction.
  • Stored procedures can invoke from a trigger or a condition handler.
  • We have ability to pass parameters into stored procedure,

Benefits of stored procedure:

Execution Pre compiled: SQL Server compiles each only one time procedure and then reuse the execution plan. This is called repeatedly.
Reduced System (client/server) traffic:  If network bandwidth is a slow in environment, procedures can reduce long SQL queries to a single line.
Reuse of code and abstraction programming:  Stored proc can be used by more than one users and client application programs.
Security control Enhancement:  We can give users permission to execute that particular  procedure independently.

Type of creating SQL Server stored procedures:

  • Creating  simple SQLsp.
  • Input parameters Using SQLsp
  • Output parameters Using SQLsp
  • Try Catch Using SQLsp
    Create Storeprocedure in Sql by parijat
    Create Storeprocedure in Sql

Structure of stored procedures:

Stored procedures are same as constructs in other programming languages. Execution time depend on data in the form of input parameters. Example is Creating Sp here. 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO


These input parameters are used in the execution of a series of SQL Queries in SQLsp. This result is called a record set.The main use of stored procedures is the ability to give parameters.

Other Post:

Comments

Post a Comment