Hello, I'm sorry for the delay. Stored procedures with IN and OUT parameters can be called easily as documented here: http://www.jooq.org/doc/latest/manual/sql-execution/stored-procedures/
However, I'm not sure what you mean by "getting a result set". Is your procedure supposed to return a cursor (in addition to the OUT parameters)? Can you post the generated GetAddress class? Best Regards, Lukas 2014-03-13 22:05 GMT+01:00 dsr <[email protected]>: > Hi, > > Can you please let me know how do I make a call from Java to fetch > records from a stored procedure that may be similar to the following: > Jooq Codegen does not generate pojos to fetch resultSet. also, generated > routines does not give resultset - routine.execute gives an int type and > not result set > > CREATE PROCEDURE [dbo].[GetAddress]( > @orgId int, > @errorCode int OUTPUT > @numberOFemployees int OUTPUT > ) AS > Begin try > DECLARE @empId int, @msg nvarchar(1000), @error_message nvarchar(1000), > @error_line int, @error_severity int, @error_state int > SET @numberOFEmployees = select employeeCount from organization where > id=@orgId > > if @numberOFEmployees = 0 > begin > ... > ... > .../do some updates to some other tables and then > set @errorCode=4014 // some org specific code end > else begin > ... > ... > more updates to another set of tables > .... > ... > > select emp.id, emp.name, address.number,address.street, state.name, > country.name > from organization join emp on emp.orgId=@orgId > join address on emp.id = address.empId > join state on address.stateId=state.id > join country on country.id=state.countryId > where organization.id =@orgId and emp.status ='active' and > address.type='permanent' > > end > END TRY > BEGIN CATCH > SELECT @msg = 'Exiting GetAddress on error: ' > SELECT @error_message = ERROR_MESSAGE(), @error_line = ERROR_LINE(), > @error_severity = ERROR_SEVERITY(), @error_state = ERROR_STATE() > SET @msg += @error_message > print @msg > > --IF @@Trancount > 0 > --ROLLBACK > INSERT INTO Log(OrgId,Severity,LogDetail,LogSource,LineNumber) > VALUES(@OrgId,@error_severity,@error_message,@msg,@error_line); > > RAISERROR(@error_message,@error_severity,@error_state) WITH NOWAIT > set @errorCode=5999 > END CATCH > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
