How to sp_executesql Stored Procedure Return Value

When you are working with MySQL, you definitely ever find any issues that hinder you from working. Of course, it is such a common situation which is  absolutely experienced by many people who are getting in touch with SQL programs.

Unfortunately, all issues of MySQL totally drain your mind and  energy, don’t they? One of the most issues occuring on MySQL program is sp_executesql that is not working when you want to return value through SQL’s Stored Procedure. Then, you may wonder how to get return value through Stored Procedure.

How to sp_executesql Stored Procedure Return Value

When you use MySQL program to get the return value, of course you will be getting in touch with the sp_executesql. Indeed, The sp_executesql is a built-in stored procedure in SQL server to execute the constructed SQL statement or batches dynamically. In executing the constructed SQL batches, you will use a technique to overcome the different issues in SQL program.

How to sp_executesql Stored Procedure Return Value1

Then, when you want to return the value  through Stored Procedure, you definitely will use the sp_executesql to execute it. Unfortunately, many users sometimes fail when they are finding the return value through sp_executesql.  Then, how to fix it?

Of course, the issue that happens to everyone related to the sp_executesql which is not working to return the value through Stored Procedure probably will be different. It means that the situation that you experience with other people may be different even though the case is totally the same.

Here, we have two problem situations experienced by people who try using the sp_executesql on Stored Procedure to find the return value. We definitely can do those cases from some forums. So, here they are:

Problem 1: with sp_executesql in Getting The Return Value

A member on StackOverflow.com forum asked the answer for the problem in EXEC sp_executesql – capture RETURN value and OUTPUT value from stored procedure call. He had a simple stored procedure which populates an Output parameter and also Return a value as follow:

CREATE PROCEDURE sp_test

(

    @param_out INT OUTPUT

)

AS BEGIN

    SELECT @param_out = 9

    RETURN 2

END

He was confused this procedure using sp_executesql and capture both of these values as follow:

DECLARE @ret INT, @param_out INT

EXEC SP_EXECUTESQL N’EXEC @ret = sp_test’,

    N’@ret INT OUTPUT, @param_out INT OUTPUT’,

    @ret OUTPUT,

    @param_out OUTPUT

 SELECT @ret, @param_out

Then, it complains that  @param_out was not supplied:

Procedure or function ‘sp_test’ expects parameter ‘@param_out’, which was not supplied.

Then, he asked why are you wrapping the call in sp_executesql anyway? and is sp_executesql needed for that?

The Solution:

At least, there are two answers which replied to him. Here they are:

In this case, to pass both values as Output parameters to sp_executesql is really needed as well:

DECLARE @ret int,

        @param_out int;

EXEC sp_executesql N’EXEC @ret = sp_test @param_out OUT;’,

                   N’@ret INT OUTPUT, @param_out INT OUTPUT’,

                   @ret OUTPUT,

                   @param_out OUTPUT;

SELECT @ret,

       @param_out;

Why should you use this? However, there is nothing dynamic about the SQL. So that’s why using sp_executesql is really recommended to return the value of a Stored Procedure.

Apart from that solution, you can also do the process as follow:

DECLARE @ret       INT,

        @param_out INT

DECLARE @procname SYSNAME = ‘sp_test’

 EXEC @ret = @procname

  @param_out OUTPUT

However, The EXEC grammar accepts @module_name_var. It means that you do not need sp_executesql as the stored procedure is dynamic.

Problem 2: with sp_executesql in Getting The Return Value

Besides, a member of DBA.Stackexchange forum asked the same questions, he really needed to call a stored procedure in another database and check the return value.

The simplified version of what he had got as follow;

DECLARE @errorLogId INT

DECLARE @otherDbName NVARCHAR(MAX) = ‘Foo’ — Get the database name from somewhere

DECLARE @sql NVARCHAR(MAX)

SET @sql = @otherDbName + ‘.dbo.SomeProc’

— I want @errorLogId to be the return value from SomeProc, this didn’t work.   

EXECUTE @errorLogId = sp_executesql @sql

IF @errorLogId <> 0

   RAISERROR(‘SomeProc failed, ErrorLogId = %d.’, 16, 1, @errorLogId) WITH SETERROR

Then, if an error happens in SomeProc,  it can be caught and an entry is written to an error table. Then, SomeProc returns the error table ID of the record which was  written. If not, SomeProc returns 0.

He definitely guessed that the return value would be passed through  sp_executesql, but he also thought that the return value is definitely from sp_executesql not SomeProc. Is there a solution to access the return value from @otherDbName.dbo.SomeProc in the local database?

The Solution:

Way 1: To solve this problem, the users definitely have to use the Output parameter described as follow:

Given this dummy stored procedure:

CREATE PROCEDURE sp_test AS

RETURN 2

Then, the code will give you the result:

declare @ret int

exec sp_executesql N’exec @ret = sp_test’, N’@ret int OUTPUT’, @ret = @ret OUTPUT

select @ret as result

result

———–

2

(1 row(s) affected)

Way 2: To solve this problem, you can also create Proc with two regular parameters and 1 table value type parameter which returns an int. Then, you can also use sp_Executesql to execute generated execution statements dynamically.

DROP PROC IF EXISTS tmp.EraseMeIfYouCan

DROP TYPE IF EXISTS tmp.EraseMeTo

GO

CREATE TYPE tmp.EraseMeTo AS TABLE(Id INT)

GO

CREATE PROC tmp.EraseMeIfYouCan

    @Debug TINYINT ,                                — 0 No debug, 1 debug + print, 2 = print only

    @P2 AS tmp.EraseMeTo READONLY,

    @DebugRetVal INT = 0                           

AS

SELECT *

FROM   @P2

    RETURN @DebugRetVal

GO

SET NOCOUNT ON

DECLARE @P INT = 1

DECLARE @I INT = 0

DECLARE @R INT

DECLARE @EM2 AS tmp.EraseMeTo

WHILE @I < 10

BEGIN

    SET @I += 1

    INSERT INTO @EM2

      (

        Id

      )

    VALUES

      (

        @I

      )

END

PRINT ‘Start sp-exec’

EXEC SP_ExecuteSQL N’Exec @R = tmp.EraseMeIfYouCan @Debug, @P2, @DebugRetVal’,

     N’@R Int Output, @P2 As tmp.EraseMeTo ReadOnly, @Debug TinyInt, @DebugRetVal Int’,

     @R = @R OUTPUT,

     @P2 = @EM2,

     @Debug = 0,

     @DebugRetVal = 3

SELECT @R\

Way 3: To solve this problem, you can also execute the procedure and view the results.

IF the stored-procedure name is for spCHGAssetGet.

The output parameters are for @CustomerID, @AssetID.

The input parameters are for  @ IDTicketItem, @IDExtWarranty.

The result will be:

DECLARE @CustomerID NVARCHAR(100)

DECLARE @AssetID    NVARCHAR(100)

EXEC spCHGAssetGet

@IDTicketItem = 4764,

@IDExtWarranty = NULL,

@CustomerID = @CustomerID OUT,

@AssetID = @AssetID OUT

SELECT @CustomerID, @AssetID

Well, those are the solutions that probably can solve your problem with sp_executesql in Getting The Return Value through SQL’s Stored Procedure. If all solutions above cannot solve your problem, don’t hesitate to take the other solutions from another source. Good Luck!!!

Leave a Reply

Your email address will not be published. Required fields are marked *