Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

table variable in a table-valued function Expand / Collapse
Author
Message
Posted Thursday, September 11, 2008 1:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 8:19 PM
Points: 19, Visits: 216


The 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

(BEGIN


declare @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_NM



END

)
Post #568094
Posted Thursday, September 11, 2008 1:52 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:51 PM
Points: 3,840, Visits: 3,848
You 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.

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





John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #568098
Posted Thursday, September 11, 2008 2:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 8:19 PM
Points: 19, Visits: 216
John,

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.

Messan
Post #568134
Posted Thursday, September 11, 2008 3:04 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:51 PM
Points: 3,840, Visits: 3,848
In 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.

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
RETURN
END
GO





John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #568146
Posted Thursday, September 11, 2008 3:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 8:19 PM
Points: 19, Visits: 216
Thank you for pointing me in the right direction.

Messan
Post #568154
Posted Friday, September 12, 2008 2:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
Just be aware that multi-statement table valued functions tend to perform less then admirably, especially if they'll be returning lots of rows.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #568324
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse