SQL CRUD operation using Stored Procedures

  SQL CRUD operation using Stored Procedures  

Step One: create SQL table

Step Two: create stored Procedure

 CREATE PROCEDURE [dbo].[TeacherSubject_CRUD]

      @Action VARCHAR(10)

      ,@TId INT = NULL

      ,@Subject nvarchar(50) = NULL

      ,@Sid int = NULL

AS

BEGIN

      SET NOCOUNT ON;

      --SELECT

      IF @Action = 'SELECT'

      BEGIN

            SELECT *

            FROM TeacherSubject  

      END

      --INSERT

      IF @Action = 'INSERT'

      BEGIN

            INSERT INTO TeacherSubject(Subject,sid)

            VALUES (@Subject, @Sid)

      END

      --UPDATE

      IF @Action = 'UPDATE'

      BEGIN

            UPDATE TeacherSubject

            SET Subject = @Subject, sid = @Sid

            WHERE Tid = @TId

      END

      --DELETE

      IF @Action = 'DELETE'

      BEGIN

            DELETE FROM TeacherSubject

            WHERE Tid = @TId

      END

END

Step Three: Write  Queries and executes

EXEC [dbo].[TeacherSubject_CRUD]@Action = 'INSERT'

                  ,@Subject = 'OOP'

                  ,@Sid = '2'   

EXEC [dbo].[TeacherSubject_CRUD]@Action = 'SELECT'

EXEC [dbo].[TeacherSubject_CRUD]@Action = 'UPDATE'

                  ,@Tid=2,

@Subject = 'Object Oriented Programming'

EXEC [dbo].[TeacherSubject_CRUD]@Action = 'DELETE'

                  ,@Tid=1