Create multistatement table-valued functions

Question

Post reply

Create multistatement table-valued functions

sgovoni

Ten Centuries

Points: 1156
More actions
May 26, 2008 at 12:00 pm

#207743

Hi,
I'm developing (with SQL Server 2000) a multistatement table-valued functions to process and return the data from a table passed as a parameter.
Here the definition of the table:
IF OBJECT_ID('UDF_ELABDATA') IS NOT NULL
DROP FUNCTION dbo.UDF_ELABDATA
GO
CREATE FUNCTION dbo.UDF_ELABDATA(@TableName As Varchar(128))
RETURNS @Table TABLE
(Id INT IDENTITY(1, 1) NOT NULL,
IdART INT NOT NULL,
IdORIG INT NOT NULL,
QTA FLOAT,
FLAG SMALLINT)
AS
BEGIN
INSERT INTO @Table
(IdART,
IdORIG,
QTA,
FLAG)
SELECT
IdART,
IdORIG,
QTA,
FLAG
FROM @TableName
--Data Processing
-- UPDATE @Table....
-- UPDATE @Table....
RETURN
END
The definition of the function goes wrong in part highlighted.
How can I enter data into temporary variable @Table from the table passed as a parameter ?
I do not want to use a stored procedure because the caller would have to run a SELECT * FROM dbo.UDF_ELABDATA( )
Thanks
Sergio

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

You need to use dynamic SQL if you want to treat the tablename as a variable.

However, you cannot use dynamic SQL in a user-defined function.

You'll have to use another approach.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

sgovoni Ten Centuries Points: 1156 More actions · Answer 2

OK try the approach with stored procedures even though I will have to change much code.

I hoped there was some trick.

Thanks

Sergio

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Alternatively, you could use dynamic SQL to pre-generate a seperate function for each table that you want this function to apply to.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

sgovoni Ten Centuries Points: 1156 More actions · Answer 4

Unfortunately the names of the table passed at the function represent temporary tables whose name is given by: a prefix + user ID (for example tmpProduct123 to indicate the products of the temporary table with user ID 123).

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Oh well, then.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

sgovoni Ten Centuries Points: 1156 More actions · Answer 6

To create a separate function for each table should have a finite and limited number of tables, I say good?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

yes

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

sgovoni Ten Centuries Points: 1156 More actions · Answer 8

Hello rbarryyoung,

this is the approach that I used to solve the problem:

1) Creating a stored procedures can accept as a parameter the name of temporary table (relative to the user)

2) Using OPENQUERY() function to execute the stored procedures

[font="Courier New"] EXEC sp_serveroption 'nome_server', 'Data Access', 'true'

SELECT

T1.*

FROM

OPENQUERY(nome_server,

'EXEC DBNAME.schema.USP_ELABDATA ''dbo.TmpProduct123''') AS T1[/font]

What do you think ?

Thanks

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Looks OK to me, but I am not an expert on OPENQUERY.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]