how to create a delete stored procedure in sql
snippet in sql

how to create a delete stored procedure in sql

user5053

Stored Procedure for Select, Insert, Update, Delete
 
Here, we create a stored procedure with SELECT, INSERT, UPDATE, and DELETE SQL statements. The SELECT SQL statement is used to fetch rows from a database table. The INSERT statement is used to add new rows to a table. The UPDATE statement is used to edit and update the values of an existing record. The DELETE statement is used to delete records from a database table. The following SQL stored procedure is used insert, update, delete, and select rows from a table, depending on the statement type parameter. 
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