Error Handling Function Sql
Contents |
up Recent PostsRecent Posts Popular TopicsPopular Topics Home Search http://www.sqlservercentral.com/Forums/Topic524905-149-1.aspx Members Calendar Who's On Home » SQL Server 2005 » SQL http://dataeducation.com/blog/exception-handling-in-t-sql-using-error-why-bother Server 2005 General Discussion » How to use the try catch block in Function? How to use the try catch block in Function? Rate Topic Display Mode Topic Options Author Message Swamy MagamSwamy Magam Posted Friday, June 27, 2008 6:11 AM SSC Journeyman error handling Group: General Forum Members Last Login: Friday, January 15, 2010 5:07 AM Points: 82, Visits: 129 HiI am trying write a function in that function i am use the try catch block, when i am complie that function it is the throwing complie error.my functioncreate function GetEmployeeid(@Deptno varchar(Max))returns varchar(Max)asBeginDeclare @employeeid Varchar(Max)BEGIN TRYSelect top 1 Employeeid sql error handling from temployee where deptno=@deptnoEnd Try BEGIN Catch print 'geting errror'End Catchreturn(@employeeid )EndComplie Error Is:Msg 443, Level 16, State 14, Procedure GetEmployeeid, Line 7Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function.Msg 444, Level 16, State 2, Procedure GetEmployeeid, Line 8Select statements included within a function cannot return data to a client.Msg 443, Level 16, State 14, Procedure GetEmployeeid, Line 9Invalid use of side-effecting or time-dependent operator in 'END TRY' within a function.Msg 443, Level 16, State 14, Procedure GetEmployeeid, Line 10Invalid use of side-effecting or time-dependent operator in 'BEGIN CATCH' within a function.Msg 443, Level 16, State 14, Procedure GetEmployeeid, Line 11Invalid use of side-effecting or time-dependent operator in 'PRINT' within a function.Msg 443, Level 16, State 14, Procedure GetEmployeeid, Line 12Invalid use of side-effecting or time-dependent operator in 'END CATCH' within a function.Thanks In Advance Please help me. RegardsSwamy. Post #524905 Andrew Gothard-467944Andrew Gothard-467944 Posted Friday, June 27, 2008 7:13 AM SSC-Enthusiastic Group: General Forum Members Last
is Part 6 of a series of blog posts by Data Education founder Adam Machanic on errors and exceptions in Microsoft SQL Server. In Part 1, Adam gave a basic explanation of the difference between errors and exceptions. In Part 2, he examined types of exceptions. In Part 3, Adam broke down the parts of the dreaded error message. In Part 4, he took a steely-eyed look at the RAISERROR function. In the last post, Part 5, Adam discussed monitoring exception events with traces. Here, he offers his arguments for why to handle exceptions in T-SQL using @@ERROR. Understanding when, why, and how SQL Server throws exceptions is great, but the real goal is to actually do something when an exception occurs. Exception handling refers to the ability to catch an exception when it occurs, rather than simply letting it bubble up to the next level of scope. This is a capability that was not possible in T-SQL until SQL Server 2005, and its addition to the language added some interesting development possibilities. Exception handling in T-SQL should be thought of as no different from exception handling in any other language. A generally accepted programming practice is to handle exceptions at the lowest possible scope, in order to keep them from interacting with higher levels of the application. If an exception can be caught at a lower level and dealt with there, higher-level modules will not require special code to handle the exception and therefore can concentrate on whatever their purpose is. This means that every routine in the application becomes simpler, more maintainable, and therefore quite possibly more robust. Put another way, exceptions should be encapsulated as much as possible; knowledge of the internal exceptions of other modules is yet another form of coupling. Keep in mind that encapsulation of exceptions is really something that must be handled on a case-by-case basis. But the basic rule is: If you can “fix” the exception one way or another without letting the caller ever know it even occurred, that is probably a good place to encapsulate. Exception “Handling” Using @@ERROR Versions of SQL Server prior to SQL Server 2005 did not have true exception-handling capabilities. Any exception that occurred would be passed back to the caller, regardless of any action taken by the code of the stored procedure or query in whic