Select rows from Two (Multiple) Tables in a single Stored Procedure in SQL Server

It is possible.

Database

Northwind

Stored Procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Get_Customers_And_Employees
AS
BEGIN
    SET NOCOUNT ON;
    SELECT TOP 10 ContactName, City, Country FROM Customers;
    SELECT TOP 10 (FirstName + ' ' + LastName) EmployeeName, City, Country FROM Employees
END
GO

Output