Please Help ---User defined functions (using a dynamic table name)

Question

Post reply

Please Help ---User defined functions (using a dynamic table name)

Stacy Parrish

SSC-Addicted

Points: 488
More actions
February 2, 2005 at 11:48 am

#59898

I understand the value of functions in sQL server and was wondering if I was missing something I want to create a function to pass the tablename dynamically. I.E.
CREATE FUNCTION fnSelectTable(@TableName varchar(150))
RETURNS Table
AS
RETURN (select * from @TableName)

Is this not possible outside of creating a dynamic sql statement and using the execute, which I do not want to use ... such as:
Execute('select * from ' + @tablename)

Thanks in advance.

Viewing 13 posts - 1 through 12 (of 12 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

>>Is this not possible outside of creating a dynamic sql statement and using the execute

Correct, not possible. You need dynamic SQL if table name is a parameter.

Stacy Parrish SSC-Addicted Points: 488 More actions · Answer 2

Ok...can anyone suggest a work around ...I want to set up an object for resuability obviously

and is this something MS will provide or enhance in 2005?

Thanks again.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

Where would you need to use this, where you couldn't just select from the table in a sub-query ?

Maybe the solution isn't to look for workarounds but to revisit the design so that dynamic SQL isn't necessary ?

Stacy Parrish SSC-Addicted Points: 488 More actions · Answer 4

That is a good point about revisiting the design but I am not sure that I can avoid this. I am importing data using a DTS package and the DTS package needs to be able to mulit instance from different sources at the same time. So I am staging the data and suffixing the names of the staging tables with a businessdate and source Id to make them unique. Then I was using stored procs to insert the data into the destination tables, which require joins and business logic to transform and place in its intended column. I did not want to hold a connection to the source very long so I am using select ..into statements to stage the data quickly at the destination from the source.

thoughts?

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

So the goal is to have a DTS package behave differently depending on certain runtime conditions ?

DTS in Sql2K is a little weak in this regard. You typically have an ActiveX Script Task as the 1st item in the package, which modifies other tasks in the package before they execute.

The ActiveX Script Task can modify the actual SQL statement of a Execute SQL task at runtime before the SQL executes.

Here's an example. It is modifying a Bulk Insert task, but the principal is the same for any type of task in a DTS package:

http://www.sqldts.com/default.aspx?231

schleep SSChampion Points: 12585 More actions · Answer 6

See CREATE FUNCTION in BOL.

It states that dynamic SQL is not allowed in functions.

Instead of having new tables everyday (tablename_businessdate), why not have permanent tables with a businessdate column. Then, you can keep the records as long as you need (or not), work on the subset records you want.

P

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

What if you populated a @TableVariable with the names of the new tables and then simply looped through those records?

SELECT name AS TableName

INTO @TableVariable

FROM sysobjects

WHERE xtype IN( 'U') -- not 'S or system table

AND UID = 1.0 -- Indicates a dbo created table

You may have to play with the UID as you may not be making dbo created tables...

I wasn't born stupid - I had to study.

schleep SSChampion Points: 12585 More actions · Answer 8

schleep

SSChampion

Points: 12585

February 3, 2005 at 12:03 pm

#540481

Can't SELECT...INTO a table var either.

Farrell Keough SSCoach Points: 17414 More actions · Answer 9

You can insert into one. You need to create it ahead of time. Then call your function.

I wasn't born stupid - I had to study.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 10

Except that the function can't see the @Table since it's only in scope of the caller.

And you can't pass a Table type as a parameter into a function.

Farrell Keough SSCoach Points: 17414 More actions · Answer 11

No, that is not how it works. Within your code, you Create #Table. Then call function and within the function make it Return @Table (same table structure) as the #Table. Then insert into @Table within function. When the function is completed, you will have a populated #Table.

We do it all the time. You just need to make sure your tables match. Since you are only selecting a TableName, that should be easy.

INSERT INTO #Table

SELECT * FROM Function( ParameterToPass)

I wasn't born stupid - I had to study.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 12

Yes, but read the original post:

CREATE FUNCTION fnSelectTable(@TableName varchar(150))

RETURNS Table

AS

RETURN (select * from @TableName)

The design goal is to pass a tablename into a function and have it do something dynamic. But you can't exec dynamic SQL in a function ... so using a function to build an @Table doesn't solve the problem in this particular case.