Error Handling in SQL UDF

Question

I am creating a UDF in SQL Server. I can not use try-catch block in a user defined function. How can I handle an exception or error in UDF?

CREATE FUNCTION GET_POLICY_NO
(@POLICYID nvarchar(15))
 RETURNS varchar(30)
 AS
 BEGIN
 declare @Return varchar(30)
 select @return = (SELECT POLICY_NO FROM tblPolicy WHERE POLICY_ID = @POLICYID)
return @return
 end

Although tblPolicy is Not exist, it will through error. How can I handle like SP?

better to paste your code here, cannot help without looking into your code... and why cannot you use try and catch block? — LONG
– LONG, Commented Apr 20, 2017 at 12:27
What's the point creating a function which will always fail? — Roger Wolf
– Roger Wolf, Commented Apr 20, 2017 at 13:24
the above code is just for example. it's not my requirement. actually this question was asked in an Interview. "How can I handle errors. in Udf" — Suraj K Mad
– Suraj K Mad, Commented Apr 20, 2017 at 14:00

Community · Accepted Answer · 2017-05-23 12:25:57Z

2

You should check This for some answers

And also I have some suggestions:

Since you cant print or use try catch blocks inside a function , use a stored procedure instead, although I dont think you can catch the "Invalid object name 'tblPolicy' " error. I suggest checking if the table exists and then perform your query.

Hope it helps.

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Apr 20, 2017 at 13:07

Cosmin

1523 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Suraj K Mad Over a year ago

the above code is just for example. it's not my requirement. actually this question was asked in an Interview. "How can I handle errors. in Udf"

Cosmin Over a year ago

@SurajKMad I understand, but it was a little misleading and also there is a lot of info regarding this topic that I`m sure you could've found before asking the question! Cheers

Collectives™ on Stack Overflow

Error Handling in SQL UDF

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related