Results 1 to 2 of 2

Thread: SQL Fucntion

  1. #1
    Join Date
    Dec 2013
    Posts
    2

    SQL Fucntion

    M trying to create a function to return a table!! but it jus pops an error message!!
    Plz help me!!
    The code follows!!

    alter function dbo.anf
    (
    @mdvn int,
    @fdate datetime,
    @tdate datetime
    )
    returns table
    as
    Begin
    declare @vdate date
    declare @fedddate date
    declare @tedddate date
    declare @tvdate date
    set @vdate=dateadd(dd,-91,@fdate)
    set @tvdate=dateadd(dd,36,@tdate)
    set @fedddate=dateadd(dd,20,@fdate)
    set @tedddate=dateadd(dd,310,@tdate)
    declare @REGDT int
    declare @ANC int
    declare @TT1 int
    declare @TT2 int
    declare @IFA int
    declare @BP int
    declare @HB int
    select dvn_cd,phc_cd,hsc_cd,sum(REGDT) as Regdt,SUM(ANC) as ANC,SUM(TT1) as TT1, SUM(TT2)as TT2,
    SUM(IFA) as IFA, SUM(BP) as BP, SUM(HB) as HB from
    (
    select dvn_cd,phc_cd,hsc_cd,
    case when ANEDD IS null then 0 else 1 end as REGDT,
    case when visit_no=3 and ANEDD between @vdate and @tvdate then 1 else 0 end as ANC,
    case when TTB=1 and ANEDD between @fdate and @tdate then 1 else 0 end as TT1,
    case when TTB>2 and ANEDD between @fdate and @tdate then 1 else 0 end as TT2,
    case when IFA=100 and ANEDD between @fdate and @tdate then 1 else 0 end as IFA,
    case when BP>='140/90' and ANEDD between @fdate and @tdate then 1 else 0 end as BP,
    case when HB<11 and ANEDD between @fdate and @tdate then 1 else 0 end as HB
    from ANVisits3 a where DVN_CD=@mdvn and ANEDD between @fedddate and @tedddate
    )a group by dvn_cd,phc_cd,hsc_cd
    end

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Your return statement should be like

    returns table
    as
    return
    (
    select ...
    )

    So remove declares and sets and do the date math within SELECT statement.

    If you want to use multi statement table valued function then you have to use a table variable.



    http://msdn.microsoft.com/en-us/library/ms186755.aspx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •