CREATE SELECT,INSERT,UPDATE,DELETE IN ONE STORED PROCEDURE COMMAND

CREATE SELECT,INSERT,UPDATE,DELETE IN ONE STORED PROCEDURE COMMAND




Create PROCEDURE MasterInsertUpdateDelete ( @id INTEGER, @first_name VARCHAR(10), @last_name VARCHAR(10), @salary DECIMAL(10,2), @city VARCHAR(20), @StatementType nvarchar(20) = '' ) AS BEGIN BEGIN TRAN 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 IF @@ERROR <> 0 ROLLBACK TRAN ELSE COMMIT TRAN END