Catching return value from stored procedure

Archived Topics
1

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:

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: :mrgreen: :mrgreen:

2

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

3

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.

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

4

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 -1instead of RETURN.

5

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