SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to use the try catch block in Function?


How to use the try catch block in Function?

Author
Message
Swamy Magam
Swamy Magam
Right there with Babe
Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)

Group: General Forum Members
Points: 760 Visits: 129
Hi
I 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 function

create function GetEmployeeid(@Deptno varchar(Max))
returns varchar(Max)
as
Begin

Declare @employeeid Varchar(Max)
BEGIN TRY
Select top 1 Employeeid from temployee where deptno=@deptno
End Try
BEGIN Catch
print 'geting errror'
End Catch
return(@employeeid )
End

Complie Error Is:
Msg 443, Level 16, State 14, Procedure GetEmployeeid, Line 7
Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function.
Msg 444, Level 16, State 2, Procedure GetEmployeeid, Line 8
Select statements included within a function cannot return data to a client.
Msg 443, Level 16, State 14, Procedure GetEmployeeid, Line 9
Invalid use of side-effecting or time-dependent operator in 'END TRY' within a function.
Msg 443, Level 16, State 14, Procedure GetEmployeeid, Line 10
Invalid use of side-effecting or time-dependent operator in 'BEGIN CATCH' within a function.
Msg 443, Level 16, State 14, Procedure GetEmployeeid, Line 11
Invalid use of side-effecting or time-dependent operator in 'PRINT' within a function.
Msg 443, Level 16, State 14, Procedure GetEmployeeid, Line 12
Invalid use of side-effecting or time-dependent operator in 'END CATCH' within a function.


Thanks In Advance
Please help me.
Regards
Swamy.
Andrew Gothard-467944
Andrew Gothard-467944
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3340 Visits: 2357
Afraid you can't use TRY - CATCH in a T-SQL UDF.
Swamy Magam
Swamy Magam
Right there with Babe
Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)

Group: General Forum Members
Points: 760 Visits: 129
Thank u ,
How to capture the error's in User Defined Functions in SQL-Server.
Regards
Swamy.
GSquared
GSquared
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222790 Visits: 9733
You have to capture them in the calling procedure or code. Currently, there's no real error handling in T-SQL UDFs.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Swamy Magam
Swamy Magam
Right there with Babe
Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)Right there with Babe (760 reputation)

Group: General Forum Members
Points: 760 Visits: 129
GSquared (6/27/2008)
You have to capture them in the calling procedure or code. Currently, there's no real error handling in T-SQL UDFs.


How to capture error's by using stored procedure or code in function ?
Please help me step by step. Thanks In advance.
Regards
Swamy.
GSquared
GSquared
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222790 Visits: 9733
However you are calling the function, you put error-handling in there.

For example, if you have a function that select the first employee ID in a department, you might have a proc that selects all departments, and then uses the function. You would use Try/Catch in that proc.

I really can't give you step-by-step instructions or actual code, without knowing how you are using the function.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Gopi S
Gopi S
SSC Eights!
SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)SSC Eights! (934 reputation)

Group: General Forum Members
Points: 934 Visits: 332
I too tried the same and end up with error. To quickly start with, i am trying to create CLR function to handle this and bring the same functionality and currently i am working on that.

May be some experts can advise on this.

Thx
Gopi
mp3killa 9680
mp3killa 9680
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 54
My SQL skills are limited and I probably don't know any better but not being able to make use of TRY CATCH in a UDF is simply retarded. IMO anyway.... Perhaps MS can put this into a future SP of SQL or something... or not... whatever...
davidandrews13
davidandrews13
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5643 Visits: 5153
is there a purpose for putting a TRY CATCH into a function that is merely a simple SELECT statement?
What error would be expected?
mp3killa 9680
mp3killa 9680
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 54

SELECT dbo.LongitudeFix('23°10''354"')

ALTER FUNCTION LongitudeFix ( @input VARCHAR(80) )
RETURNS FLOAT
AS
BEGIN
IF @input IS NULL
BEGIN
--Just return NULL if input string is NULL
RETURN 0.00
END

--Character variable declarations
DECLARE @output FLOAT
DECLARE @first FLOAT
DECLARE @middle FLOAT
DECLARE @last FLOAT
--Integer variable declarations
SET @output = 0.00
SET @input = REPLACE(@input, 'S', '')
SET @input = REPLACE(@input, 'E', '')
SET @input = REPLACE(@input, 'N', '')
SET @input = REPLACE(@input, 'W', '')
SET @input = REPLACE(@input, '-', '')
SET @input = REPLACE(@input, ' ', '')
--Variable/Constant initializations
IF ( LEN(@input) > 8 )
BEGIN
SET @first = CAST(LEFT(@input, 2) AS FLOAT)
SET @middle = CAST(SUBSTRING(@input, 4, 2) AS FLOAT) / 60
SET @last = CAST(REPLACE(SUBSTRING(@input, 7, 4), '"', '') AS FLOAT)
SET @output = @first + @middle + @last / 3600 * 100000
/ 100000
END
RETURN ROUND(@output,6)
END

GO



I'd love to just have


BEGIN TRY
SELECT @output = CAST(LEFT(@input, 2) AS FLOAT)+
CAST(SUBSTRING(@input, 4, 2) AS FLOAT) / 60+
CAST(REPLACE(SUBSTRING(@input, 7, 4), '"', '') AS FLOAT) / 3600 * 100000 / 100000
END TRY
CATCH TRY
RETURN NULL
END TRY



Much simpler IMO... No check for evvvvvvverrrrrrrrry single possible type of entry. Just does it fit the format, yes/no if yes then churn out answer if no, then nullify... Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search