Microsoft SQL Server Stored Procedures Stored Procedure with If...Else and Insert Into operation
Example
Create example table Employee:
CREATE TABLE Employee
(
Id INT,
EmpName VARCHAR(25),
EmpGender VARCHAR(6),
EmpDeptId INT
)
Creates stored procedure that checks whether the values passed in stored procedure are not null or non empty and perform insert operation in Employee table.
CREATE PROCEDURE spSetEmployeeDetails
(
@ID int,
@Name VARCHAR(25),
@Gender VARCHAR(6),
@DeptId INT
)
AS
BEGIN
IF (
(@ID IS NOT NULL AND LEN(@ID) !=0)
AND (@Name IS NOT NULL AND LEN(@Name) !=0)
AND (@Gender IS NOT NULL AND LEN(@Gender) !=0)
AND (@DeptId IS NOT NULL AND LEN(@DeptId) !=0)
)
BEGIN
INSERT INTO Employee
(
Id,
EmpName,
EmpGender,
EmpDeptId
)
VALUES
(
@ID,
@Name,
@Gender,
@DeptId
)
END
ELSE
PRINT 'Incorrect Parameters'
END
GO
Execute the stored procedure
DECLARE @ID INT,
@Name VARCHAR(25),
@Gender VARCHAR(6),
@DeptId INT
EXECUTE spSetEmployeeDetails
@ID = 1,
@Name = 'Subin Nepal',
@Gender = 'Male',
@DeptId = 182666
