Simple Stored Procedure to Perform GridView Insert, Update and Delete

Following stored procedure will help to perform Gridview Insert, Update, Delete operation.

Consider we have a table as Users - table structure as follows :

User_ID is a primary key and set Identity = 'Yes' for auto-generate primary key.

• In Stored procedure below we have a parameter @Function. while using this stored procedure set value of @Function as 'insert' , 'update', 'delete' for respective operation.
• Update and delete operation required primary key field User_ID
• GETDATE()  : to get current DateTime is used in insert operation.

CREATE PROCEDURE [dbo].[SP_User_Modify]

      -- Add the parameters for the stored procedure here

      @Function varchar(20) ,@UID int,@FName varchar(100),@LName      

      varchar(100),@Email varchar(100),@City varchar(100),

      @Phone int

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

      -- Insert statements for procedure here

      -- Insert operation

      IF @Function='insert'

      BEGIN

            insert into dbo.Users            

            (First_Name,Last_Name,Email,City,Phone,Active_Date)

            values

            (@FName,@LName,@Email,@City,@Phone,GETDATE())

      END

      

      -- Update operation

      IF @Function='update'

      BEGIN

            update dbo.Users

            set 

            First_Name=@FName,Last_Name=@LName,Email=@Email,City=@City,

            Phone=@Phone

            WHERE User_ID=@UID;

      END

     

      -- Delete operation

      IF @Function='delete'

      BEGIN

            delete from dbo.Users

            WHERE User_ID=@UID;

      END

END