Asp.net programming we consider all important topic for knowledge about controls,web Services, JSON, Ajax, database, and interview related Question. In this Asp.net and sql server tutorial blog we give many examples of jquery as like How Play YouTube Video in Your Asp.net Website by URL, How to Play YouTube Videos in Asp.net by using Jquery , Jquery Change div background on mouseover in asp.net , JQuery UI Datepicker (Calendar) etc.

Saturday, August 9, 2014

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.

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:

No comments:

Post a Comment