Executing Procedures

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

Stored procedures are precompiled SQL statements that can be executed multiple times with different parameters. Here's a summary of executing stored procedures and an example:

Basic Syntax

-----------------

To execute a stored procedure, use the following syntax:

sql
EXEC procedure_name [(@parameter1, @parameter2, ...)];

• procedure_name is the name of the stored procedure to be executed.
• @parameter1, @parameter2, etc. are input parameters passed to the procedure.

Example

-----------

Let's create a simple stored procedure called GetEmployeeDetails that retrieves employee information based on an employee ID:

sql
CREATE PROCEDURE GetEmployeeDetails
    @EmpID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmpID = @EmpID;
END;
GO

To execute the stored procedure, use the following query:

sql
EXEC GetEmployeeDetails @EmpID = 123;

This will return all columns for the employee with ID 123.

Parameter Passing

---------------------

Stored procedures can take input parameters that are passed to them using the @ symbol. The data type of each parameter must match the data type of the corresponding variable in the procedure.

Out Parameters

------------------

Some stored procedures may also return output parameters, which can be used to store values returned by the procedure. To declare an out parameter, use the OUTPUT keyword:

sql
CREATE PROCEDURE GetEmployeeDetails
    @EmpID INT,
    @Name VARCHAR(50) OUTPUT
AS
BEGIN
    SELECT * FROM Employees WHERE EmpID = @EmpID;
    SET @Name = (SELECT Name FROM Employees WHERE EmpID = @EmpID);
END;
GO

To execute the stored procedure with an out parameter, use the following query:

sql
DECLARE @Name VARCHAR(50);
EXEC GetEmployeeDetails @EmpID = 123, @Name OUTPUT;
PRINT @Name;  -- prints the employee name

Best Practices

-------------------

• Always use sp_executesql instead of EXEC when executing a stored procedure that takes input parameters.
• Use parameter sniffing to avoid SQL Server's optimizer from caching query plans based on parameter values.
• Avoid using out parameters unless absolutely necessary, as they can lead to performance issues.

I hope this summary and example help you understand how to execute stored procedures in SQL Server!