May 26, 2008 at 12:00 pm
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
May 26, 2008 at 12:21 pm
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]
May 26, 2008 at 3:21 pm
OK try the approach with stored procedures even though I will have to change much code.
I hoped there was some trick.
Thanks
Sergio
May 26, 2008 at 3:35 pm
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]
May 26, 2008 at 3:58 pm
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).
May 26, 2008 at 4:06 pm
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]
May 26, 2008 at 4:21 pm
To create a separate function for each table should have a finite and limited number of tables, I say good?
May 26, 2008 at 5:22 pm
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]
May 27, 2008 at 3:47 am
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
May 27, 2008 at 7:01 am
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]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply