Hi All,

I've created a user-defined function as below:

Create FUNCTION dbo.FnReportInstancesGet
    @AfterTimestamp timestamp  = null output,

    @MailingNameFilter DTTypeNameLong = '%',
    @ReportEntityFilter varchar(50) = '%',
    @AccountIDFilter int = null,
    @StatusMessageFilter varchar(255) = '%',
    @CreateSourceFilter varchar(50) = '%',
    @CreateDateBegin datetime = null,
    @CreateDateEnd datetime = null,
    @EffectiveDateBegin datetime = null,
    @EffectiveDateEnd datetime = null

RETURNS TABLE
AS
RETURN
    SET NOCOUNT ON


    SELECT TOP 65536            -- "65536" so we don't accidentally overwhelm the
UI (flex-grid specifically)

        ri.ReportInstanceName,             -- "Account"
        ri.AccountID,
        ri.ReportEntity AccountName,        -- VI 7998
        ri.EffectiveDate,
        rt.PackageName,                    -- "Report Package"
        ri.StatusMessage,
        ri.DueDate,
        ri.CreateDate,                    -- "Create Date" in pace is preproc time
        ri.Priority,                    -- "Critical Level"
        ri.TimestampValue,

        -- CORE-specific
        ri.ReportInstanceID,
        ri.Status,
        ri.CreateSource,
        ri.RetryDate,
        ri.RetriesLeft,
        ri.UpdateDate,
        ri.PivotalAccountMailingID,

        ri.CurrentAgentID,
        ri.CurrentStepInstanceID,
        ri.CurrentStepTypeID,
        ri.ReportTypeID,
        ri.OptionGroupID

    FROM
        ReportInstance ri

        inner join ReportType rt
        on rt.ReportTypeID = ri.ReportTypeID
    WHERE

        -- timestamp filtering

        ri.TimestampValue > isnull(@AfterTimestamp,0x0000000000000000)

        -- other filtering
        and isnull(rt.PackageName,'') like @MailingNameFilter
        and isnull(ri.ReportEntity,'') like @ReportEntityFilter
        and (@AccountIDFilter is null or (ri.AccountID = @AccountIDFilter))
        and isnull(ri.StatusMessage,'') like @StatusMessageFilter
        and isnull(ri.CreateSource,'') like @CreateSourceFilter
        and ri.CreateDate between isnull(@CreateDateBegin,'1900-01-01') and
isnull(@CreateDateEnd,'2100-01-01')
        and ri.EffectiveDate between isnull(@EffectiveDateBegin,'1900-01-01')
and isnull(@EffectiveDateEnd,'2100-01-01')

    ORDER BY
        ri.TimestampValue DESC



    SET @AfterTimestamp = @@dbts

    SET NOCOUNT OFF
    RETURN 0
GO


However, it gives a syntax error:

"Error 170: Line 2: Incorrect syntax near '@AfterTimestamp'.
Must declare the variable '@AfterTimestamp'.
Invalid use of '@@dbts' within a function.
A RETURN statement with a return value can not be used in this
context."

It seems that the "AfterTimestamp" is causing trouble. Anyone can
advise me on how to get rid of this error?

Many thanks!
-Emily

It may be that trying to use an OUTPUT parameter with a function is getting
the query processor all confused. ;-)

I suggest removing the word OUTPUT in the Function declaration.

Show quoteHide quote

On 12 Jul 2006 14:30:38 -0700, Emily wrote:

Hi Emily,

Unlike stored procedures, user-defined functions REQUIRE you to enclose
the parameter list in parentheses.

And UDFs don't support OOUTPUT parameters.

Or even OUTPUT paramters either.  ;-)

Show quoteHide quote

Hi Arnie and Hugo,

Thanks for your kind advice!

I'll remove "OUTPUT" as you suggested. However, that means
AfterTimestamp will be an input parameter. I have to feed data to this
parameter, which is kind of difficult because AfterTimestamp, in SQL
Server, is binary data and guaranteed to be different each time.

Any more advice on how to handle this parameter?

-Emily


Arnie Rowland wrote:
Show quoteHide quote

If your application has previously retrieved the [AfterTimestamp] from the
table, you 'should' be holding it in a compatible variable type.Then for the
stored procedure, make the INPUT parameter a binary datatype, and be sure
that the application passes the data in the correct form.

And remove the SET @AfterTimestamp at the bottom, it is not going to do
anything.

Show quoteHide quote

Hi Arnie,  Aaron,

