How To Create Store Procedure Using Insert,Update,Delete And Select Trigger Functionality

Hello Friend Today In This Blog I Will Show You How To Create Store Procedure In Mssql. A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs.

A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again.  So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.

In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.

Take a look through each of these topics to learn how to get started with stored procedure development for SQL Server.

You can either use the outline on the left or click on the arrows to the right or below to scroll through each of these topics.

So Follow The Step To Create Store Procedure In Ms-sql.

Step 1 : Create Database Name 

Here 

create database tension

Step 2 : Create Table Name 
Here We Will Create a Table.

Here In This Step We Required Four Column With Name vid,vname,vlocation and vgender using Parameter int,varchar.

First We Will Declared vid as a NOT NULL PRIMARY KEY and Remaining as NULL.

create table viewer

(

vid int NOT NULL PRIMARY KEY,

vname varchar(50) NULL,

vlocation varchar(50) NULL,

vgender varchar(50) NULL,

);

Step 3 : CREATE PROCEDURE pro
Here We Will Create Procedure.
Here In This Step We Will Pass Five Parameter Were Four Parameter Will Be The Parameter Use In Creating Table And the Parameter @status were we will Do The Functionality Such As  Insert,Update,Delete And Select Trigger Functionality.
All The Trigger Functionality Is Done Using @status By Passing Parameter Varchar Which Is Equal To Null.

CREATE PROCEDURE pro

@vid int=null,

@vname varchar(50)=null,

@vlocation varchar(50)=null,

@vgender varchar(50)=null,

@status varchar(50)=null

AS 

BEGIN 

SET NOCOUNT ON;

---INSERT NEW RECORDS

IF @status='INSERT'

BEGIN

INSERT INTO viewer(vid,vname,vlocation,vgender)VALUES(@vid,@vname,@vlocation,@vgender)

END

---SELECT RECORDS IN TABLE

IF @status='SELECT'

BEGIN

SELECT vid,vname,vlocation,vgender FROM viewer

END

---UPDATE RECORDS IN TABLE

IF @status='UPDATE'

BEGIN

UPDATE viewer SET vname=@vname,vlocation=@vlocation,vgender=@vgender WHERE vid=@vid

END

---DELETE RECORD FROM TABLE

IF @status='DELETE'

BEGIN

DELETE FROM viewer where vid=@vid

END

SET NOCOUNT OFF

END

Here Is An Video Related To Topic In This Blog