|
Getting the returned set of a MSSQL stored procedure |
Iniciado por dev_JP, jul., 30 2020 7:54 PM - 3 respostas |
| |
| | | |
|
| |
Publicado em julho, 30 2020 - 7:54 PM |
Hi everyone,
I'm having issues getting a return value from a stored procedure on MSSQL. Here's my SQL code:
ALTER PROCEDURE [dbo].[createEmployeeShift] ( @employeeID VARCHAR(38), @equipmentID VARCHAR(38) ) AS
DECLARE @ID uniqueidentifier = NEWID()
INSERT INTO dbo.EmployeeShift VALUES(@ID,@employeeID,GETDATE(),NULL,@equipmentID)
SELECT @ID AS ID
When I run this stored procedure in MSSQL Management Studio, the stored procedure returns the generated ID as normal. However, when I do the same query and I do HReadFirst in WinDev, it says the data source has not been initialized. I'm kinda lost because I've had this kind of stored procedure work great before, but I can't get this one to work. Here's my WinDev code:
sQuery is string dsData is Data Source
sQuery = "EXEC dbo.createEmployeeShift '" + sEmployeeID + "', '" + sEquipmentID + "'"
IF HExecuteSQLQuery(dsData,gctSQLConnection,hQueryWithoutCorrection,sQuery) THEN IF HReadFirst(dsData) THEN gsShiftID = dsData.ID EDT_ShiftStartTime = SysDateTime() END END
Does anyone has an idea on why I'm unable to get the ID from the stored procedure into WinDev? Thanks in advance. |
| |
| |
| | | |
|
| | |
| |
Membro registado 54 mensagems Popularité : +2 (2 votes) |
|
Publicado em julho, 31 2020 - 10:04 AM |
Hi Dev, I never tried with MSSQL, but I use select from store procedure with FirebirdSQL and I use this query definition:
sQuery="SELECT valret1, valret2, valretx FROM mystoredprocedure(param1, param2, paramx)" hexecutesqlquery(...) ...
I hope this help
Bye Andrea |
| |
| |
| | | |
|
| | |
| |
Publicado em julho, 31 2020 - 4:10 PM |
Thanks for the reply Andrea but unfortunately this syntax does not work in MSSQL. However I was able to make it work.
I had to change one line in my stored procedure from this:
INSERT INTO dbo.EmployeeShift VALUES(@ID,@employeeID,GETDATE(),NULL,@equipmentID) to this:
INSERT INTO dbo.EmployeeShift OUTPUT INSERTED.ID VALUES(@ID,@employeeID,GETDATE(),NULL,@equipmentID)
And now it works great. |
| |
| |
| | | |
|
| | |
| |
Publicado em agosto, 04 2020 - 2:47 PM |
Do this way works fine i use MSSQLserver with windev using Native conector
//// Pega Qt Usuarios Conectados no Banco de Dados // get number Of user connected in DATABASE // DEC is name of my connection
MyProcedure is data source
MyProcedure.QtUsuarios = 0
IF not HExecuteSQLQuery(MyProcedure, "Dec", hQueryWithoutCorrection, "Dec_GetQtUsuarios @QtUsuarios out " ) THEN Error(HErrorInfo()) EndProgram() ELSE wsge_QtConexoes = MyProcedure.QtUsuarios END HCancelDeclaration(MyProcedure) // libera da memoria
att Carlos Lages |
| |
| |
| | | |
|
| | | | |
| | |
|