PC SOFT

PROFESSIONAL NEWSGROUPS
WINDEVWEBDEV and WINDEV Mobile

Home → WINDEV 24 → Getting the returned set of a MSSQL stored procedure
Getting the returned set of a MSSQL stored procedure
Started by dev_JP, Jul., 30 2020 7:54 PM - 2 replies
Posted on July, 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.
Registered member
6 messages
Posted on July, 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
Posted on July, 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.