January 3, 2017 at 2:10 pm
I have a function where I am trying to create syntax or data error conditions
where I can create it? I have declared the variables. where to set the condition to fail this function.
ALTER FUNCTION [dbo].[GetEmp]
(@ID varchar(20))
RETURNS @SE TABLE
(
Num varchar(100)
)
AS
BEGIN
DECLARE
@Num varchar(100),
@Error INT,
@IsError CHAR(1)
If @ID like 'DEM%'
BEGIN
--------process start
END
ELSE
------Another process start
END
-- Return the result of the function
Insert into @SE
Select @Num
Return
END
January 3, 2017 at 2:19 pm
Stay away of multi-statement table-valued functions. They're great performance killers.
January 3, 2017 at 2:33 pm
Here we are returning 4 columns as I mentioned only one @NUM.
There is another reason to use table valued function.
Can you please modify the above one where to use THrow?
January 3, 2017 at 3:04 pm
mcfarlandparkway (1/3/2017)
Here we are returning 4 columns as I mentioned only one @NUM.There is another reason to use table valued function.
Can you please modify the above one where to use THrow?
Surely you can do that. I don't understand the required logic, you do.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 3, 2017 at 3:16 pm
Both THROW and RAISERROR cause side effects and user-defined functions cannot cause side effects, so you cannot use a THROW or RAISERROR in a UDF.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 3, 2017 at 6:14 pm
drew.allen (1/3/2017)
Both THROW and RAISERROR cause side effects and user-defined functions cannot cause side effects, so you cannot use a THROW or RAISERROR in a UDF.Drew
Shows how often I write UDFs! Thanks, Drew.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 3, 2017 at 7:38 pm
mcfarlandparkway (1/3/2017)
Here we are returning 4 columns as I mentioned only one @NUM.There is another reason to use table valued function.
Switching gears a little... If your statement is in response to Luis' comment, "Stay away of multi-statement table-valued functions." then there may be a little confusion. There are two kinds of table valued functions (TVF) in SQL Server: multi-statement (mTVF) and inline (iTVF). In short: mTVF's are awful and iTVFs are wonderful (provided they are written correctly). Learning how to replace mTVFs and Scalar functions with iTVFs is one of those SQL "Black Arts" tricks that will enable you to write better, faster code than 99% of the people that write SQL Server queries for a living.
The code you posted is for an mTVF. You could, however, easily re-write it as an iTVF. Here's what you posted (cleaned up for brevity):
CREATE FUNCTION dbo.GetEmp(@ID varchar(20))
RETURNS @SE TABLE(Num varchar(100))
AS
BEGIN
DECLARE @Num varchar(100), @Error INT, @IsError CHAR(1);
If @ID like 'DEM%'
BEGIN
--------process start
END
ELSE
------Another process start
END;
Insert into @SE
Select @Num;
Return;
END
GO
This could be re-written like so:
CREATE FUNCTION dbo.GetEmp(@ID varchar(20))
RETURNS TABLE AS RETURN
WITH
yourVariables AS
(
SELECT Num = --<some logic>,
Error = --<some other logic>
--...
)
SELECT Num = --<your logic for when @ID LIKE 'DEM%'>
FROM yourVariables
WHERE @ID LIKE 'DEM%'
UNION ALL
SELECT Num = --<your logic for when @ID LIKE 'DEM%'>
FROM yourVariables
WHERE @ID NOT LIKE 'DEM%';
GO
-- Itzik Ben-Gan 2001
January 4, 2017 at 8:10 am
Then how I have to create error in the function?
how this function will fail? I am trying to create this eror condition inside the function.And this func is calling from another procedure when this fails it has to go to ERROR. I want to check weather it is going into that loop or not?
SET @IsError = 'Y'
--SET @ProcName = 'Update'
GOTO ERROR
January 4, 2017 at 8:19 am
mcfarlandparkway (1/4/2017)
Then how I have to create error in the function?how this function will fail? I am trying to create this eror condition inside the function.And this func is calling from another procedure when this fails it has to go to ERROR. I want to check weather it is going into that loop or not?
SET @IsError = 'Y'
--SET @ProcName = 'Update'
GOTO ERROR
You can't do error handling such as that inside your function, as the others have said.
Do you know what could be the cause of your function to error (for example, conversion error)? If it's due to likely dirty data, try using TRY_CONVERT (Transact-SQL) or TRY_CAST (Transact-SQL).
If it could be something you can't anticipate so easily, your error handling need to be outside your function, inside the calling procedure.
Do you know what type of error you are likely to be expecting?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply