Create Stored Procedure in SQL-Server.

Stored Procedure in SQL-Server

Stored procedure is nothing but the simple compiled sql statement.

Stored Procedure is a simple SQL Query in Specific format in SQL Server and we can call them from in our program or application instead of writing queries inside our application code.
Stored Procedure are pre-compiled sql statements which is stored in a SQL Server database.
Stored procedure is a simple SQL Statement, which is pre-compiled in sql.

Check video tutorial of create table and stored procedure in sql server 2008

Here, are  some sql step create stored procedure in sql server  management studio.

Step 1 –Open SQL Server Management Studio 2008

Step 2 –  Connect Server with Server Name

 Step 3 – Explore Server Name tab and select database option

Step 4 – Right click on database and Select New Database option

Step 5 – Write New database name at database name field

Step 6 – Click  OK to create database successfully.

Step 7 – Create New Table in Database

Step 7 – Go to Programmability –> Stored Procedure –> Right Click –> New Stored Procedure.

For create stored procedure, first create new database and create new table in database.

we will learn how to create stored procedure in sql server management studio with an example.

First Create Database –> Create Table — > Programmability — > Stored Procedures.

Here we have Database name = Example.

After creating new database create new table in database. We have here created new table with name “USERMST”.

For Create New Table Database –> Right Click on Tables –> New Table

Here we create table USERMST with three columns ID , Name and Surname. The first column ID is integer and we assign it Primary key and auto increment. The other two columns name and surname are nvarchar() datatype.

Create New Table in sql server
Create New Table in sql server

Now, Select Programmability — > Stored Procedure — > New Stored Procedure

create stored procedure in sql server
create stored procedure in sql server

Select the programmability option and right click on and select New Stored procedure.

create stored procedure in sql server
create stored procedure in sql server

 

give a name for new Select stored procedure CREATE PROCEDURE [Name of the procedure]

here is the new stored procedure for SELECT statement.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[USERMST_SELECT]
AS
BEGIN

SELECT * FROM USERMST

END
create stored procedure in sql server
create stored procedure in sql server

 

give a name for new Insert stored procedure CREATE PROCEDURE [Name of the procedure]

here is the new stored procedure for INSERT statement.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[USERMST_INSERT]

@NAME AS NVARCHAR(256),
@SURNAME AS NVARCHAR(256)

AS
BEGIN

INSERT INTO USERMST VALUES(@NAME, @SURNAME)

END
create stored procedure in sql server
create insert stored procedure in sql server

 

give a name for new Delete stored procedure CREATE PROCEDURE [Name of the procedure]

here is the new stored procedure for DELETE statement.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[USERMST_DELETE]

@ID AS INT

AS
BEGIN

DELETE FROM USERMST  WHERE ID=@ID

END

create stored procedure in sql server

 

give a name for new Update stored procedure CREATE PROCEDURE [Name of the procedure]

here is the new stored procedure for UPDATE statement.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[USERMST_UPDATE]

@ID AS INT,
@NAME AS NVARCHAR(256),
@SURNAME AS NVARCHAR(256)

AS
BEGIN

UPDATE USERMST SET NAME=@NAME, SURNAME=@SURNAME WHERE ID=@ID

END
create stored procedure in sql server
create stored procedure in sql server

 

list of all stored procedure which we have already created above.

create stored procedure in sql server
create stored procedure in sql server

 

Leave a Reply

Your email address will not be published. Required fields are marked *