# Catching return value from stored procedure

**URL:** <https://forum.inductiveautomation.com/t/catching-return-value-from-stored-procedure/2828>\
**Category:** Archived Topics\
**Created:** [March 31, 2011, 9:18am UTC](https://forum.inductiveautomation.com/t/catching-return-value-from-stored-procedure/2828 "2011-03-31T09:18:14Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![bluefrog](https://avatars.discourse-cdn.com/v4/letter/b/a6a055/32.png) [@bluefrog](https://forum.inductiveautomation.com/u/bluefrog)\
**Post date:** [March 31, 2011, 9:18am UTC](https://forum.inductiveautomation.com/t/catching-return-value-from-stored-procedure/2828/1 "2011-03-31T09:18:14Z")

</div>

Hi,  
I have a stored procedure in MS SQL 2005 that return a result. I tried to get the return value with runUpdateQueryGetKey, runPrepStmtGetKey and runPrepStmt. But I couldn’t get the result. How can I get it? Let say, I have a table with two columns. One is ID and another one is code. so that stored procedure will be like this:

```auto
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[testsp]
@CODE AS VARCHAR(4)
AS
SET NOCOUNT ON;
INSERT INTO TEST (CODE)
VALUES
(@CODE);
IF @@ERROR = 0
	RETURN @@IDENTITY
ELSE 
	RETURN(-1)
SET NOCOUNT OFF;
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
```

In this case, I want to know that the return value is -1 or not to execute the another stored procedure. Please help me. Thanks. ![:mrgreen:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/d23659b203d2d24d394be983a52adb2fa6fe23d4.gif "Mr. Green") ![:mrgreen:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/d23659b203d2d24d394be983a52adb2fa6fe23d4.gif "Mr. Green") ![:mrgreen:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/d23659b203d2d24d394be983a52adb2fa6fe23d4.gif "Mr. Green")

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [March 31, 2011, 2:55pm UTC](https://forum.inductiveautomation.com/t/catching-return-value-from-stored-procedure/2828/2 "2011-03-31T14:55:45Z")

</div>

Those functions are for running updates, inserts or delete queries. You should use runScalarQuery or runQuery to get the result.

---

<div class="post-metadata">

**Author:** ![bluefrog](https://avatars.discourse-cdn.com/v4/letter/b/a6a055/32.png) [@bluefrog](https://forum.inductiveautomation.com/u/bluefrog)\
**Post date:** [April 1, 2011, 4:17am UTC](https://forum.inductiveautomation.com/t/catching-return-value-from-stored-procedure/2828/3 "2011-04-01T04:17:00Z")

</div>

I got the same error when I tried with runScalarQuery and runQuery. But when I checked in the table, the data is inserted successfully. I attached the error message and the code I used to run the stored procedure.

> [@](#):
>
> ValueError: SQL error for "EXECUTE testsp 'BBBB'": The executeQuery method must return a result set.

`query = "EXECUTE testsp 'BBBB'"
result = fpmi.db.runQuery(query);`

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [April 1, 2011, 3:56pm UTC](https://forum.inductiveautomation.com/t/catching-return-value-from-stored-procedure/2828/4 "2011-04-01T15:56:01Z")

</div>

What does running the stored procedure return in SQL Server Management Studio? In the stored procedure you can try:`IF @@ERROR = 0
   SELECT @@IDENTITY
ELSE 
   SELECT -1`instead of RETURN.

---

<div class="post-metadata">

**Author:** ![bluefrog](https://avatars.discourse-cdn.com/v4/letter/b/a6a055/32.png) [@bluefrog](https://forum.inductiveautomation.com/u/bluefrog)\
**Post date:** [April 4, 2011, 8:45am UTC](https://forum.inductiveautomation.com/t/catching-return-value-from-stored-procedure/2828/5 "2011-04-04T08:45:01Z")

</div>

Thanks for your idea. It really helped. I got it.
