PC SOFT

GRUPOS DE DISCUSSÃO PROFISSIONAL
WINDEV, WEBDEV e WINDEV Mobile

Inicio | Mensagens recentes | Conecte-se... |  | Português FR
Inicio → WINDEV 2024 → How to return table from a MS SQL Stored procedure with native access driver?
How to return table from a MS SQL Stored procedure with native access driver?
Iniciado por richard_, jan., 09 2014 11:17 PM - 6 respostas
#1
Publicado em janeiro, 09 2014 - 11:17 PM
Just bought the WD18 MS SQL native access driver but I cant find a way to return the table of results, the stored procedure returns.

I'm not trying to return a single parameter value, but rather pass a number of parameters to the SP, and then get a table of results back.

How do I do this?

Is there a way I can also get the SP table into the analysis so that I can use it elsewhere in other parts of the program I'm writing?

Any pointers?

TIA
#2
Publicado em janeiro, 10 2014 - 9:57 AM
The code listed below will read the result of a stored procedure and populate a table:

=============================================================


TableDeleteAll(TABLE_CM)

dsSQL is Data Source
sCMD is string = ""

sSellTsy_Fill is string = ""
sSellTsy_FillPend is string = ""
sRaise_Fill is string = ""
sRaise_FillPend is string = ""

//Execute Procedure
dsSQL.UserID = gsUserID
dsSQL.Date=gsBatchDate
dsSQL.BatchID=gnBatchID
sCMD="CashManagement_CreateUI @UserID,@Date,@BatchID"
IF HExecuteSQLQuery(dsSQL,"FIXOMS",hQueryWithoutCorrection,sCMD) THEN
HReadFirst(dsSQL)
WHILE NOT HOut(dsSQL)

//Set UI Flags
IF dsSQL.BOD_Cash_Tsy > 0 THEN
IF (dsSQL.BOD_Cash - dsSQL.Rqd_Cash_Filled) >= 0 THEN
sSellTsy_Fill=""
ELSE
sSellTsy_Fill="Y"
END
IF (dsSQL.BOD_Cash - dsSQL.Rqd_Cash_FillPend) >= 0 THEN
sSellTsy_FillPend=""
ELSE
sSellTsy_FillPend="Y"
END
ELSE
sSellTsy_Fill=""
sSellTsy_FillPend=""
END

IF (dsSQL.BOD_Cash_Tradeable - dsSQL.Rqd_Cash_Filled) >= 0 THEN
sRaise_Fill=""
ELSE
sRaise_Fill="Y"
END
IF (dsSQL.BOD_Cash_Tradeable - dsSQL.Rqd_Cash_FillPend) >= 0 THEN
sRaise_FillPend=""
ELSE
sRaise_FillPend="Y"
END


TableAddLine(TABLE_CM,dsSQL.PortfolioID, ...
dsSQL.PortfolioCode, ...
dsSQL.DESCRIPTION, ...
dsSQL.Strategy, ...
dsSQL.Erisa, ...
dsSQL.BOD_Cash, ...
dsSQL.BOD_Cash_Tsy, ...
dsSQL.BOD_Cash_Tradeable, ...
dsSQL.MV_Filled_Buy, ...
dsSQL.MV_Filled_Sell, ...
dsSQL.MV_Pending_Buy, ...
dsSQL.MV_Pending_Sell, ...
dsSQL.Rqd_Cash_Filled, ...
dsSQL.Rqd_Cash_Pending, ...
dsSQL.Rqd_Cash_FillPend, ...
dsSQL.Deficit_Filled, ...
dsSQL.Deficit_FillPend, ...
sSellTsy_Fill, ...
sSellTsy_FillPend, ...
sRaise_Fill, ...
sRaise_FillPend)


HReadNext(dsSQL)
END
ELSE
Error(ErrorInfo(errFullDetails))
END
HCancelDeclaration(dsSQL)

----------------------------------------------------------------------------------------------------------

The code below is the stored procedure being called ... you must have a SELECT statement at the end to return a result set

SELECT * FROM @ResultTable

========================================================================

ALTER PROCEDURE [dbo].[CashManagement_CreateUI] @UserID nvarchar(20), @Date char(8), @BatchID INT

AS
BEGIN

SET NOCOUNT ON;

DECLARE @ResultTable TABLE (PortfolioID INT,
PortfolioCode VARCHAR(32),
DESCRIPTION VARCHAR(100),
Strategy VARCHAR(20),
Erisa CHAR(1),
BOD_Cash DECIMAL(18,5),
BOD_Cash_Tsy DECIMAL(18,5),
BOD_Cash_Tradeable DECIMAL(18,5),
MV_Filled_Buy DECIMAL(18,5),
MV_Filled_Sell DECIMAL(18,5),
MV_Pending_Buy DECIMAL(18,5),
MV_Pending_Sell DECIMAL(18,5),
Rqd_Cash_Filled DECIMAL(18,5),
Rqd_Cash_Pending DECIMAL(18,5),
Rqd_Cash_FillPend DECIMAL(18,5),
Deficit_Filled DECIMAL(18,5),
Deficit_FillPend DECIMAL(18,5)
)

