soft deletion query in sql server stored procedure code example
Example 1: how to delete a procedure in sql
DROP PROCEDURE <stored procedure name>;
GO
Example 2: how to create a delete stored procedure in sql
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