Stored Procedure Insert Update Delete

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.

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...

Figure2

Execute procedure window will be open.

Figure3
Now for insert we fill the data in required field.
StatementType=insert

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

Figure5
Stored Procedure to Check update
MasterInsertUpdateDelete -> right click select execute stored procedure...
Execute procedure window will be open.
StatementType = 'Update'

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

Figure7
Stored Procedure to Check Delete
MasterInsertUpdateDelete -> right click select execute stored procedure...
Execute procedure window will be open.
StatementType = 'Delete'

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.

Using stored procedure output parameters

 Using stored procedure output parameters

Suppose database Northwind has the following stored procedures for table [Order Details]:

      (1) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.SelectOrderDetails

            AS

            SELECT OrderID, ProductID, Quantity, TStamp FROM [Order Details] WHERE OrderID < 10254

           

           

      (2) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.UpdateOrderDetails

            (

                @OrderID_New int,

                @ProductID_New int,

                @Quantity_New smallint,

                @OrderID_Orig int,

                @ProductID_Orig int,

                @TStamp timestamp OUTPUT

             )

            AS

            UPDATE [Order Details]

                SET OrderID = @OrderID_New, ProductID = @ProductID_New, Quantity = @Quantity_New

                WHERE OrderID = @OrderID_Orig AND ProductID = @ProductID_Orig AND TStamp = @TStamp;

               

            IF @@ROWCOUNT = 1

                SELECT @TStamp = TStamp FROM [Order Details] WHERE OrderID = @OrderID_New and ProductID = @ProductID_New

           

           

      (3) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.InsertOrderDetails

            (

                @OrderID int,

                @ProductID int,

                @Quantity smallint,

                @TStamp timestamp OUTPUT

            )

            AS

               INSERT INTO [Order Details] (OrderID, ProductID, Quantity) VALUES(@OrderID, @ProductID, @Quantity);

           

               SELECT @TStamp = TStamp FROM [Order Details] WHERE OrderID = @OrderID and ProductID = @ProductID

           

           

      (4) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.DeleteOrderDetails

            (

                @OrderID int,

                @ProductID int

  )

            AS

               DELETE FROM [Order Details] WHERE OrderID = @OrderID AND ProductID = @ProductID

The code is listed as follow:

Dim param As OleDbParameter

' SelectCommand

Dim selectCmd As OleDbCommand = mcn.CreateCommand()

selectCmd.CommandText = "SelectOrderDetails"

selectCmd.CommandType = CommandType.StoredProcedure

mda.SelectCommand = selectCmd

' UpdateCommand

Dim updateCmd As OleDbCommand = mcn.CreateCommand()

updateCmd.UpdatedRowSource = UpdateRowSource.OutputParameters

updateCmd.CommandText = "UpdateOrderDetails"

updateCmd.CommandType = CommandType.StoredProcedure

updateCmd.Parameters.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

updateCmd.Parameters.Add("ProductID_New", OleDbType.VarChar, 0, "ProductID")

updateCmd.Parameters.Add("Quantity_New", OleDbType.VarChar, 0, "Quantity")

param = updateCmd.Parameters.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("ProductID_Orig", OleDbType.VarChar, 0, "ProductID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("TimeStamp_Orig", OleDbType.Binary, 8, "TStamp")

param.SourceVersion = DataRowVersion.Original

param.Direction = ParameterDirection.InputOutput

mda.UpdateCommand = updateCmd

' DeleteCommand

Dim deleteCmd As OleDbCommand = mcn.CreateCommand()

deleteCmd.CommandText = "DeleteOrderDetails"

deleteCmd.CommandType = CommandType.StoredProcedure

deleteCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

deleteCmd.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

mda.DeleteCommand = deleteCmd

' InsertCommand

Dim insertCmd As OleDbCommand = mcn.CreateCommand()

insertCmd.UpdatedRowSource = UpdateRowSource.OutputParameters

insertCmd.CommandText = "InsertOrderDetails"

insertCmd.CommandType = CommandType.StoredProcedure

insertCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

insertCmd.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

insertCmd.Parameters.Add("Quantity", OleDbType.VarChar, 0, "Quantity")

param = insertCmd.Parameters.Add("TStamp", OleDbType.Binary, 8, "TStamp")

param.Direction = ParameterDirection.Output

mda.InsertCommand = insertCmd

mda.Fill(mds)

mdg.DataSource = mds

mdg.DataMember = "Table"

Note that in the UPDATE command the time stamp parameter is used for both input and output, and in the INSERT command the time stamp parameter is used for output only.