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.
Now, Select Programmability — > Stored Procedure — > New Stored Procedure
Select the programmability option and right click on and select New Stored procedure.
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
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
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
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
list of all stored procedure which we have already created above.