Stopbyte

How to return a table rows from a Transact-SQL Stored Procedure?

Database Development SQL Server
,
#1

What’s the correct way of returning a table (SELECT * From Table) from within a Stored Procedure in Transact-SQL?

2 Likes
#2

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
1 Like
#3

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.

This syntax:

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.