"Emily" <fir5tsi***@yahoo.com> wrote in message
news:1152802880.219430.63930@i42g2000cwa.googlegroups.com...
> Hi All,
>
> I created the following user-defined function. It passed the syntax
> check so I selected "OK". However, it was not added to the list under
> "User Defined Functions". Anyone can tell me what is wrong with my UDF?
> Thanks!
>
> 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
>        )
>
> -Emily
>

"Emily" <fir5tsi***@yahoo.com> wrote in message
news:1152805373.067759.293230@i42g2000cwa.googlegroups.com...
> Hi Arnie,
>
> I ran the code in Query Analyzer. Then I got error "Parameter
> '@AfterTimestamp' has an invalid data type."
>
> FYI, I get the same error after I changed the data type to "binary" as
> below:
>
> @AfterTimestamp binary  = null,
>
> Any advice on how to get rid of the error?
>
> Thanks!
> -Emily
>