Hi folks, I am typing this from my phone so I can't include code. What I am looking to do. udf that returns an int Udf receives 3 params Loads a table variable with data selected based on the Params. Once the table variable is loaded then it does a if exists
select against the table Variable and returns a 1 or 0 based on the result. What is happening is that the mssql returns an error saying the @mytablevar must Be declared. I have not found a way to resolve this yet.
I did a test with the function below and it worked normally:
CREATE FUNCTION [dbo].[GetTestTableVar]
(
@Par1 int, @Par2 int, @Par3 int
)
RETURNS int
AS
BEGIN
DECLARE @TableVar table (DateCol date);
insert into @TableVar
select DATEADD(month, @Par1, GETDATE())
union all
select DATEADD(month, @Par2, GETDATE())
union all
select DATEADD(month, @Par3, GETDATE()) ;
DECLARE @Return int;
if Exists (select 1 from @TableVar where DATEDIFF(month, GETDATE(), DateCol) > 3)
SET @RETURN = 1;
else
SET @Return = 0;
RETURN @Return;
END
GO
I will give it a try on Monday, funny enough that your example is pretty close to how i made my function, maybe its some small little detail that I am just not seeing at the moment but I will let you know on monday.
Also I am on SQL Server 2005, who knows maybe there is some particularity,
I did some checking and testing and finally got it working, your sample helped clarify that it does work on my end and I just needed to do a little more digging.
aabruzzese
Contributor
2806 Points
759 Posts
udf returns int
Apr 27, 2012 09:24 PM|LINK
gimimex
Participant
1052 Points
157 Posts
Re: udf returns int
Apr 27, 2012 11:05 PM|LINK
Hi,
I did a test with the function below and it worked normally:
CREATE FUNCTION [dbo].[GetTestTableVar] ( @Par1 int, @Par2 int, @Par3 int ) RETURNS int AS BEGIN DECLARE @TableVar table (DateCol date); insert into @TableVar select DATEADD(month, @Par1, GETDATE()) union all select DATEADD(month, @Par2, GETDATE()) union all select DATEADD(month, @Par3, GETDATE()) ; DECLARE @Return int; if Exists (select 1 from @TableVar where DATEDIFF(month, GETDATE(), DateCol) > 3) SET @RETURN = 1; else SET @Return = 0; RETURN @Return; END GOHope this helps.
aabruzzese
Contributor
2806 Points
759 Posts
Re: udf returns int
Apr 28, 2012 01:14 AM|LINK
Hello gimimex,
I will give it a try on Monday, funny enough that your example is pretty close to how i made my function, maybe its some small little detail that I am just not seeing at the moment but I will let you know on monday.
Also I am on SQL Server 2005, who knows maybe there is some particularity,
aabruzzese
Contributor
2806 Points
759 Posts
Re: udf returns int
Apr 30, 2012 01:04 PM|LINK
I did some checking and testing and finally got it working, your sample helped clarify that it does work on my end and I just needed to do a little more digging.
:)