INSERT INTO @ResultTable
( PortfolioID ,
PortfolioCode ,
DESCRIPTION ,
Strategy ,
Erisa ,
BOD_Cash ,
BOD_Cash_Tsy ,
BOD_Cash_Tradeable ,
MV_Filled_Buy ,
MV_Filled_Sell ,
MV_Pending_Buy ,
MV_Pending_Sell ,
Rqd_Cash_Filled ,
Rqd_Cash_Pending ,
Rqd_Cash_FillPend ,
Deficit_Filled ,
Deficit_FillPend
)
SELECT PortfolioID,
PortfolioCode,
Description,
(SELECT Description FROM dbo.C_STRATEGY WHERE (C_STRATEGY.StrategyID = M_ACCOUNT.StrategyID)) as Strategy,
Erisa,
BOD_Cash,
BOD_CashTSY,
BOD_CashTradeable,
dbo.fCashManagement_FilledOrders(dbo.M_ACCOUNT.PortfolioID,@BatchID,@BatchDate,'BY') AS MV_Filled_Buy,
dbo.fCashManagement_FilledOrders(dbo.M_ACCOUNT.PortfolioID,@BatchID,@BatchDate,'SL') AS MV_Filled_Sell,
dbo.fCashManagement_PendingOrders(dbo.M_ACCOUNT.PortfolioID,@BatchDate,'BY') AS MV_Pending_Buy,
dbo.fCashManagement_PendingOrders(dbo.M_ACCOUNT.PortfolioID,@BatchDate,'SL') AS MV_Pending_Sell,
0,
0,
0,
0,
0
FROM M_ACCOUNT
ORDER BY PortfolioCode

UPDATE @ResultTable
SET Rqd_Cash_Filled = (MV_Filled_Buy - MV_Filled_Sell),
Rqd_Cash_Pending = (MV_Pending_Buy - MV_Pending_Sell),
Rqd_Cash_FillPend = (MV_Filled_Buy - MV_Filled_Sell) + (MV_Pending_Buy - MV_Pending_Sell),
Deficit_Filled = CASE WHEN (BOD_Cash_Tradeable-(MV_Filled_Buy - MV_Filled_Sell)) > 0 THEN 0 ELSE (BOD_Cash_Tradeable-(MV_Filled_Buy - MV_Filled_Sell)) END,
Deficit_FillPend = CASE WHEN (BOD_Cash_Tradeable-((MV_Filled_Buy - MV_Filled_Sell) + (MV_Pending_Buy - MV_Pending_Sell))) > 0 THEN 0 ELSE (BOD_Cash_Tradeable-((MV_Filled_Buy - MV_Filled_Sell) + (MV_Pending_Buy - MV_Pending_Sell))) END

SELECT * FROM @ResultTable

END

----------------------------------------------------------------------------------------------------------

Hope this helps ...
#3
Publicado em janeiro, 10 2014 - 2:59 PM
Hi Richard

FYI: your question has been "automatically" copied on the pcsoft US windev forum, and got an answer over there (it seems that the answer was NOT automatically copied) with a code example of what you want to achieve. This is just in case you are not reading ALSO the other forum

Best regards
#4
Publicado em janeiro, 10 2014 - 4:57 PM
Thanks, just checking it out now.
#5
Publicado em janeiro, 15 2014 - 1:12 PM
Hi

It's said "You cannot retrieve a stored procedure that returns records (SELECT ... FROM ...), columns." in windev help of topic "Native SQL Server Access".

My workaround is to store result set in temp table and once the code, eg,
1 exec selectTableResult 'product'
then
2 select * from ##tempTable to retrieve all column values

ie

CREATE PROCEDURE electTableResult
@aTable varchar(4000)
as
exec ('select * into ##tempTable from '+ @aTable)
GO

It's clumsy with 2 steps but it works for me.

Appreciating if someone has a better solution than this, thanks in advance.

HTH

Cheers
King
#6
Membro registado
160 mensagems
Popularité : +18 (22 votes)
Publicado em novembro, 10 2020 - 11:02 AM
Any updates on this issue ?
#7
Publicado em novembro, 10 2020 - 12:17 PM
Kevin this works for me:

MyProc is Data Source
sProc is string

// Run the query and retrieve the result
sProc = "EXEC sp_employee 100, 200"
IF HExecuteSQLQuery(MyProc, myConnection, hQueryWithoutCorrection, sProc) THEN

// Display the result
FOR EACH MyProc
Trace(MyProc.EmployeeName)
END

ELSE
Error()
END

Info("Done!")


and the stored proc looks like this

CREATE PROCEDURE sp_employee(
@nEmp1 int ,
@nEmp2 int )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

SELECT employeeID, employeeName FROM tbl_employees WHERE employeeID BETWEEN @nEmp1 AND @nEmp2
END
GO
→ Voltar para WINDEV 2024