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.
- More info: Fix getstringbetween error
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.

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
- Don’t miss: What is java -djava.security.policy?
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!!!
AUTHOR BIO
On my daily job, I am a software engineer, programmer & computer technician. My passion is assembling PC hardware, studying Operating System and all things related to computers technology. I also love to make short films for YouTube as a producer. More at about me…
Leave a Reply