June 27, 2008 at 6:11 am
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.
June 27, 2008 at 7:13 am
Afraid you can't use TRY - CATCH in a T-SQL UDF.
June 27, 2008 at 7:22 am
Thank u ,
How to capture the error's in User Defined Functions in SQL-Server.
Regards
Swamy.
June 27, 2008 at 12:23 pm
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
June 28, 2008 at 12:41 am
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.
June 30, 2008 at 8:14 am
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
February 11, 2011 at 4:08 pm
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
April 28, 2011 at 6:49 am
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...
April 28, 2011 at 8:53 am
is there a purpose for putting a TRY CATCH into a function that is merely a simple SELECT statement?
What error would be expected?
April 28, 2011 at 9:24 am
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... 😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply