Pass Table as a parameter to a function

#1

Hi Friends,
Is it possible to pass a table as a parameter to a funtion.

whos function declaration would look some thing like this....
ALTER FUNCTION TempFunction (@TempTable TABLE, @nPId INT)

my problem is: i have to access a temporary table created in an SP in
a function

ALTER PROCEDURE MySP
BEGIN
....
DECLARE @TmpTable TABLE(...)
....
TempFunction(@TmpTable)
....
END

Thanks
ArunDhaJ

#2

ArunDhaJ (arundhaj (AT) gmail (DOT) com) writes:

Quote:

Is it possible to pass a table as a parameter to a funtion.

It should be in SQL 2008, which currently is in beta. The functionality
is available in the current CTP, but I have not played with it, so I
can't say for sure that it works with functions.

Quote:

my problem is: i have to access a temporary table created in an SP in
a function

ALTER PROCEDURE MySP
BEGIN
....
DECLARE @TmpTable TABLE(...)
....
TempFunction(@TmpTable)
....
END

You probably need to rewrite the function as a procedure. See here for
some tips of passing data between stored procedures in current SQL versions.
http://www.sommarskog.se/share_data.html

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#3

Here you go and it's not wishful thinking -

http://beyondsql.blogspot.com/2007/0...parameter.html

www.beyondsql.blogspot.com

#4

As already suggested a procedure will work. We have a procedure to
drop all default constraints from our tables. I pass in a list of
tables to this procedure (the list of tables are loaded in to a table
variable by calling a function). If you need further help then re-post
and I can hopefully give you an example!

Paul

#5

On Jul 26, 3:20 pm, Paul <paulwragg2... (AT) hotmail (DOT) com> wrote:

Quote:

As already suggested a procedure will work. We have a procedure to
drop all default constraints from our tables. I pass in a list of
tables to this procedure (the list of tables are loaded in to a table
variable by calling a function). If you need further help then re-post
and I can hopefully give you an example!

Paul

Hi All,
Thanks for your response..

Ya.. the procedure will do fine....
But my question is that is there any performance difference between
using functions and procedure....

Thanks
ArunDhaJ

#6

ArunDhaJ (arundhaj (AT) gmail (DOT) com) writes:

Quote:

Ya.. the procedure will do fine....
But my question is that is there any performance difference between
using functions and procedure....

That all depends on how you implement and use them. You can't say that any
is faster than the other as such.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#7

Quote:

Is it possible to pass a table as a parameter to a function.

Please read a book, any book, on data modeling, and RDBMS. A table is
an entity or a relationship. That would mean you have a magical,
super function that works on Squids, Automobiles, Britney Spears,
Geographical locations or anything in the whole of creation.

Or you have a bad design which you are not showing us. That is what Ia
m beting.

Quote:

I have to access a temporary table created in an SP in a function

No, you do not. You just don't know how to write declarative, non-
procedural code so you are mimicking a scratch tape with a table that
appears out nowhere in your data model as if you were still using a
magnetic tape file system instead of an RDBMS.

#8

On Jul 28, 2:03 pm, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:

Quote:

Is it possible to pass a table as a parameter to a function.

Please read a book, any book, on data modeling, and RDBMS. A table is
an entity or a relationship. That would mean you have a magical,
super function that works on Squids, Automobiles, Britney Spears,
Geographical locations or anything in the whole of creation.

No; it would much more likely mean that he wants to pass a set of
values to his function. There are various reasons to want something
like this, I'll give you a prime example: When passing mutli-value
parameters in Reporting Services, the most common method is to use IN
- so you might have a parameter @someParam and then in your underlying
queries you'll have WHERE some_column in ( @someParam )

This can lead to performance issues when the number of values in the
parameter are very large, and in some cases can even fail to run
because essentially SRS creates a long comma delimited string; but
there is a limit to the size that string can be. There are
workarounds, of course, but having the ability to simply pass a set
would make things enormously easier, faster, and cleaner - which is
probably why they're including it in SQL 2008.

Quote:

Or you have a bad design which you are not showing us. That is what Ia
m beting.

I am betting your lack of real-world development experience has
rendered you incapable of understanding why someone would need this.

Quote:

I have to access a temporary table created in an SP in a function

No, you do not. You just don't know how to write declarative, non-
procedural code so you are mimicking a scratch tape with a table that
appears out nowhere in your data model as if you were still using a
magnetic tape file system instead of an RDBMS.

I can think of several scenarios in which doing exactly what he is
asking would be necessary - reporting being the most obvious.

#9

Quote:

No; it would much more likely mean that he wants to pass a set of values to his function.

Not very likely at all. Read the last 5+ years of postings here and
you will see that when they want to pass a list to an IN() predicate,
they explicitly ask about that. When they want to pass a table they
explicitly ask about that, as this guy did.

Quote:

the ability to simply pass a set would make things enormously easier, faster, and cleaner - which is probably why they're including it in SQL 2008.

Right now you can declare a huge number of parameters in a stored
procedure -- more than enough for any practical situation. But
programmers who grew up with BASIC and other interpreted languages
seem to panic at the the thought of a long parameter list.

Quote:

I can think of several scenarios in which doing exactly what he is
asking would be necessary - reporting being the most obvious.

The most obvious is a system utility program which treats all tables
as tables rather than as part of a logical model. Now you are at the
meta data level, which has no place in an application or RDBMS
schema.

#10

On Jul 31, 10:00 am, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:

Quote:

No; it would much more likely mean that he wants to pass a set of values to his function.

Not very likely at all. Read the last 5+ years of postings here and
you will see that when they want to pass a list to an IN() predicate,
they explicitly ask about that. When they want to pass a table they
explicitly ask about that, as this guy did.

And do you suppose his interest is in the table itself, or the set of
data that the table contains?

Quote:

the ability to simply pass a set would make things enormously easier, faster, and cleaner - which is probably why they're including it in SQL 2008.

Right now you can declare a huge number of parameters in a stored
procedure -- more than enough for any practical situation. But
programmers who grew up with BASIC and other interpreted languages
seem to panic at the the thought of a long parameter list.

Clearly you did not understand the example I gave you. I wasn't
talking about passing many parameters, I was talking about passing one
parameter that can have many values. A drop-down list where the user
can select more than one value. In other words, a set. This is an
EXTREMELY common scenario in the real world.

Classroom coders who have little to no development experience in the
real world tend to panic at the thought of examples that are outside
of their limited experience :b