Functions as arguments to Functions?

Question

Functions as arguments to Functions?

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
September 30, 2005 at 12:25 pm

#165641

I have a user defined function. It expects a four-character string with the current year. If I run Select * from dbo.Pledge_Match('2005') it works fine.
If I run
Select * from dbo.Pledge_Match(cast(datepart(yyyy,getdate()) as char(4)))
it tells me: Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
I ran "Select cast(datepart(yyyy,getdate()) as char(4))" and got back '2005'. Why doesn't using that as the function argument work?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Dunno really. Could depend on how the function is written or how you are actually calling it, or that there are some typos somewhere else around the code..? Unfortunately we can't repro the behaviour without the actual function at hand.

You could try assigning your datepart to a variable and see if that works better...?

declare @year char(4)

set @year = cast(datepart(yyyy,getdate()) as char(4))

select * from dbo.Pledge_Match(@year)

..perhaps it will work better...?

/Kenneth

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

The code inside dbo.Pledge_Match is essentially:

Create Function dbo.Pledge_Match(@campaign_year char(4)) AS
BEGIN
Select {fields from both functions}
FROM dbo.Pledge_This(@campaign_year) Pledge_This
LEFT OUTER JOIN
dbo.Pledge_Last(@campaign_year) Pledge_Last
END

dbo.Pledge_This and dbo.Pledge_Last grab the same fields from two tables for This year and Last year. The code isn't terribly comlicated.

I was trying to specify the original code in a view so declaring a variable and setting it to a value wouldn't work...

I even took the original Select statement I posted from the View Designer in EM and ran it in QA. Same error message. As far as I can tell all the parens are balanced, etc. I'm stumped.

David Burrows SSC Guru Points: 65143 More actions · Answer 3

As parameters to functions can only be parameters or constants, the cast statement is neither.

Do as Kenneth suggests or change the input to the function to int

Create Function dbo.Pledge_Match(@campaign_year int) ...

and use

Select * from dbo.Pledge_Match(datepart(yyyy,getdate()))

Far away is close at hand in the images of elsewhere.
Anon.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

No such luck:

Select * from dbo.RPT_Pledge_Match(datepart(yyyy,getdate())) -- Ran in QA

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near '('.

dbo.RPT_Pledge_Match as Char(4):

CREATE FUNCTION [dbo].[RPT_PLEDGE_MATCH] (@campaign_year char(4))

RETURNS TABLE AS

RETURN

(

SELECT TOP 100 PERCENT

RPT_PLEDGE_THIS.EMPLOYEE_ID,

RPT_PLEDGE_THIS.HIG_BGS1_CODE,

RPT_PLEDGE_THIS.HIG_BGS2_CODE,

RPT_PLEDGE_THIS.HIG_BGS_CODE,

RPT_PLEDGE_THIS.LAST_NAME,

RPT_PLEDGE_THIS.FIRST_NAME,

RPT_PLEDGE_THIS.Pledge_Type,

RPT_PLEDGE_THIS.City,

RPT_PLEDGE_THIS.Executive,

RPT_PLEDGE_THIS.Emp_or_Ret,

RPT_PLEDGE_THIS.Amount_This,

RPT_PLEDGE_LAST.Amount_Last,

Pledge_Match =

CASE

WHEN coalesce(RPT_Pledge_This.Amount_This,0) >= coalesce(RPT_Pledge_Last.Amount_Last,0)

THEN coalesce(RPT_Pledge_This.Amount_This,0) - coalesce(RPT_Pledge_Last.Amount_Last,0)

ELSE 0

END

FROM dbo.RPT_PLEDGE_THIS(@campaign_year) RPT_PLEDGE_THIS LEFT OUTER JOIN dbo.RPT_PLEDGE_LAST(@campaign_year) RPT_PLEDGE_LAST

ON RPT_PLEDGE_THIS.EMPLOYEE_ID = RPT_PLEDGE_LAST.EMPLOYEE_ID

)

dbo.RPT_Pledge_Match as Char(4):

