|
|
Posted by Hugo Kornelis on 07/21/07 20:57
On Sat, 21 Jul 2007 00:14:51 +0200, Gert-Jan Strik wrote:
>Hugo Kornelis wrote:
>>
>> On Fri, 20 Jul 2007 09:39:44 -0700, Carsten wrote:
>>
>> >Hello Folks,
>> >
>> >I encountered a problem with SQL server 2000 and UDFs.
>> >
>> >I have a scalar UDF and a table UDF where I would like the scalar UDF
>> >to provide the argument for the table UDF like in:
>> >
>> >SELECT
>> > *
>> >FROM
>> > transaction_t
>> >WHERE
>> > trxn_gu_id in (
>> > select get_trxns_for_quarter(get_current_quarter( GetDate() ) )
>> > )
>> >
>> >'get_current_quarter' returns an integer which is a GUID in a table
>> >containing business quarter definitions, like start date, end date.
>> >'get_current_quarter' is a scalar UDF.
>> >'get_trxns_for_quarter' will then get all transctions that fall into
>> >that quarter and return their GUID's in a table.
>> >'get_trxns_for_quarter' is a table UDF.
>>
>> Hi Carsten,
>>
>> You need to select from a table-valued function. And you need to
>> schema-qualify UDF's.
>
>Hugo, you are right (as usual). The trick is in schemabinding the scalar
>UDF (and addressing the UDF with its 2-part name).
>
>Do you have any documentation or BOL reference that specifies that
>schemabinding is required? Because I don't understand why this would be
>relevant.
Hi Gert-Jan,
I have no such reference, and there is none - because schemabinding is
not required. Here's a repro:
DROP FUNCTION dbo.get_current_quarter;
go
CREATE FUNCTION dbo.get_current_quarter (@dt datetime)
RETURNS datetime
AS
BEGIN;
DECLARE @BegQ datetime;
SET @BegQ = DATEADD(quarter, DATEDIFF(quarter, 0, @dt), 0);
RETURN (@BegQ);
END;
go
DROP FUNCTION dbo.get_trxns_for_quarter;
go
CREATE FUNCTION dbo.get_trxns_for_quarter (@dt datetime)
RETURNS TABLE
AS
RETURN (SELECT DATEADD(month, Number - 1, @dt) AS TheDate
FROM dbo.Numbers
WHERE Number BETWEEN 1 AND 3);
go
SELECT *
FROM dbo.get_trxns_for_quarter(dbo.get_current_quarter(GetDate()));
go
SELECT id, dt
FROM (SELECT 1, CAST('20070701' AS datetime)
UNION ALL
SELECT 2, CAST('20070815' AS datetime)) AS t(id, dt)
WHERE dt IN (SELECT TheDate
FROM
dbo.get_trxns_for_quarter(dbo.get_current_quarter(GetDate())));
go
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
[Back to original message]
|