You are currently viewing SQL 1.37 SQL Stored Procedures for SQL Server

SQL 1.37 SQL Stored Procedures for SQL Server

Stored procedures are precompiled SQL statements that are stored in the database itself. They can accept input parameters, perform operations, and return results. They provide a way to encapsulate and execute frequently used SQL queries or operations.

1. Introduction to Stored Procedures:

Stored procedures are a fundamental concept in database programming. They offer several advantages, including improved performance, better security, and easier maintenance.

2. Creating a Stored Procedure:

Let’s start by creating a simple stored procedure that retrieves data from a table.

CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT * FROM Employees;
END;

Explanation:

  • CREATE PROCEDURE: This SQL statement is used to create a new stored procedure.
  • GetEmployees: Name of the stored procedure.
  • AS: Marks the beginning of the procedure definition.
  • BEGIN and END: These keywords enclose the body of the procedure.
  • SELECT * FROM Employees: This is the SQL query that retrieves all records from the Employees table.

3. Executing a Stored Procedure:

Once created, you can execute the stored procedure using the EXEC keyword.

EXEC GetEmployees;

Explanation:

  • EXEC: Short for execute, this keyword is used to invoke a stored procedure.
  • GetEmployees: Name of the stored procedure to be executed.

4. Passing Parameters to a Stored Procedure:

Stored procedures can accept input parameters, allowing for dynamic behavior. Let’s modify our previous example to accept a parameter.

CREATE PROCEDURE GetEmployeeById
    @EmployeeId INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeId = @EmployeeId;
END;

Explanation:

  • @EmployeeId INT: Defines an input parameter named @EmployeeId of type INT.
  • WHERE EmployeeId = @EmployeeId: Filters the result based on the passed EmployeeId parameter.

5. Executing a Parameterized Stored Procedure:

To execute a stored procedure with parameters, specify the parameter values.

EXEC GetEmployeeById @EmployeeId = 123;

Explanation:

  • @EmployeeId = 123: Passes the value 123 to the @EmployeeId parameter.

6. Returning Data from Stored Procedures:

Stored procedures can return result sets or scalar values. Let’s modify our previous example to return a count of employees instead of the entire record set.

CREATE PROCEDURE GetEmployeeCount
AS
BEGIN
    SELECT COUNT(*) AS EmployeeCount FROM Employees;
END;

Explanation:

  • COUNT(*) AS EmployeeCount: Returns the count of records in the Employees table with the alias EmployeeCount.

7. Executing a Stored Procedure with a Return Value:

To execute a stored procedure that returns a result set, you can use the same EXEC keyword.

EXEC GetEmployeeCount;

Explanation:

  • This will return the count of employees as a result set.

Conclusion

In this tutorial, you’ve learned the basics of SQL Stored Procedures for SQL Server. You now understand how to create, execute, and parameterize stored procedures, as well as how to return data from them. Stored procedures are powerful tools for managing and manipulating data within a SQL Server database. Practice these concepts to become proficient in using stored procedures in your database applications.

Leave a Reply