Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

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!! :-)


Log in to reply