How to Run a MS SQL Store Procedure and get the Value from it to a Variable

Hello everyone and Thank you in advance for the Help

I have this stored procedure inside a Microsoft Sequel Database which gets me the Date and Time of the Server.

Name of the Procedure: Get_The_Date_And_Time

Structure of the Procedute

CREATE PROCEDURE dbo.Get_The_Date_And_Time
AS
BEGIN
/* Procedure body */
DECLARE @Date_And_Time as date SELECT Date_And_Time = GETDATE();
END

The result of the Procedure if executed is the following:
Under the Column Name "Get_The_Date_And_Time" The result is the Following

2/8/2021 8:10:19 PM <-------- This is the Date and Time of the Server when the stored procedure is Executed

My question is: How can I run this store procedure from within WinDev or WebDev and put the result in a Variable, for Example varServerDate.

I have tried different things HExecuteProcedure() This does not work with ODBC Connection
I Have tried with HExecuteSQLQuery Get errors
I have tried SQLExec got errors too

Nothing works

I would really appreciate it you can give me a hand on this and perhaps a code Example can be provided since I have no clue where to start.

Thank you so Much

Best regards,
Carlos



Edited 1 time(s). Last edit at 02/09/2021 02:42AM by MPD_For_Life.

did you check tihs help page:
[doc.windev.com]

Regards

Paulo Oliveira

Hi Carlos

Why don't you simply do:

MSSSCnx is connection
--> Define your connection or typically reuse the one defined at application level
SQL is string = "Select GetDate() as MSSSDate"
SQLRes is data source

if hExecuteSQLQuery(SQLRes,MSSSCnx,hQueryDefault+hQueryWithoutCorrection,SQL) then
hReadFirst(SQLRes)
trace(SQLRes.MSSSDate)
end

PS: [docs.microsoft.com]

Cheers,

Peter



Edited 1 time(s). Last edit at 02/09/2021 10:15AM by PeHoBe.

Hi Peter,

This sample code worked perfectly. Thank you so much for all your Help!!!!!

Hope that your code helps others greatly as well.

I added to your code the following to convert the date and time to a better reading outcome so that others have a better understanding.

Trace(DateToString(SQLRes.MSSSDate, "MM/DD/YYYY HH:MM"))


Again
Thank you so much

Best regards,

Carlos

Hi Paulo,

Thank you for the help.

Peter was kind enough to provide me with a piece of code that worked out what I was looking for, however, I never came across this document that you provided me in the link, which looks very good and I will read it so I can have a better understanding for the future.

Thank you both for the help. Much appreciated.

Best regards,

Carlos