Pass a table to a user-defined function? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Pass a table to a user-defined function?

Author

Topic

james_b
Starting Member

9 Posts

Posted - 2004-11-18 : 12:48:56

hi

is it possible to pass a table as a parameter to a user-defnied fuunction in Sql Server 2000?

eg

INSERT INTO #TEMP_TABLE SELECT * FROM Get_All_Cities_For_Region(@REGION_ID)
^^ obviously you can do this

INSERT INTO #FINAL SELECT * FROM Do_Something_With_The_Cities(#TEMP_TABLE)
^^ but can I then do this?

thanks for any help :)

chadmat
The Chadinator

1974 Posts

Posted - 2004-11-18 : 13:06:34

Sure you can.

As long as Do_Something_With_The_Cities returns a table.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-11-18 : 13:33:02

What? As far as I knew, you can't "pass" a table, in any form, as a parameter of a udf or stored proc. In fact, to my knowledge, temporary tables are forbidden in udfs. If you need this functionality, create a physical table that will always be used with this udf. For state, you can create an extra column called "BatchID" as a uniqueidentifier and pass that into the udf.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-18 : 13:33:50

Actually, you can't PASS a temp table or table variable to a function. ~~But you can write a function that accesses a ~~temp table~~ table variable. You just have to create the ~~temp table~~ table variable before you call the function, and you wouldn't pass anything.~~

I guess I gotta start reading up on user-defined functions more.

chadmat
The Chadinator

1974 Posts

Posted - 2004-11-18 : 13:40:20

[code]
USE PUBS
GO
CREATE Function get_authors_fn
RETURNS Table AS
RETURN
( SELECT * from Authors )
GO

Insert Into #MyAuthorsTable SELECT * FROM get_authors_fn

[/code]

This is what he asked, and it will work.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

chadmat
The Chadinator

1974 Posts

Posted - 2004-11-18 : 13:42:41

Oh, I see the problem (I missed the (#TEMP_TABLE) part). No, you can't pass the Temp table in as a variable. Just create it first and access it like Rob said.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-11-18 : 13:45:34

[CODE]
if exists (select * from dbo.sysobjects where id = object_id(N'udfTestTable') and xtype in (N'FN', N'IF', N'TF'))
drop function udfTestTable
GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tblTestTable') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.tblTestTable
GO

CREATE TABLE [dbo].[tblTestTable] (
[BatchID] [uniqueidentifier] NOT NULL ,
[MyValue] [int] NOT NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION dbo.udfTestTable (@BatchID uniqueidentifier)
RETURNS TABLE
AS
RETURN
(
SELECT tt.MyValue FROM dbo.tblTestTable tt WHERE tt.BatchID = @BatchID

)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

DECLARE @BatchID1 UNIQUEIDENTIFIER,
@BatchID2 UNIQUEIDENTIFIER;

SELECT @BatchID1 = NewID(),
@BatchID2 = NewID();

INSERT dbo.tblTestTable(BatchID,myValue)
select @BatchID1,1 a
UNION ALL
select @BatchID1,2
UNION ALL
select @BatchID1,3
UNION ALL
select @BatchID1,4
UNION ALL
select @BatchID1,5
UNION ALL
select @BatchID1,6

INSERT dbo.tblTestTable(BatchID,myValue)
select @BatchID2,4 a
UNION ALL
select @BatchID2,5
UNION ALL
select @BatchID2,6
UNION ALL
select @BatchID2,7
UNION ALL
select @BatchID2,8
UNION ALL
select @BatchID2,9

SELECT tt1.MyValue
FROM dbo.udfTestTable(@BatchID1) tt1
JOIN dbo.udfTestTable(@BatchID2) tt2
ON tt1.MyValue = tt2.MyValue;

delete from tblTestTable;
[/CODE]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-18 : 13:46:58

Yeah, well, I didn't know that that doesn't work (I'm still using SQL 7.0 primarily) You can't create a function if it references a temp table, and it can't reference a table variable declared outside of it. The permanent table will have to be the way to go.

There's also the option to format your data as comma-separated values, or XML, and passing that to the function. It can parse it into a table variable and manipulate it that way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources