jwcolby
jwcolby at colbyconsulting.com
Fri Nov 27 09:24:15 CST 2009
Shamil, I am looking for guidance in this area myself. As you know by now, the particular application I am working on is not CRUD based objects but rather specific sets of SPs to perform specific operations in my business. To this point, I have used VBA and a function for executing a stored procedure provided by Charlotte. I could never figure out how to use that function to get at returned values so I have to this point not used that functionality at all. Now I expect to be able to do so, and as a result I will be expanding my processing to include such error handling. It will be a long time before I am correcting anyone on this stuff! John W. Colby www.ColbyConsulting.com Shamil Salakhetdinov wrote: > Hi John -- > > This kind of "excessive" execution result/error information is useful in my > opinion for non-CRUD SPs. > But for CRUD ones one can just use SPs return value: > > - if it's zero or positive - all is OK and returned value is @@ROWCOUNT; > - if it's negative - then this is @@ERROR (if actual @@ERROR value will be > positive just return it as negative number). > > If some kinds of runtime errors happen in SPs then they can be trapped by > calling C# code try/catch block and actual error message can be obtained > from System.Data.SqlClient.SqlException. > > Well, above is what I'd call a "lightweight" error handling approach - and > it works well in many real life systems AFAIK... > > But for long running (set of) SPs one can use "heavyweight" error handling > approach using T-SQLs > > TRY...CATCH > > features as described in MS SQL books online (local link): > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/248df62a-7334-4bca-8262- > 235a28f4b07f.htm > > I could be missing something. > Please correct me where I'm wrong, please write about your approach to T-SQL > (and calling C# code) error handling. > > Thank you. > > -- > Shamil