Skip to Content
0
Nov 09, 2011 at 02:11 PM

User Defined Function (Part 2)

22 Views

Hi,

István Korös has finally solved my problem on User Defined Function.

See

My objective was to write a simple UDF to represent the following formula which Gordon Du gave me:

(DATEADD (s, -1,  
         DATEADD (mm, (DATEDIFF (m,0,@refdtzz ) + @mthnumber),  0)))

However, the solution proposed by István, although it works perfectly, leaves me scratching my head. Let's see why?

The only difference between my solution and that proposed by István is that István enclosed the UDF name between .

I wonder how this can make the vital difference between a (simple query!) that works well and one that gets blocked.

I tried several combinations of and am in for a few surprises, listed below. Can anybody explain what's going on?

-

-


Solution proposed by István:

CREATE FUNCTION [dbo].[udf_EndOfMonth]
and executed as:
select  [dbo].udf_EndOfMonth (@refdt1 , 1)
Remarks: works perfectly

-

-


If I execute the UDF with exaclly the same name as in CREATE, it does not work

CREATE FUNCTION [dbo].[udf_EndOfMonth]
and executed as:
select  [dbo].[udf_EndOfMonth] (@refdt1 , 1)
Error Msg: Must specify table to select from

-

-


If I don't put the , the function is created, but the execution of the calling query returns error

CREATE FUNCTION dbo.udf_EndOfMonth
Remarks : Creation OK

and executed as:
select  dbo.udf_EndOfMonth (@refdt1 , 1)
Error Msg: Must specify table to select from

-

-


It seems that the only combination that works is that provided by István.

This is surely a simple UDF.

I don't know what to do if I attack a more complicated UDF.

Grateful if anybody could help light my way.

Thanks

Leon Lai