Solved Stored Procedures bindValue QSql::Out Parameter and table result
-
I am trying to use Stored Procedures with return an out parameters.
QSqlQuery query; query.prepare("EXECUTE ?=testOutParam ?"); query.bindValue(0, 0, QSql::Out) query.bindValue(1, 0, QSql::Out)
My stored procedure looks like this:
ALTER PROCEDURE [dbo].[testOutParam] -- Add the parameters for the stored procedure here @iOut int = NULL OUTPUT AS BEGIN DECLARE @return int -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT @iOut = 101; SELECT @return = 102; return @return; END
Everything works fine, when I only have Out and Return parameters. I get the values 101 for the output parameter and 102 for the return parameter. But when I would like to select values from a table I only get the table values and the parameters return 0:
ALTER PROCEDURE [dbo].[testOutParam] -- Add the parameters for the stored procedure here @iOut int = NULL OUTPUT AS BEGIN DECLARE @return int -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT @iOut = 101; SELECT @return = 102; SELECT * FROM tTest; return @return; END
When I EXECUTE the stored procedure in SQL Server Management Studio:
DECLARE @i1 INT; DECLARE @i2 INT; EXECUTE @i1=testOutParam @i2 OUTPUT; PRINT @i1; PRINT @i2;
I get back the table values and 101 and 102 for the parameters.
I there a restriction in QT? Or am I doing something wrong?
-
I found the error. Per definition you have to read all the values of a result set, before you can read return and output parameters. I changed my code reading all results of my result table before reading the parameters, and now it works!! :-)