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

[theroper]

What kind of errors are you going to trap?

[Rocks]

Hi,

I want to trap any Null values,
Null recordsets,
and any other error msgs that may be thrown out of the DB and take then all into VB to dispay to the user.


Hope that helps

[theroper]

I would suggest that you do all the error checking within the VB code.

if the StoredProcedure bombs out this will raise an error within VB.

if the recordset is empty you can check for this and return an error.

if you dont want to do this then use the @@ERROR constant (within sql7) not sure what the constant is for other platforms