Reader Level:
Article

Select, Insert, Update, Delete Using Stored Procedure in SQL Server 2008

By Rohatash Kumar on Nov 16, 2011
Here, we will see how to create select, insert, update, delete statements using stored procedure.

Here, we will see how to create select, insert, update, delete statements using stored procedure. Let's take a look at a practical example. We create a table.

Creating Table

CREATE TABLE employee(

    id          INTEGER NOT NULL PRIMARY KEY,

    first_name  VARCHAR(10),

    last_name   VARCHAR(10),

    salary      DECIMAL(10,2),

    city        VARCHAR(20),   

 )

Now insert some values in the table and using select statement to select a table.

 INSERT INTO employee VALUES (2, 'Monu',  'Rathor',4789,'Agra');

 GO

 INSERT INTO employee VALUES (4, 'Rahul' ,  'Saxena',   5567,'London');

 GO

 INSERT INTO employee VALUES (5, 'prabhat',  'kumar',  4467,'Bombay');

 go

 INSERT INTO employee VALUES (6, 'ramu',  'kksingh',  3456, 'jk');

 go

 select * from employee

Table looks like this.

employeetable.gif

Figure 1

Stored procedure for Select, insert, update, delete

Here, we create a stored procedure for select,insert,update,delete statements to select the data from the table.

Alter PROCEDURE MasterInsertUpdateDelete

(

    @id         INTEGER,

    @first_name  VARCHAR(10),

    @last_name   VARCHAR(10),

    @salary      DECIMAL(10,2),

    @city        VARCHAR(20), 

    @StatementType nvarchar(20) = ''

)

 

AS

BEGIN

IF @StatementType = 'Insert'

BEGIN

insert into employee (id,first_name,last_name,salary,city) values( @id, @first_name,  @last_name,  @salary, @city)   

END

 

IF @StatementType = 'Select'

BEGIN

select * from employee

END 

 

IF @StatementType = 'Update'

BEGIN

UPDATE employee SET

            First_name =  @first_name, last_name = @last_name, salary = @salary,

            city = @city

      WHERE id = @id

END

 

else IF @StatementType = 'Delete'

BEGIN

DELETE FROM employee WHERE id = @id

END

end

Now press F5 to execute the stored procedure.

Now open object explorer and select storeprocedure MasterInsertUpdateDelete.

Stored Procedure to Check Insert

StatementType = 'Insert'

MasterInsertUpdateDelete -> right click select execute stored procedure...

employeetable1.gif

Figure2

Execute procedure window will be open.

employeetable3.gif

Figure3

Now for insert we fill the data in required field.

StatementType=insert

employeetable4.gif

Figure4

Click on the ok Button. and check in the employee table with following inserted data.

employeetable5.gif

Figure5

Stored Procedure to Check update

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

StatementType = 'Update'

employeetable6.gif

Figure6

Click on the ok Button. and check in the employee table with following updated data where id is 7.

employeetable7.gif

Figure7

Stored Procedure to Check Delete

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

StatementType = 'Delete'

employeetable8.gif

Figure8

we delete record from table which has id=2

Click on the ok Button. and check in the employee table with following deleted data where id is 2.

employeetable9.gif

Figure9

Article Extensions
Contents added by Abhishek A on Jul 23, 2012
Very nice SP
Rohatash Kumar

Normal 0 false false false EN-AU X-NONE X-NONE I am a Microsoft .NET software Developer and author and C# Corner MVP. I hold Masters degree i... Read more

COMMENT USING