InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 22:09:41 GMT20http://www.sqlservercentral.com/Forums/Topic568094-8-1.aspxJust be aware that multi-statement table valued functions tend to perform less then admirably, especially if they'll be returning lots of rows.Fri, 12 Sep 2008 02:00:59 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic568094-8-1.aspxThank you for pointing me in the right direction.MessanThu, 11 Sep 2008 15:10:21 GMTGuy-591148http://www.sqlservercentral.com/Forums/Topic568094-8-1.aspxIn that case, you'll want to go with a multi-statement table valued function. Check in BOL for details, but here's a quick example.[code]CREATE FUNCTION dbo.udf_functest(@ID int)RETURNS @ReturnTable TABLE (TranID int, Amount money)AS BEGIN DECLARE @tempTable TABLE (ID int, TranID int, Amount money) INSERT INTO @tempTable SELECT ID, TransactionID, Amount FROM dbo.Table1 WHERE ID = @ID INSERT INTO @ReturnTable SELECT TranID, Amount FROM dbo.Table2 t1 INNER JOIN @tempTable t2 ON t1.ID = t2.ID RETURNENDGO[/code]Thu, 11 Sep 2008 15:04:56 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic568094-8-1.aspxJohn,I appreciate your prompt reply. The reason why I am using the table declaration is that I have more code that follows the first part. I intend to join to the records in the table variable for speed reasons. Think of it almost like joining to a view in a table-valued function. If I did not have any more codes after the first set of codes, then your suggestion would be the way to go.Thanks again.MessanThu, 11 Sep 2008 14:52:26 GMTGuy-591148http://www.sqlservercentral.com/Forums/Topic568094-8-1.aspxYou don't need the table declaration section. The function itself is a table variable. Your function needs only to have the SELECT statement in it. [code]CREATE function new_function (@some_param nvarchar(20)='MISSED') RETURNS TABLE AS RETURN(select MAX (LEGAL_NM) AS LEGAL_NAME, MAX (TCPRL.VEND_CO_ID) AS VEND_CO_ID, MAX(tcx.max_process_date) as LTST_PROCESSED_CHK_DATE FROM t_bps_company_extract TCX INNER JOIN T_COMPANY_PAYROLL TCPRL ON TCPRL.Vend_CO_ID=TCX.VEND_CO_ID_TEXT WHERE TCX.Company_status = 'Active' group by TCPRL.vend_co_id, TCX.LEGAL_NM) GO[/code]Thu, 11 Sep 2008 13:52:44 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic568094-8-1.aspxThe code that is building the table variable works great outside of the code creating the function. Once I add it the the function, it would not parse. Am I dealing with a syntax issue here? I know that I can not use a temp table in this function, which is the reason why I am going with a table variable (@T_stage).Thanks a lot. CREATE function new_function ( @some_param nvarchar(20)='MISSED') RETURNS TABLE AS RETURN (BEGINdeclare @T_stage TABLE (field1 VARCHAR(40) NOT NULL, field2 NVARCHAR (10) NOT NULL PRIMARY KEY, field3 DATETIME NULL)INSERT @T_COMPANY_PAYROLL_LAST select MAX (LEGAL_NM) AS LEGAL_NAME, MAX (TCPRL.VEND_CO_ID) AS VEND_CO_ID, MAX(tcx.max_process_date) as LTST_PROCESSED_CHK_DATE --INTO #T_COMPANY_PAYROLL_LAST FROM t_bps_company_extract TCX INNER JOIN T_COMPANY_PAYROLL TCPRL ON TCPRL.Vend_CO_ID=TCX.VEND_CO_ID_TEXT WHERE TCX.Company_status = 'Active' group by TCPRL.vend_co_id, TCX.LEGAL_NMEND)Thu, 11 Sep 2008 13:47:36 GMTGuy-591148