What’s the correct way of returning a table (SELECT * From Table) from within a Stored Procedure in Transact-SQL?
You’ve already answered it, All you have to do is implement your table query the same way you do all the time
SELECT * FROM YourTable, within an optional
BEGIN..END block inside a
CREATE PROCEDURE block, something like this:
CREATE PROCEDURE [dbo].[GetCategories] AS BEGIN SELECT * FROM CategoriesTable END
By the way the
BEGIN..END block is only “required” if you have a multi-lines script within the block. Otherwise, something like this without the block would work just fine:
CREATE PROCEDURE [dbo].[GetCategories] AS SELECT * FROM CategoriesTable
I think you DO NOT need to use the RETURN keyword to return a spesific value from your stored procedure. Unless you are willing to return a status code.
CREATE PROCEDURE [dbo].[MyStoredProcedure] AS RETURN SELECT * FROM MyTable
Is still valid! but not recommended, since when calling the stored procedure, it will return a status code in all cases, alongside the table data rows. Main point: Return keyword is not required, though you can use it like this:
CREATE PROCEDURE [dbo].[MyStoredProcedure] AS SELECT * FROM MyTable RETURN (0)
According to this article, (0) status code means the procedure call has been successful. You can use other status codes to return an error.