STORE PROCEDURE ADVANCE CONCEPT - PART 2

 

List All Stored Procedures

SELECT name
FROM sys.procedures;

================================================================================

Stored Procedure Calling Another Stored Procedure with Parameters in SQL Server
You cannot create a stored procedure inside another stored procedure, but you can call one stored procedure from another and pass parameters.

Step 1: Create Table

CREATE TABLE Employee
(
    EmpID INT IDENTITY(1,1) PRIMARY KEY,
    EmpName VARCHAR(100),
    Salary DECIMAL(10,2)
);




Step 2: Insert Sample Data
INSERT INTO Employee(EmpName, Salary)
VALUES
('Nilesh Gupta', 50000),
('Rahul Sharma', 45000),
('Priya Patel', 55000);



======================================================================================

Procedure 1: Get Employee by ID
CREATE PROCEDURE sp_GetEmployeeByID
(
    @EmpID INT
)
AS
BEGIN
    SELECT *
    FROM Employee
    WHERE EmpID = @EmpID;
END;
GO

Execute
EXEC sp_GetEmployeeByID 1;
====================================================================================

Procedure 2: Call Procedure 1 and Pass Parameter
CREATE PROCEDURE sp_ShowEmployee
(
    @ID INT
)
AS
BEGIN
    EXEC sp_GetEmployeeByID @EmpID = @ID;
END;
GO

Execute
EXEC sp_ShowEmployee 2;


===================================================================================


Calling Another Stored Procedure Inside IF Condition in SQL Server
You can use an IF...ELSE statement inside a stored procedure and call different stored procedures based on the condition.
Example: Employ

Procedure 1: High Salary Employee
CREATE PROCEDURE sp_HighSalary
    @EmpName VARCHAR(100)
AS
BEGIN
    PRINT @EmpName + ' is a High Salary Employee';
END;
GO



=========================================================>>>>>>>>>>>>>>>>>>>>>.


Procedure 2: Low Salary Employee
CREATE PROCEDURE sp_LowSalary
    @EmpName VARCHAR(100)
AS
BEGIN
    PRINT @EmpName + ' is a Low Salary Employee';
END;
GO


============================================================>>>>>>>>>>>>>>>>>>>>


Procedure 3: Call Procedures Inside IF Condition
CREATE PROCEDURE sp_CheckSalary
(
    @EmpName VARCHAR(100),
    @Salary DECIMAL(10,2)
)
AS
BEGIN
    IF @Salary >= 50000
    BEGIN
        EXEC sp_HighSalary @EmpName;
    END
    ELSE
    BEGIN
        EXEC sp_LowSalary @EmpName;
    END
END;
GO

Execute
EXEC sp_CheckSalary 'Nilesh Gupta', 60000;
EXEC sp_CheckSalary 'Rahul Sharma', 30000;

Output
Nilesh Gupta is a High Salary Employee
Rahul Sharma is a Low Salary Employee


==================================================================================





Example with Number Check
Procedure for Even Number
CREATE PROCEDURE sp_EvenNumber
    @Num INT
AS
BEGIN
    PRINT CAST(@Num AS VARCHAR(10)) + ' is Even';
END;
GO
Procedure for Odd Number
CREATE PROCEDURE sp_OddNumber
    @Num INT
AS
BEGIN
    PRINT CAST(@Num AS VARCHAR(10)) + ' is Odd';
END;
GO
Main Procedure
CREATE PROCEDURE sp_CheckNumber
    @Num INT
AS
BEGIN
    IF @Num % 2 = 0
    BEGIN
        EXEC sp_EvenNumber @Num;
    END
    ELSE
    BEGIN
        EXEC sp_OddNumber @Num;
    END
END;
GO
Execute
EXEC sp_CheckNumber 10;
EXEC sp_CheckNumber 15;
Output
10 is Even
15 is Odd
Syntax:
IF condition
BEGIN
    EXEC ProcedureName parameter;
END
ELSE
BEGIN
    EXEC AnotherProcedure parameter;
END
This is a common technique in SQL Server to execute different stored procedures based on business logic.