|
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 |
| |
| | | |
|
| |
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 |
| |
| |
| | | |
|
| | |
| |
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(, @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 ... |
| |
| |
| | | |
|
| | |
| |
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 |
| |
| |
| | | |
|
| | |
| |
Publicado em janeiro, 10 2014 - 4:57 PM |
Thanks, just checking it out now. |
| |
| |
| | | |
|
| | |
| |
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 |
| |
| |
| | | |
|
| | |
| |
Membro registado 160 mensagems Popularité : +18 (22 votes) |
|
Publicado em novembro, 10 2020 - 11:02 AM |
Any updates on this issue ? |
| |
| |
| | | |
|
| | |
| |
Publicado em novembro, 10 2020 - 12:17 PM |
Kevin this works for me:
MyProc is Data Source sProc is string
sProc = "EXEC sp_employee 100, 200" IF HExecuteSQLQuery(MyProc, myConnection, hQueryWithoutCorrection, sProc) THEN 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;
SELECT employeeID, employeeName FROM tbl_employees WHERE employeeID BETWEEN @nEmp1 AND @nEmp2 END GO |
| |
| |
| | | |
|
| | | | |
| | |
|