Stored Procedures

[Rocks]

Hi all, I have not used Stored procs before and have now been given some to modify and aslo add error handling in them..........

All I need to do is to return a error msg in VB from the stored proc indicating that an error has occurred.

I am only using select statements in the Stored procs therefore no rollback is required..

any help / advice would be great.

heres an example of a proc that i am using:
BEGIN

SELECT a.CompId, a.Company, a.RankId AS Rank, a.FiscalId AS LastActual,
a.FyEnd, b.Period, b.EPS AS EPS, c.PTPNETINC AS PTP,
b.CFPS AS CFPS, b.DPS AS DPS, b.BVPS AS BVPS, d.SHRFUND AS ShrHldFnd,
d.NETDEBT AS NetDebt, b.NETDBTCSH AS NetDbtCsh, e.EBITDA AS EBITDA,
h.Sectors, f.CrossCd5, f.CrossCd6, f.CrossCd9, g.Lastname
FROM eqt_Company a, eqt_StockRatio b, eqt_FinanceData c,
eqt_BalanceSheet d, eqt_ProfitLoss e, eqt_CrossCodes f,
eqt_Employee g, eqt_Sectors h
WHERE a.CntryId = 'UK' AND
a.CompId = b.CompId AND
b.CompId *= c.CompId AND
b.CompId *= d.CompId AND
b.CompId *= e.CompId AND
a.CompId *= f.CrossCd1 gfhgf
a.EmpId *= g.EmpId AND
a.SectId *= h.SectId AND
b.Period = '1997' AND c.Period = '1997'
AND d.Period = '1997' AND e.Period = '1997'
ORDER BY h.Sectors, a.Company, b.Period ASC
End

I am also using ones with variables being passed.

Thanks all in advance

[Orpheus]

SP language and Error handling can be dierent depending on the database engine. What engine do you use (Oracle/SQL/Informix.........)

[Rocks]

Thanks for replying I use a Sybase DB and access it using ISQL.......


Thanks for looking into this

[Orpheus]

Sorry, never used Sybase and do not know the syntaxes.

Here is an Oracle example though that you may be able to start from ...

procedure fndrow
(
pDptName IN E2KDPTS.DPTNAME%TYPE,
pFran_ID OUT E2KDPTS.FRAN_ID%TYPE
)
is
begin

select E2KDPTS.FRAN_ID into pFran_ID from E2KDPTS
where E2KDPTS.DPTNAME = pDptName;

if SQL%NOTFOUND
then
pFran_ID := 0;
end if;

EXCEPTION
when OTHERS
then
pFran_ID := -1;

end fndrow;

I can then look at the returned value in the second parameter to determine if the requested row exists.

[Michael Kizer]

It depends what kind of error you are thinking this select will generate (typically zero rows returned). So to test that, just check the row count in VB after you execute the stored procedure.