CREATE FUNCTION [dbo].[RPT_PLEDGE_MATCH] (@cyear int) RETURNS TABLE AS RETURN (
DECLARE @campaign_year char(4)
SET @campaign_year=cast(@cyear as char(4))
SELECT TOP 100 PERCENT RPT_PLEDGE_THIS.EMPLOYEE_ID, RPT_PLEDGE_THIS.HIG_BGS1_CODE, RPT_PLEDGE_THIS.HIG_BGS2_CODE, RPT_PLEDGE_THIS.HIG_BGS_CODE, RPT_PLEDGE_THIS.LAST_NAME, RPT_PLEDGE_THIS.FIRST_NAME, RPT_PLEDGE_THIS.Pledge_Type, RPT_PLEDGE_THIS.City, RPT_PLEDGE_THIS.Executive, RPT_PLEDGE_THIS.Emp_or_Ret, RPT_PLEDGE_THIS.Amount_This, RPT_PLEDGE_LAST.Amount_Last, Pledge_Match = CASE WHEN coalesce(RPT_Pledge_This.Amount_This,0) >= coalesce(RPT_Pledge_Last.Amount_Last,0) THEN coalesce(RPT_Pledge_This.Amount_This,0) - coalesce(RPT_Pledge_Last.Amount_Last,0) ELSE 0 END FROM  dbo.RPT_PLEDGE_THIS(@campaign_year) RPT_PLEDGE_THIS LEFT OUTER JOIN  dbo.RPT_PLEDGE_LAST(@campaign_year) RPT_PLEDGE_LAST ON RPT_PLEDGE_THIS.EMPLOYEE_ID = RPT_PLEDGE_LAST.EMPLOYEE_ID )

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

If I were to hazard a guess it would be that the culprit is getdate() itself since it is a non-deterministic function...don't think this is "allowed"...

**ASCII stupid question, get a stupid ANSI !!!**

David Burrows SSC Guru Points: 65143 More actions · Answer 6

Not exactly true.

You can use GETDATE() as a parameter to a Scalar Function

but not as a paramenter to an Inline Table-valued Function

Extract from BOL:

TABLE

Specifies that the return value of the table-valued function is a table. Only constants and @local_variables can be passed to table-valued functions.

So, Kenneth's solution is the one to use

Far away is close at hand in the images of elsewhere.
Anon.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 7

Tried in QA:

Declare @dt datetime
set @dt=getdate()
select * from dbo.RPT_Pledge_Match(datepart(yyyy,@dt))

Same error...

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near '('.

Then did:

Declare @dt int
set @dt=datepart(yyyy,getdate())
select * from dbo.RPT_Pledge_Match(@dt)

works fine...

I've used getdate() as a parameter to a UDF that needs a date in the past. I know you can't use getdate() within the UDF because it's non-deterministic, but I'm pretty sure you can use it as a datetime parameter since it is evaluated before the function is called. It has something to do with the overall format of the call, but I can't figure out what.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 8

The devil is in the details...

I've used getdate() as a parameter, but only in scalar functions now that I think about it.

Learn something new every day.

Thanks David.

sushila SSC-Dedicated Points: 35293 More actions · Answer 9

In my defense...I should've qualified my statement with "not allowed in this particular instance"...but you said it so much better David with your explanation of scalar & table-valued - wish I had your "way with words"..

The bottom line - outside of declaring a variable and passing the stored value, there doesn't seem to be the kind of solution that the original post was looking for...ie - passing the function as argument...

**ASCII stupid question, get a stupid ANSI !!!**

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 10

The bottom line - outside of declaring a variable and passing the stored value, there doesn't seem to be the kind of solution that the original post was looking for...ie - passing the function as argument...

Which is why I changed it from a view to a Stored Procedure about a day after starting this thread. I needed to get it working and that seemed to be the only way: declare the variable, set it, then select against the function. I was pursuing it to ensure I understood why...

I guess I should have followed my own advice to our junior developers: RTFM!!

Thanks all!!

John Diss SSC Enthusiast Points: 156 More actions · Answer 11

A common solution to this problem is to have a view the contains GetDate() and query the view in your function...

HTH jd