SQL SERVER – Stored Procedure and RETURN Keyword

Earlier today while working on Comprehensive Database Performance Health Check, we landed up a very interesting situation where we had to debug a stored procedure. The stored procedure was very long and we had to debug that one step at a time. Let us learn about Stored Procedure and RETURN Keyword in today’s blog post.

There are multiple ways to debug the Stored Procedure in SQL Server but the most popular way to debug the stored procedure is just run only part of the stored procedure time and keep on checking various progress and output. During the conversation, SQL Server Expert Sapandeep Singh suggested that we should use the RETURN statement to only run the part of the stored procedure instead of commenting out or using GO TO command. Here is how he suggested that we can use RETURN Keyword.

Let us assume that our stored procedure is very big and we only want to run the first part of the stored procedure and immediately stop running it further. Here is how we can do it.

CREATE PROCEDURE MySP
AS
BEGIN
SELECT 1
RETURN
SELECT 2
END
GO

Now run the stored procedure.

EXEC MySP

You will realize as soon as the first resultset completes, the stored procedure stops running. This is because SQL Server stored procedure stops executing the query as soon as it encounters RETURN statement. Well, this was a very quick and useful tip. If you have any such tip, please send me a note and I will publish it with due credit to you.

Here is a few related blog post on this topic:

• SQL SERVER – Different Methods to Know Parameters of Stored Procedure
• SQL SERVER – Creating Temporary and Global Temporary Stored Procedures
• SQL SERVER – Natively Compiled Stored Procedures and Location of Compiler
• List All the Stored Procedure Modified in Last Few Days – Interview Question of the Week #070

Reference: Pinal Dave (https://blog.sqlauthority.com)