How can a SQL Server stored procedure return a table?

A SQL Server stored procedure can return a table in several ways. Here are the most common methods:

1. Using a SELECT Statement:

- The most straightforward way is to use a SELECT statement within the stored procedure. The result set of this SELECT statement is what the stored procedure returns as a table. This is the most common and recommended approach for returning data.

- Example:

CREATE PROCEDURE GetEmployees
AS
BEGIN
  SELECT EmployeeID, FirstName, LastName, Department
  FROM Employees;
END;

- When you execute this stored procedure, it will return a table with the columns EmployeeID, FirstName, LastName, and Department.

2. Using a Temporary Table:

- You can create a temporary table (either local or global) within the stored procedure, populate it with data, and then select from it. This is useful when you need to perform intermediate operations before returning the final result.

- Example (Local Temporary Table):

CREATE PROCEDURE GetFilteredEmployees
@Department VARCHAR(50)
AS
BEGIN
  CREATE TABLE #TempEmployees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
  );

  INSERT INTO #TempEmployees
  SELECT EmployeeID, FirstName, LastName, Department
  FROM Employees
  WHERE Department = @Department;

  SELECT FROM #TempEmployees;

  DROP TABLE #TempEmployees;
END;

- This procedure creates a local temporary table #TempEmployees, inserts filtered data into it, and then returns the table. The temporary table is dropped at the end of the procedure.

3. Using a Table-Valued Function (TVF):

- While not technically a stored procedure, a Table-Valued Function (TVF) is another way to return a table. TVFs can be used in the FROM clause of a SELECT statement, making them very flexible.

- Example (Inline TVF):

CREATE FUNCTION GetEmployeesByDepartment (@Department VARCHAR(50))
RETURNS TABLE
AS
RETURN
(
  SELECT EmployeeID, FirstName, LastName, Department
  FROM Employees
  WHERE Department = @Department
);

- You can then use this function in a query like this: SELECT FROM GetEmployeesByDepartment('Sales');

4. Using a Common Table Expression (CTE):

- Although CTEs are not directly returned, they can be used within a stored procedure to structure the data before returning it with a SELECT statement.

- Example:

CREATE PROCEDURE GetEmployeesWithRank
AS
BEGIN
  WITH RankedEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Department,
    RANK() OVER (ORDER BY LastName) AS Rank
    FROM Employees
  )
  SELECT FROM RankedEmployees;
END;

- This procedure uses a CTE to add a rank to the employees before returning the result set.

In summary, the most common and recommended way to return a table from a SQL Server stored procedure is by using a SELECT statement. Temporary tables and Table-Valued Functions offer more flexibility for complex scenarios. Choose the method that best suits your specific needs and complexity.

Learn more about this