473,890 Members | 1,642 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Pass Table as a parameter to a function

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(@T mpTable)
....
END
Thanks
ArunDhaJ

Jul 25 '07 #1
12 4654
ArunDhaJ (ar******@gmail .com) writes:
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.
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(@T mpTable)
....
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, es****@sommarsk og.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
Jul 25 '07 #2
Here you go and it's not wishful thinking -:)

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

www.beyondsql.blogspot.com
Jul 26 '07 #3

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

Jul 26 '07 #4
On Jul 26, 3:20 pm, Paul <paulwragg2...@ hotmail.comwrot e:
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

Jul 26 '07 #5
ArunDhaJ (ar******@gmail .com) writes:
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, es****@sommarsk og.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
Jul 26 '07 #6
>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.
> 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.
>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.
Jul 31 '07 #7
On Jul 31, 10:00 am, --CELKO-- <jcelko...@eart hlink.netwrote:
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?
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
Jul 31 '07 #8
On Jul 31, 9:00 am, --CELKO-- <jcelko...@eart hlink.netwrote:
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.
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.
Procedures with long lists of parameters simply cannot be fully tested
in reasonable time. Just think how many permutations are there for
1000 nullable parameters. Any responsible professional will avoid
using untested code in real life...

Jul 31 '07 #9
> Procedures with long lists of parameters simply cannot be fully tested in reasonable time. Just think how many permutations are there for 1000 NULL- able parameters. <<

I prefer the "Rule of seven plus or minus two" (http://www.musanim.com/
miller1956/) for a parameter list -- classic Software Engineering.

But you do not test all permutations in a repeated group. I can see
that "p001" to "p999" are all integers, that they are loaded into a
table named "Parts" and are therefore subject to the constraints on
that table. That is simple induction and set-oriented programming.
> Any responsible professional will avoid using untested code in real life...<<
Agreed. I wish there were more them than "Agile Programmers" :) That
is why I like Dijkstra, Mana, Gries, et al -- I want my code to be
provably correct.

I did QA for weapons systems in my youth. I was probably shooting at
you :)

Jul 31 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
528
by: Nelson Xu | last post by:
Hi All Does anyone knows how to pass an array from .net application to oracle stored procedure Thank you in advance Nelson
2
2446
by: Yarik | last post by:
Hello there! I am working with MS SQL Server 2000. I have a table function that takes an integer parameter and returns a table, and I can successfully use it like this (passing a literal as a parameter): SELECT * FROM MyTableFunction(1)
2
1820
by: jlficken | last post by:
I am trying to create a stored procedure that accepts 3 parameters. Using these parameters I am joining on 2 tables and a UDF that accepts these 3 parameters to get movement information. When I try to pass these parameters in the function for each parameter I get and SQL0206 'Position # Column &1 not in specified table'. I am using DB2 on an AS/400 running V5R2 for the operating system. I will post a n example below. CREATE PROCEDURE...
15
7208
by: deko | last post by:
I need a way to create a table with a programmatically defined name. I have a Make Table query that will create the table with the name that I put in the query, but I don't know how to (or if I can) use a function to pass the table name into the query. Here is the basic form of the Mke Tble query: SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO FROM tblTxJournal;
4
11999
by: deko | last post by:
This is a basic program flow question. I'm trying to refractor an AC2000 app and split sections of code into separate modules. But there are a number of collections I create in one big module - containing file paths, Excel worksheet names, etc - I'm not sure how to pass these around, or if I'd be better off saving things to a table and using a recordset. My guess is a recordset would be slower. For example, should I do something like...
0
3330
by: Zlatko Matiæ | last post by:
Hi everybody! Recently I was struggling with client/server issues in MS Access/PostgreSQL combination. Although Access is intuitive and easy to use desktop database solution, many problems appear when someone is trying to use it as front-end for real server database systems such as PostgreSQL or MySQL. One of these problems is regarding pass-through queries and parameters. I wanted to have all the code on client, while executing it on...
5
3754
by: Fresh Air Rider | last post by:
Hello Could anyone please explain how I can pass more than one arguement/parameter value to a function using <asp:linkbutton> or is this a major shortfall of the language ? Consider the following code fragments in which I want to list through all files on a directory with a link to download each file by passing a filename and whether or not to force the download dialog box to appear.
4
160099
by: _Mario.lat | last post by:
Hallo, I have a little question: In the function session_set_save_handler I can pass the name of function which deal with session. In Xoops code I see the use of this function like that: session_set_save_handler(array(&$sess_handler, 'open'), array(&$sess_handler, 'close'), array(&$sess_handler, 'read'), array(&$sess_handler, 'write'), array(&$sess_handler, 'destroy'), array(&$sess_handler, 'gc'));
0
927
by: bjswart | last post by:
Hello, Here is the query I'm trying to run from a C# Windows Program for a Login Form. SELECT CAST(DecryptByCert(Cert_ID('PassEncryption'), Password) AS NVARCHAR) AS Password, A.* FROM Test, @Field1 AS A WHERE Test.EmpID = @Field0 The @Field1 Parameter is the second table with permissions that will change depending on the program. Thus the need to pass it in as a parameter. When I run this I get an error saying "Must declare Table...
0
9977
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9816
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11218
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10910
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10452
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
8009
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5837
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4260
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3268
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.