[sql] How to return a table from a Stored Procedure?

It is very simple question.

I am trying to return a table from a stored procedure, like

select * from emp where id=@id

I want to return this query result as a table. I have to do this through a stored procedure.

In SQL Server 2008 you can use

http://www.sommarskog.se/share_data.html#tableparam

or else simple and same as common execution

CREATE PROCEDURE OrderSummary @MaxQuantity INT OUTPUT AS

SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity)
FROM Orders AS Ord
     JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID)
GROUP BY Ord.EmployeeID
ORDER BY Ord.EmployeeID

SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]

RETURN (SELECT SUM(Quantity) FROM [Order Details])
GO

I hopes its help to you

It's VERY important to include:

 SET NOCOUNT ON;

into SP, In First line, if you do INSERT in SP, the END SELECT can't return values.

THEN, in vb60 you can:

SET RS = CN.EXECUTE(SQL)

OR:

RS.OPEN CN, RS, SQL