title: Table Variable In a table Valued function
body: <p>I am getting an error when trying to get this table valued function up. I have an error when I try to modify it. It is</p>
  <blockquote>
  <p>Incorrect syntax near the keyword 'Declare'.</p>
  </blockquote>
  <p>However when I use this outside of the function it all works great. So I was just wondering is there something I am missing or how should I be doing this. Thanks.</p>
  <pre><code> ALTER FUNCTION [dbo].[XXX]( @i_contactkey int, 
                                                          @v_scope varchar(15), 
                                                          @i_entrykey int = null, 
                                                          @i_staffcontactkey int = null,
                                                          @d_startdate datetime,
                                                          @d_today datetime)
  RETURNS TABLE
  AS begin
  Declare @temp as table
  (contactkey int, contactkey1 int, rolekey1 int,contactkey2 int, rolekey2 int, relationshipid int) 
  insert into @temp    (contactkey , contactkey1 , rolekey1 ,contactkey2 , rolekey2 , relationshipid ) 
  select contactkey , contactkey1 , rolekey1 ,contactkey2 , rolekey2 , relationshipid  
  from contact clcon 
          LEFT JOIN contactassociation ca on ca.contactkey2 = clcon.contactkey where  ca.rolekey1 in (4,5,6)
                      and ca.relationshipid = 181 
                      and ca.activeind = 1
                      and ca.associationkey = (select top 1 associationkey 
                                               from contactassociation 
                                               where contactkey2 = clcon.contactkey and activeind = 1
                                                  and relationshipid = 181
                                                  and rolekey1 in (4,5,6)
                                               order by begindate desc)
                                               
                              
  
          SELECT 
               clcon.contactkey'ClientId', 
               clcon.Stat'ClientStatus', 
               ctacct.optiondesc'account',
               ctlvl.optiondesc'levelid',
              (clcon.lastname+', '+clcon.firstname)'ClientName',
               clcon.firstname'ClientFirstName', 
               clcon.lastname'ClientLastName',   
               clcon.addressline1'address1',
               clcon.addressline2'address2',
               clcon.city'city',
               dbo.getcnfgoption(81,clcon.stateid,'D')'state',
               clcon.zipcode'zipcode',
               clcon.mainphone'mainphone',
               cgcon.contactkey'cgkey',(cgcon.firstname+' '+cgcon.lastname)'CGName', 
               cgcon.firstname'CGFirstName', 
               cgcon.lastname'CGLastName', 
               cgcon.addressline1'cgaddressline1',
               cgcon.addressline2'cgaddressline2',
               cgcon.city'cgcity',
               dbo.getcnfgoption(81,cgcon.stateid,'D')'cgstate',
               cgcon.zipcode'cgzipcode',
               cgcon.mainphone'cgmainphone',  
               dbo.getClientAltCGKeys_JSON(clcon.contactkey,'J')'AltCGsJSON', 
               --dbo.getClientAltCGKeys(clcon.contactkey,'C')'AltCGNames', 
               --dbo.getClientAltCGKeys(clcon.contactkey,'L')'altcgnamekeyslast',
               --dbo.getClientAltCGKeys(clcon.contactkey,'A')'altcgkeysaddress',
               dbo.getClientEventCount(clcon.contactkey, 'M', @d_startdate, @d_today) 'MLOA',
               dbo.getClientEventCount(clcon.contactkey, 'N', @d_startdate, @d_today) 'NMLOA',
               dbo.getClientEventCount(clcon.contactkey, 'A', @d_startdate, @d_today) 'Alts',
               dbo.getClientEventCount(clcon.contactkey, 'S', @d_startdate, @d_today ) 'Suspension',
               dbo.getClientEventCountAnnual(clcon.contactkey, 'C') 'MissingNotes',           
              -- dbo.getContactVerificationStatus(clcon.contactkey, 'D')'clverification',
              -- dbo.getContactVerificationStatus(cgcon.contactkey, 'D')'cgverification',
               ed1.eventkey 'mdskey',
               dbo.getCnfgTableOption(54,ed1.eventstatusid,'D')'mdsstatus',
               ed1.ScheduledDate 'NextMDS',
               ed2.eventkey 'pockey',
               dbo.getCnfgTableOption(54,ed2.eventstatusid,'D')'pocstatus',
               ed2.ScheduledDate 'NextPoC',
               ed3.eventkey 'hvkey',
               dbo.getCnfgTableOption(54,ed3.eventstatusid,'D')'hvsstatus',
               ed3.ScheduledDate 'NextHV',
               ed4.eventkey 'medlistkey',
               dbo.getCnfgTableOption(54,ed4.eventstatusid,'D')'medstatus',
               ed4.ScheduledDate 'NextMedList',
               ed5.eventkey 'semikey',
               dbo.getCnfgTableOption(54,ed5.eventstatusid,'D')'semistatus',
               ed5.ScheduledDate 'NextSemi',          
               ed6.eventkey'placementkey',
               ed6.startdate'placementstart',
               ed6.enddate'placementend',
               [dbo].[getClientCMName](clcon.contactkey)'cmname',
               [dbo].[getClientRNName](clcon.contactkey)'rnname',
               [dbo].[getClientCMKey](clcon.contactkey)'cmkey',
               [dbo].[getClientRNKey](clcon.contactkey)'rnkey',
              alertcount=(SELECT COUNT(eventalertkey) FROM veventalert WHERE alerttype='Alert' AND clientkey=clcon.contactkey AND viewedind=0 AND contactkey=COALESCE(@i_staffcontactkey,@i_contactkey)),
              alertkey=(SELECT MAX(eventalertkey) FROM veventalert WHERE alerttype='Alert' AND clientkey=clcon.contactkey AND viewedind=0 AND contactkey=COALESCE(@i_staffcontactkey,@i_contactkey)),
              msgcount=(SELECT COUNT(eventalertkey) FROM veventalert WHERE alerttype='Message' AND clientkey=clcon.contactkey AND viewedind=0 AND cgkey=cgcon.contactkey),
              msgkey=(SELECT MAX(eventalertkey) FROM veventalert WHERE alerttype='Message' AND clientkey=clcon.contactkey AND viewedind=0 AND cgkey=cgcon.contactkey),
              clcp.birthdate
          FROM (select dbo.getcontactstatus(contactkey,'ts')'Stat',* from contact where dbo.getcontactstatus(contactkey,'ts') is not null ) clcon 
          INNER JOIN contactrole cr
              ON (clcon.contactkey = cr.contactkey)
          --Find caregiver contact info
          LEFT JOIN contactassociation ca on ca.contactkey2 = clcon.contactkey and ca.rolekey1 in (4,5,6)
                      and ca.relationshipid = 181 and ca.activeind = 1
                      and ca.associationkey = (select max(associationkey) 
                                               from contactassociation 
                                               where contactkey2 = clcon.contactkey and activeind = 1
                                                  and relationshipid = 181
                                                  and rolekey1 in (4,5,6))
          LEFT JOIN contact cgcon 
              ON cgcon.contactkey = ca.contactkey1 and cgcon.activeind = 1            
          LEFT JOIN contactbu cbu
              ON (clcon.contactkey = cbu.contactkey)
              /*Account/Lvl Information*/
          LEFT JOIN contactprofile clcp
              ON (clcon.contactkey=clcp.contactkey)
          LEFT JOIN cnfgtableoption  ctlvl
              ON (clcp.svclevelid = ctlvl.tableoptionkey)
          LEFT JOIN cnfgtableoption  ctacct
              ON (clcp.accountid = ctacct.tableoptionkey)
          
          LEFT JOIN eventdefinition ed1               /* MDS */
              ON (clcon.contactkey=ed1.contactkey AND ed1.eventkey=dbo.getContactEventByWftask(clcon.contactkey, 181, 'MINOPEN'))
          LEFT JOIN eventdefinition ed2               /* POC */
              ON (clcon.contactkey=ed2.contactkey AND ed2.eventkey=dbo.getContactEventByWftask(clcon.contactkey, 120, 'MINOPEN'))
          LEFT JOIN eventdefinition ed3               /* HV */
              ON (clcon.contactkey=ed3.contactkey AND ed3.eventkey=dbo.getContactEventByWftask(clcon.contactkey, 341, 'MINOPEN'))
          LEFT JOIN eventdefinition ed4               /* MED */
              ON (clcon.contactkey=ed4.contactkey AND ed4.eventkey=dbo.getContactEventByWftask(clcon.contactkey, 178, 'MINOPEN'))
          LEFT JOIN eventdefinition ed5               /* SEMI */
              ON (clcon.contactkey=ed5.contactkey AND ed5.eventkey=dbo.getContactEventByWftask(clcon.contactkey, 122, 'MINOPEN'))                     
          LEFT JOIN eventdefinition ed6           /* Placement */
              ON (clcon.contactkey=ed6.contactkey AND ed6.wftaskkey = 49
                      AND ed6.eventstatusid = 16
                      AND ed6.activeind = 1
                      AND ed6.enddate = (select MAX(enddate) from eventdefinition 
                                         where contactkey = clcon.contactkey and wftaskkey = 49
                                          and activeind = 1
                                          and eventstatusid = 16))
          
          WHERE
          --Contact info
          cr.rolekey = 8 
          AND cbu.entrykey = @i_entrykey
          --and dbo.getcontactstatus (clcon.contactkey,'TS') not in ('Closed','Discharged')
          and clcon.Stat not in ('Closed')
          and clcon.activeind=1
          -- filter by branch entrykey (if param exists)
          --order by clcon.lastname,clcon.firstname
  </code></pre>