Thanks for your valuable advice! I've removed "OUTPUT" and "SET
AfterTimestamp =  @@dbts", and added parentheses around the parameter
list. Now the syntax check is successful.

However, when I select "OK", the UDF editor dialog is closed but the
UDF doesn't appear on the list. This means, after all of the work, it's
gone!

Luckily, I copied the whole function to a notepad. Would you take a
look and tell me why it is not added to the list:

Create FUNCTION dbo.FnReportInstancesGet
        --@AfterTimestamp timestamp  = null output,
        (@AfterTimestamp timestamp  = null, --output,

        @MailingNameFilter DTTypeNameLong = '%',
        @ReportEntityFilter varchar(50) = '%',
        @AccountIDFilter int = null,
        @StatusMessageFilter varchar(255) = '%',
        @CreateSourceFilter varchar(50) = '%',
        @CreateDateBegin datetime = null,
        @CreateDateEnd datetime = null,
        @EffectiveDateBegin datetime = null,
        @EffectiveDateEnd datetime = null )


RETURNS TABLE
AS
RETURN
        (--SET NOCOUNT ON


        SELECT TOP 65536                        -- "65536" so we don't
accidentally overwhelm the UI (flex-grid specifically)


                ri.ReportInstanceName,                  -- "Account"
                ri.AccountID,
                ri.ReportEntity AccountName,            -- VI 7998
                ri.EffectiveDate,
                rt.PackageName,                                 --
"Report Package"
                ri.StatusMessage,
                ri.DueDate,
                ri.CreateDate,                                  --
"Create Date" in pace is preproc time
                ri.Priority,                                    --
"Critical Level"
                ri.TimestampValue,


                -- CORE-specific
                ri.ReportInstanceID,
                ri.Status,
                ri.CreateSource,
                ri.RetryDate,
                ri.RetriesLeft,
                ri.UpdateDate,
                ri.PivotalAccountMailingID,


                ri.CurrentAgentID,
                ri.CurrentStepInstanceID,
                ri.CurrentStepTypeID,
                ri.ReportTypeID,
                ri.OptionGroupID


        FROM
                ReportInstance ri


                inner join ReportType rt
                on rt.ReportTypeID = ri.ReportTypeID
        WHERE


                -- timestamp filtering


                ri.TimestampValue >
isnull(@AfterTimestamp,0x0000000000000000)


                -- other filtering
                and isnull(rt.PackageName,'') like @MailingNameFilter
                and isnull(ri.ReportEntity,'') like @ReportEntityFilter

                and (@AccountIDFilter is null or (ri.AccountID =
@AccountIDFilter))
                and isnull(ri.StatusMessage,'') like
@StatusMessageFilter
                and isnull(ri.CreateSource,'') like @CreateSourceFilter

                and ri.CreateDate between
isnull(@CreateDateBegin,'1900-01-01') and
isnull(@CreateDateEnd,'2100-01-01')
                and ri.EffectiveDate between
isnull(@EffectiveDateBegin,'1900-01-01')
and isnull(@EffectiveDateEnd,'2100-01-01')


        ORDER BY
                ri.TimestampValue DESC


        --SET @AfterTimestamp = @@dbts


        --SET NOCOUNT OFF
        --RETURN 0
        )

Many thanks!
-Emily

I don't know what "the UDF editor" is but I strongly recommend using Query
Analyzer for writing/maintaining database objects like functions, triggers,
views, and stored procedures.  For one thing, when you run the ALTER script
by hitting F5, your script doesn't disappear.  There are are many more
issues associated with using Enterprise Manager for development, that you've
just been lucky not to have come across yet.

My guess as to why it's not in the list is probably because it doesn't
automatically refresh.

A

Hi all,

FYI, I've created a new thread with the topic of
"User-defined function not show up after being created".

Thanks,
-Emily

Hi Arnie and Hugo,

Thanks for your kind advice!

I'll remove "OUTPUT" as you suggested. However, that means
AfterTimestamp will be an input parameter. I have to feed data to this
parameter, which is kind of difficult because AfterTimestamp, in SQL
Server, is binary data and guaranteed to be different each time.

Any more advice on how to handle this parameter?

-Emily

Or the even more elusive OUTPUT parameters.  :)

Ouch!

Show quoteHide quote

On Wed, 12 Jul 2006 16:46:32 -0700, Arnie Rowland wrote:

Aaarghh. I *hate* my new keybooard!!

Circular Autonum Keys
Application Role vs One SQL Login
Sample deployment script
Pivoting Data In SQL Server 2000
Query abt Srvc Broker Sample HelloWorld_CLR