Pass Table as a parameter to a function

[ArunDhaJ]

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

[Erland Sommarskog]

Re: Pass Table as a parameter to a function

ArunDhaJ (arundhaj@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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[steve]

Re: Pass Table as a parameter to a function

Here you go and it's not wishful thinking -:)

http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-parameter.html

https://www.beyondsql.blogspot.com

[Paul]

Re: Pass Table as a parameter to a function


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

[ArunDhaJ]

Re: Pass Table as a parameter to a function

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

[Erland Sommarskog]

Re: Pass Table as a parameter to a function

ArunDhaJ (arundhaj@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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

Re: Pass Table as a parameter to a function

>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.

[Shuurai]

Re: Pass Table as a parameter to a function

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

[Alex Kuznetsov]

Re: Pass Table as a parameter to a function

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...

[--CELKO--]

Re: Pass Table as a parameter to a function

> 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 :)

[Shuurai]

Re: Pass Table as a parameter to a function

I wasn't talking about passing many parameters, I was talking about passing one parameter that can have many values. <<

>
No, it cannot have many values bey definition. Parameters have to be
a scalar value. At one point in ANSI we talked about passing tables
in the SQL/PSM and decided against it. Defining comparisons, the
parameter declarations and constraints, use of VIEWs, etc. made SQL
injection look like a blessing.

The problem with the current method is that it *is* a scalar value.
Reporting Services creates a comma delimited string containing all of
the values selected by the user. This can pose problems when there
are large numbers of values selected. The ability to pass a set would
be a great benefit, and would not require any changes to the way data
is stored.

That aside, your statement that procedures can have many parameters is
meaningless - it doesn't even remotely address the question. Either
you didn't understand the question, or you simply spit out one of your
standard cookie-cutter replies that you felt could best be wedged into
the discussion.

A drop-down list where the user can select more than one value. <<

>
Gee, I looked all over my SQL Standards and books, but could not find
a drop-down list mentioned. Are you sure that is not part of the
front end and not something which a good programmer would in the
database :)?

The drop-down list *is* in the front end; which is Reporting
Services. Reporting Services uses SQL queries or stored procedures to
pull data. Therefore - stay with me, Joe - those queries/procedures
have to be written to accomidate multiple selections for a drop down
menu.

I strongly suspect that most businesses aren't going to stop using
this functionality just because you say it shouldn't work or that it
isn't standard; nor are they going to wait for Microsoft to create
some additional tier in between Reporting Services and the database;
especially when such a tier is not needed.

This is an EXTREMELY common scenario in the real world. <<

>
Yes, in the applications side of the real world, not the database.
Hey, there is nothing wrong with being an application programmer. But
it is a different tier.

In this case the only efficient means of getting the data with the
parameters needed is via SQL script or stored procedure; in either
case requiring that SQL be written to handle multiple selections.

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 <<

>
LOL! I have been gathering "limited experience" for over 35 years
now! And I have had some influence on RDBMS over the last few
decades. Instead of being a "code monkey" any more, I get called in
to train progammers, design DBs and repair disasters. Part of me
misses the programming discipline of a military weapons or medical
records system. If it screws up even a little or if it goes down, the
wrong people die.

How many years has it been since you've done any real work in the
field?

[snip]

A volunteer programmer did a pull-down list where the package options
were in a comma separated list column in the DB. It made his display
easier. But it messed up the pick list when smaller units were
available. People thought they were asking for 100 units, but it
became 10 units in the backend.
>
His little violation of 1NF and blending of tiers meant that field
medical personnel had to decide which children would and would not get
antibiotics.

What he did has nothing to do with anything I'm currently talking
about. I haven't said anything about storing comma delimited lists in
columns in the database, or anything like that. Nothing like that is
even necessary. In fact, nothing I am talking about requires any
change whatsoever in how the data is stored.

As you so often do, you are now pulling out and emotionally charged
disaster scenario that is at best superficially related to the topic
at hand. I tell you that it'd be nice to be able to pass a set as a
parameter, and you go into a story about how a bunch of kids didn't
get medicine because some bad programmer made an obvious error that
spread across tiers. Sad story, no doubt, but it has nothing to do
with what we're talking about.

[Ed Murphy]

Re: Pass Table as a parameter to a function

Shuurai wrote:

>>> I wasn't talking about passing many parameters, I was talking about passing one parameter that can have many values. <<

>No, it cannot have many values bey definition. Parameters have to be
>a scalar value. At one point in ANSI we talked about passing tables
>in the SQL/PSM and decided against it. Defining comparisons, the
>parameter declarations and constraints, use of VIEWs, etc. made SQL
>injection look like a blessing.

>
The problem with the current method is that it *is* a scalar value.
Reporting Services creates a comma delimited string containing all of
the values selected by the user. This can pose problems when there
are large numbers of values selected. The ability to pass a set would
be a great benefit, and would not require any changes to the way data
is stored.

I tend to agree with you - provided that the procedure can define what
type of set is valid, thus avoiding the Squids objection. On the other
hand, if the user /needs/ to select /large/ numbers of values, then
someone should try to refactor the overall system to eliminate that
need; by adding a few appropriate classifying attributes, the user may
be able to select just one or a few such attributes, and the DB can
compute the large list.

[Tony Rogerson]

Re: Pass Table as a parameter to a function

Comparing the CSV approach with the parameter approach below, which one do
you consider more maintainable and supportable?

Also consider the application will need one line per parameter used on the
stored procedure.

This which takes milliseconds to edit in Management Studio....

create proc getdata_dynamic
@csv varchar(max)

as
set @csv = replace( @csv, '''', '''''' )

exec( ' DECLARE @c int
select @c = count(*)
from somedata
where avalue in ( ' + @csv + ' )'
)
go

Or this which takes over two minutes to edit in Management Studio (on a 4GB
dual proc AMD machine)....

create proc [dbo].[getdata_parms] @p1 int, @p2 int, @p3 int, @p4 int, @p5
int, @p6 int, @p7 int, @p8 int, @p9 int, @p10 int, @p11 int, @p12 int, @p13
int, @p14 int, @p15 int, @p16 int, @p17 int, @p18 int, @p19 int, @p20 int,
@p21 int, @p22 int, @p23 int, @p24 int, @p25 int, @p26 int, @p27 int, @p28
int, @p29 int, @p30 int, @p31 int, @p32 int, @p33 int, @p34 int, @p35 int,
@p36 int, @p37 int, @p38 int, @p39 int, @p40 int, @p41 int, @p42 int, @p43
int, @p44 int, @p45 int, @p46 int, @p47 int, @p48 int, @p49 int, @p50 int,
@p51 int, @p52 int, @p53 int, @p54 int, @p55 int, @p56 int, @p57 int, @p58
int, @p59 int, @p60 int, @p61 int, @p62 int, @p63 int, @p64 int, @p65 int,
@p66 int, @p67 int, @p68 int, @p69 int, @p70 int, @p71 int, @p72 int, @p73
int, @p74 int, @p75 int, @p76 int, @p77 int, @p78 int, @p79 int, @p80 int,
@p81 int, @p82 int, @p83 int, @p84 int, @p85 int, @p86 int, @p87 int, @p88
int, @p89 int, @p90 int, @p91 int, @p92 int, @p93 int, @p94 int, @p95 int,
@p96 int, @p97 int, @p98 int, @p99 int, @p100 int, @p101 int, @p102 int,
@p103 int, @p104 int, @p105 int, @p106 int, @p107 int, @p108 int, @p109 int,
@p110 int, @p111 int, @p112 int, @p113 int, @p114 int, @p115 int, @p116 int,
@p117 int, @p118 int, @p119 int, @p120 int, @p121 int, @p122 int, @p123 int,
@p124 int, @p125 int, @p126 int, @p127 int, @p128 int, @p129 int, @p130 int,
@p131 int, @p132 int, @p133 int, @p134 int, @p135 int, @p136 int, @p137 int,
@p138 int, @p139 int, @p140 int, @p141 int, @p142 int, @p143 int, @p144 int,
@p145 int, @p146 int, @p147 int, @p148 int, @p149 int, @p150 int, @p151 int,
@p152 int, @p153 int, @p154 int, @p155 int, @p156 int, @p157 int, @p158 int,
@p159 int, @p160 int, @p161 int, @p162 int, @p163 int, @p164 int, @p165 int,
@p166 int, @p167 int, @p168 int, @p169 int, @p170 int, @p171 int, @p172 int,
@p173 int, @p174 int, @p175 int, @p176 int, @p177 int, @p178 int, @p179 int,
@p180 int, @p181 int, @p182 int, @p183 int, @p184 int, @p185 int, @p186 int,
@p187 int, @p188 int, @p189 int, @p190 int, @p191 int, @p192 int, @p193 int,
@p194 int, @p195 int, @p196 int, @p197 int, @p198 int, @p199 int, @p200 int,
@p201 int, @p202 int, @p203 int, @p204 int, @p205 int, @p206 int, @p207 int,
@p208 int, @p209 int, @p210 int, @p211 int, @p212 int, @p213 int, @p214 int,
@p215 int, @p216 int, @p217 int, @p218 int, @p219 int, @p220 int, @p221 int,
@p222 int, @p223 int, @p224 int, @p225 int, @p226 int, @p227 int, @p228 int,
@p229 int, @p230 int, @p231 int, @p232 int, @p233 int, @p234 int, @p235 int,
@p236 int, @p237 int, @p238 int, @p239 int, @p240 int, @p241 int, @p242 int,
@p243 int, @p244 int, @p245 int, @p246 int, @p247 int, @p248 int, @p249 int,
@p250 int, @p251 int, @p252 int, @p253 int, @p254 int, @p255 int, @p256 int,
@p257 int, @p258 int, @p259 int, @p260 int, @p261 int, @p262 int, @p263 int,
@p264 int, @p265 int, @p266 int, @p267 int, @p268 int, @p269 int, @p270 int,
@p271 int, @p272 int, @p273 int, @p274 int, @p275 int, @p276 int, @p277 int,
@p278 int, @p279 int, @p280 int, @p281 int, @p282 int, @p283 int, @p284 int,
@p285 int, @p286 int, @p287 int, @p288 int, @p289 int, @p290 int, @p291 int,
@p292 int, @p293 int, @p294 int, @p295 int, @p296 int, @p297 int, @p298 int,
@p299 int, @p300 int, @p301 int, @p302 int, @p303 int, @p304 int, @p305 int,
@p306 int, @p307 int, @p308 int, @p309 int, @p310 int, @p311 int, @p312 int,
@p313 int, @p314 int, @p315 int, @p316 int, @p317 int, @p318 int, @p319 int,
@p320 int, @p321 int, @p322 int, @p323 int, @p324 int, @p325 int, @p326 int,
@p327 int, @p328 int, @p329 int, @p330 int, @p331 int, @p332 int, @p333 int,
@p334 int, @p335 int, @p336 int, @p337 int, @p338 int, @p339 int, @p340 int,
@p341 int, @p342 int, @p343 int, @p344 int, @p345 int, @p346 int, @p347 int,
@p348 int, @p349 int, @p350 int, @p351 int, @p352 int, @p353 int, @p354 int,
@p355 int, @p356 int, @p357 int, @p358 int, @p359 int, @p360 int, @p361 int,
@p362 int, @p363 int, @p364 int, @p365 int, @p366 int, @p367 int, @p368 int,
@p369 int, @p370 int, @p371 int, @p372 int, @p373 int, @p374 int, @p375 int,
@p376 int, @p377 int, @p378 int, @p379 int, @p380 int, @p381 int, @p382 int,
@p383 int, @p384 int, @p385 int, @p386 int, @p387 int, @p388 int, @p389 int,
@p390 int, @p391 int, @p392 int, @p393 int, @p394 int, @p395 int, @p396 int,
@p397 int, @p398 int, @p399 int, @p400 int, @p401 int, @p402 int, @p403 int,
@p404 int, @p405 int, @p406 int, @p407 int, @p408 int, @p409 int, @p410 int,
@p411 int, @p412 int, @p413 int, @p414 int, @p415 int, @p416 int, @p417 int,
@p418 int, @p419 int, @p420 int, @p421 int, @p422 int, @p423 int, @p424 int,
@p425 int, @p426 int, @p427 int, @p428 int, @p429 int, @p430 int, @p431 int,
@p432 int, @p433 int, @p434 int, @p435 int, @p436 int, @p437 int, @p438 int,
@p439 int, @p440 int, @p441 int, @p442 int, @p443 int, @p444 int, @p445 int,
@p446 int, @p447 int, @p448 int, @p449 int, @p450 int, @p451 int, @p452 int,
@p453 int, @p454 int, @p455 int, @p456 int, @p457 int, @p458 int, @p459 int,
@p460 int, @p461 int, @p462 int, @p463 int, @p464 int, @p465 int, @p466 int,
@p467 int, @p468 int, @p469 int, @p470 int, @p471 int, @p472 int, @p473 int,
@p474 int, @p475 int, @p476 int, @p477 int, @p478 int, @p479 int, @p480 int,
@p481 int, @p482 int, @p483 int, @p484 int, @p485 int, @p486 int, @p487 int,
@p488 int, @p489 int, @p490 int, @p491 int, @p492 int, @p493 int, @p494 int,
@p495 int, @p496 int, @p497 int, @p498 int, @p499 int, @p500 int, @p501 int,
@p502 int, @p503 int, @p504 int, @p505 int, @p506 int, @p507 int, @p508 int,
@p509 int, @p510 int, @p511 int, @p512 int, @p513 int, @p514 int, @p515 int,
@p516 int, @p517 int, @p518 int, @p519 int, @p520 int, @p521 int, @p522 int,
@p523 int, @p524 int, @p525 int, @p526 int, @p527 int, @p528 int, @p529 int,
@p530 int, @p531 int, @p532 int, @p533 int, @p534 int, @p535 int, @p536 int,
@p537 int, @p538 int, @p539 int, @p540 int, @p541 int, @p542 int, @p543 int,
@p544 int, @p545 int, @p546 int, @p547 int, @p548 int, @p549 int, @p550 int,
@p551 int, @p552 int, @p553 int, @p554 int, @p555 int, @p556 int, @p557 int,
@p558 int, @p559 int, @p560 int, @p561 int, @p562 int, @p563 int, @p564 int,
@p565 int, @p566 int, @p567 int, @p568 int, @p569 int, @p570 int, @p571 int,
@p572 int, @p573 int, @p574 int, @p575 int, @p576 int, @p577 int, @p578 int,
@p579 int, @p580 int, @p581 int, @p582 int, @p583 int, @p584 int, @p585 int,
@p586 int, @p587 int, @p588 int, @p589 int, @p590 int, @p591 int, @p592 int,
@p593 int, @p594 int, @p595 int, @p596 int, @p597 int, @p598 int, @p599 int,
@p600 int, @p601 int, @p602 int, @p603 int, @p604 int, @p605 int, @p606 int,
@p607 int, @p608 int, @p609 int, @p610 int, @p611 int, @p612 int, @p613 int,
@p614 int, @p615 int, @p616 int, @p617 int, @p618 int, @p619 int, @p620 int,
@p621 int, @p622 int, @p623 int, @p624 int, @p625 int, @p626 int, @p627 int,
@p628 int, @p629 int, @p630 int, @p631 int, @p632 int, @p633 int, @p634 int,
@p635 int, @p636 int, @p637 int, @p638 int, @p639 int, @p640 int, @p641 int,
@p642 int, @p643 int, @p644 int, @p645 int, @p646 int, @p647 int, @p648 int,
@p649 int, @p650 int, @p651 int, @p652 int, @p653 int, @p654 int, @p655 int,
@p656 int, @p657 int, @p658 int, @p659 int, @p660 int, @p661 int, @p662 int,
@p663 int, @p664 int, @p665 int, @p666 int, @p667 int, @p668 int, @p669 int,
@p670 int, @p671 int, @p672 int, @p673 int, @p674 int, @p675 int, @p676 int,
@p677 int, @p678 int, @p679 int, @p680 int, @p681 int, @p682 int, @p683 int,
@p684 int, @p685 int, @p686 int, @p687 int, @p688 int, @p689 int, @p690 int,
@p691 int, @p692 int, @p693 int, @p694 int, @p695 int, @p696 int, @p697 int,
@p698 int, @p699 int, @p700 int, @p701 int, @p702 int, @p703 int, @p704 int,
@p705 int, @p706 int, @p707 int, @p708 int, @p709 int, @p710 int, @p711 int,
@p712 int, @p713 int, @p714 int, @p715 int, @p716 int, @p717 int, @p718 int,
@p719 int, @p720 int, @p721 int, @p722 int, @p723 int, @p724 int, @p725 int,
@p726 int, @p727 int, @p728 int, @p729 int, @p730 int, @p731 int, @p732 int,
@p733 int, @p734 int, @p735 int, @p736 int, @p737 int, @p738 int, @p739 int,
@p740 int, @p741 int, @p742 int, @p743 int, @p744 int, @p745 int, @p746 int,
@p747 int, @p748 int, @p749 int, @p750 int, @p751 int, @p752 int, @p753 int,
@p754 int, @p755 int, @p756 int, @p757 int, @p758 int, @p759 int, @p760 int,
@p761 int, @p762 int, @p763 int, @p764 int, @p765 int, @p766 int, @p767 int,
@p768 int, @p769 int, @p770 int, @p771 int, @p772 int, @p773 int, @p774 int,
@p775 int, @p776 int, @p777 int, @p778 int, @p779 int, @p780 int, @p781 int,
@p782 int, @p783 int, @p784 int, @p785 int, @p786 int, @p787 int, @p788 int,
@p789 int, @p790 int, @p791 int, @p792 int, @p793 int, @p794 int, @p795 int,
@p796 int, @p797 int, @p798 int, @p799 int, @p800 int, @p801 int, @p802 int,
@p803 int, @p804 int, @p805 int, @p806 int, @p807 int, @p808 int, @p809 int,
@p810 int, @p811 int, @p812 int, @p813 int, @p814 int, @p815 int, @p816 int,
@p817 int, @p818 int, @p819 int, @p820 int, @p821 int, @p822 int, @p823 int,
@p824 int, @p825 int, @p826 int, @p827 int, @p828 int, @p829 int, @p830 int,
@p831 int, @p832 int, @p833 int, @p834 int, @p835 int, @p836 int, @p837 int,
@p838 int, @p839 int, @p840 int, @p841 int, @p842 int, @p843 int, @p844 int,
@p845 int, @p846 int, @p847 int, @p848 int, @p849 int, @p850 int, @p851 int,
@p852 int, @p853 int, @p854 int, @p855 int, @p856 int, @p857 int, @p858 int,
@p859 int, @p860 int, @p861 int, @p862 int, @p863 int, @p864 int, @p865 int,
@p866 int, @p867 int, @p868 int, @p869 int, @p870 int, @p871 int, @p872 int,
@p873 int, @p874 int, @p875 int, @p876 int, @p877 int, @p878 int, @p879 int,
@p880 int, @p881 int, @p882 int, @p883 int, @p884 int, @p885 int, @p886 int,
@p887 int, @p888 int, @p889 int, @p890 int, @p891 int, @p892 int, @p893 int,
@p894 int, @p895 int, @p896 int, @p897 int, @p898 int, @p899 int, @p900 int,
@p901 int, @p902 int, @p903 int, @p904 int, @p905 int, @p906 int, @p907 int,
@p908 int, @p909 int, @p910 int, @p911 int, @p912 int, @p913 int, @p914 int,
@p915 int, @p916 int, @p917 int, @p918 int, @p919 int, @p920 int, @p921 int,
@p922 int, @p923 int, @p924 int, @p925 int, @p926 int, @p927 int, @p928 int,
@p929 int, @p930 int, @p931 int, @p932 int, @p933 int, @p934 int, @p935 int,
@p936 int, @p937 int, @p938 int, @p939 int, @p940 int, @p941 int, @p942 int,
@p943 int, @p944 int, @p945 int, @p946 int, @p947 int, @p948 int, @p949 int,
@p950 int, @p951 int, @p952 int, @p953 int, @p954 int, @p955 int, @p956 int,
@p957 int, @p958 int, @p959 int, @p960 int, @p961 int, @p962 int, @p963 int,
@p964 int, @p965 int, @p966 int, @p967 int, @p968 int, @p969 int, @p970 int,
@p971 int, @p972 int, @p973 int, @p974 int, @p975 int, @p976 int, @p977 int,
@p978 int, @p979 int, @p980 int, @p981 int, @p982 int, @p983 int, @p984 int,
@p985 int, @p986 int, @p987 int, @p988 int, @p989 int, @p990 int, @p991 int,
@p992 int, @p993 int, @p994 int, @p995 int, @p996 int, @p997 int, @p998 int,
@p999 int, @p1000 int
as
declare @c int;
select @c = count(*)
from somedata
where avalue in ( 1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11,
@p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23,
@p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35,
@p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47,
@p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59,
@p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71,
@p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83,
@p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92, @p93, @p94, @p95,
@p96, @p97, @p98, @p99, @p100, @p101, @p102, @p103, @p104, @p105, @p106,
@p107, @p108, @p109, @p110, @p111, @p112, @p113, @p114, @p115, @p116, @p117,
@p118, @p119, @p120, @p121, @p122, @p123, @p124, @p125, @p126, @p127, @p128,
@p129, @p130, @p131, @p132, @p133, @p134, @p135, @p136, @p137, @p138, @p139,
@p140, @p141, @p142, @p143, @p144, @p145, @p146, @p147, @p148, @p149, @p150,
@p151, @p152, @p153, @p154, @p155, @p156, @p157, @p158, @p159, @p160, @p161,
@p162, @p163, @p164, @p165, @p166, @p167, @p168, @p169, @p170, @p171, @p172,
@p173, @p174, @p175, @p176, @p177, @p178, @p179, @p180, @p181, @p182, @p183,
@p184, @p185, @p186, @p187, @p188, @p189, @p190, @p191, @p192, @p193, @p194,
@p195, @p196, @p197, @p198, @p199, @p200, @p201, @p202, @p203, @p204, @p205,
@p206, @p207, @p208, @p209, @p210, @p211, @p212, @p213, @p214, @p215, @p216,
@p217, @p218, @p219, @p220, @p221, @p222, @p223, @p224, @p225, @p226, @p227,
@p228, @p229, @p230, @p231, @p232, @p233, @p234, @p235, @p236, @p237, @p238,
@p239, @p240, @p241, @p242, @p243, @p244, @p245, @p246, @p247, @p248, @p249,
@p250, @p251, @p252, @p253, @p254, @p255, @p256, @p257, @p258, @p259, @p260,
@p261, @p262, @p263, @p264, @p265, @p266, @p267, @p268, @p269, @p270, @p271,
@p272, @p273, @p274, @p275, @p276, @p277, @p278, @p279, @p280, @p281, @p282,
@p283, @p284, @p285, @p286, @p287, @p288, @p289, @p290, @p291, @p292, @p293,
@p294, @p295, @p296, @p297, @p298, @p299, @p300, @p301, @p302, @p303, @p304,
@p305, @p306, @p307, @p308, @p309, @p310, @p311, @p312, @p313, @p314, @p315,
@p316, @p317, @p318, @p319, @p320, @p321, @p322, @p323, @p324, @p325, @p326,
@p327, @p328, @p329, @p330, @p331, @p332, @p333, @p334, @p335, @p336, @p337,
@p338, @p339, @p340, @p341, @p342, @p343, @p344, @p345, @p346, @p347, @p348,
@p349, @p350, @p351, @p352, @p353, @p354, @p355, @p356, @p357, @p358, @p359,
@p360, @p361, @p362, @p363, @p364, @p365, @p366, @p367, @p368, @p369, @p370,
@p371, @p372, @p373, @p374, @p375, @p376, @p377, @p378, @p379, @p380, @p381,
@p382, @p383, @p384, @p385, @p386, @p387, @p388, @p389, @p390, @p391, @p392,
@p393, @p394, @p395, @p396, @p397, @p398, @p399, @p400, @p401, @p402, @p403,
@p404, @p405, @p406, @p407, @p408, @p409, @p410, @p411, @p412, @p413, @p414,
@p415, @p416, @p417, @p418, @p419, @p420, @p421, @p422, @p423, @p424, @p425,
@p426, @p427, @p428, @p429, @p430, @p431, @p432, @p433, @p434, @p435, @p436,
@p437, @p438, @p439, @p440, @p441, @p442, @p443, @p444, @p445, @p446, @p447,
@p448, @p449, @p450, @p451, @p452, @p453, @p454, @p455, @p456, @p457, @p458,
@p459, @p460, @p461, @p462, @p463, @p464, @p465, @p466, @p467, @p468, @p469,
@p470, @p471, @p472, @p473, @p474, @p475, @p476, @p477, @p478, @p479, @p480,
@p481, @p482, @p483, @p484, @p485, @p486, @p487, @p488, @p489, @p490, @p491,
@p492, @p493, @p494, @p495, @p496, @p497, @p498, @p499, @p500, @p501, @p502,
@p503, @p504, @p505, @p506, @p507, @p508, @p509, @p510, @p511, @p512, @p513,
@p514, @p515, @p516, @p517, @p518, @p519, @p520, @p521, @p522, @p523, @p524,
@p525, @p526, @p527, @p528, @p529, @p530, @p531, @p532, @p533, @p534, @p535,
@p536, @p537, @p538, @p539, @p540, @p541, @p542, @p543, @p544, @p545, @p546,
@p547, @p548, @p549, @p550, @p551, @p552, @p553, @p554, @p555, @p556, @p557,
@p558, @p559, @p560, @p561, @p562, @p563, @p564, @p565, @p566, @p567, @p568,
@p569, @p570, @p571, @p572, @p573, @p574, @p575, @p576, @p577, @p578, @p579,
@p580, @p581, @p582, @p583, @p584, @p585, @p586, @p587, @p588, @p589, @p590,
@p591, @p592, @p593, @p594, @p595, @p596, @p597, @p598, @p599, @p600, @p601,
@p602, @p603, @p604, @p605, @p606, @p607, @p608, @p609, @p610, @p611, @p612,
@p613, @p614, @p615, @p616, @p617, @p618, @p619, @p620, @p621, @p622, @p623,
@p624, @p625, @p626, @p627, @p628, @p629, @p630, @p631, @p632, @p633, @p634,
@p635, @p636, @p637, @p638, @p639, @p640, @p641, @p642, @p643, @p644, @p645,
@p646, @p647, @p648, @p649, @p650, @p651, @p652, @p653, @p654, @p655, @p656,
@p657, @p658, @p659, @p660, @p661, @p662, @p663, @p664, @p665, @p666, @p667,
@p668, @p669, @p670, @p671, @p672, @p673, @p674, @p675, @p676, @p677, @p678,
@p679, @p680, @p681, @p682, @p683, @p684, @p685, @p686, @p687, @p688, @p689,
@p690, @p691, @p692, @p693, @p694, @p695, @p696, @p697, @p698, @p699, @p700,
@p701, @p702, @p703, @p704, @p705, @p706, @p707, @p708, @p709, @p710, @p711,
@p712, @p713, @p714, @p715, @p716, @p717, @p718, @p719, @p720, @p721, @p722,
@p723, @p724, @p725, @p726, @p727, @p728, @p729, @p730, @p731, @p732, @p733,
@p734, @p735, @p736, @p737, @p738, @p739, @p740, @p741, @p742, @p743, @p744,
@p745, @p746, @p747, @p748, @p749, @p750, @p751, @p752, @p753, @p754, @p755,
@p756, @p757, @p758, @p759, @p760, @p761, @p762, @p763, @p764, @p765, @p766,
@p767, @p768, @p769, @p770, @p771, @p772, @p773, @p774, @p775, @p776, @p777,
@p778, @p779, @p780, @p781, @p782, @p783, @p784, @p785, @p786, @p787, @p788,
@p789, @p790, @p791, @p792, @p793, @p794, @p795, @p796, @p797, @p798, @p799,
@p800, @p801, @p802, @p803, @p804, @p805, @p806, @p807, @p808, @p809, @p810,
@p811, @p812, @p813, @p814, @p815, @p816, @p817, @p818, @p819, @p820, @p821,
@p822, @p823, @p824, @p825, @p826, @p827, @p828, @p829, @p830, @p831, @p832,
@p833, @p834, @p835, @p836, @p837, @p838, @p839, @p840, @p841, @p842, @p843,
@p844, @p845, @p846, @p847, @p848, @p849, @p850, @p851, @p852, @p853, @p854,
@p855, @p856, @p857, @p858, @p859, @p860, @p861, @p862, @p863, @p864, @p865,
@p866, @p867, @p868, @p869, @p870, @p871, @p872, @p873, @p874, @p875, @p876,
@p877, @p878, @p879, @p880, @p881, @p882, @p883, @p884, @p885, @p886, @p887,
@p888, @p889, @p890, @p891, @p892, @p893, @p894, @p895, @p896, @p897, @p898,
@p899, @p900, @p901, @p902, @p903, @p904, @p905, @p906, @p907, @p908, @p909,
@p910, @p911, @p912, @p913, @p914, @p915, @p916, @p917, @p918, @p919, @p920,
@p921, @p922, @p923, @p924, @p925, @p926, @p927, @p928, @p929, @p930, @p931,
@p932, @p933, @p934, @p935, @p936, @p937, @p938, @p939, @p940, @p941, @p942,
@p943, @p944, @p945, @p946, @p947, @p948, @p949, @p950, @p951, @p952, @p953,
@p954, @p955, @p956, @p957, @p958, @p959, @p960, @p961, @p962, @p963, @p964,
@p965, @p966, @p967, @p968, @p969, @p970, @p971, @p972, @p973, @p974, @p975,
@p976, @p977, @p978, @p979, @p980, @p981, @p982, @p983, @p984, @p985, @p986,
@p987, @p988, @p989, @p990, @p991, @p992, @p993, @p994, @p995, @p996, @p997,
@p998, @p999, @p1000 )


--
Tony Rogerson, SQL Server MVP

http://sqlblogcasts.com/blogs/tonyrogerson

[Ramblings from the field from a SQL consultant]

http://sqlserverfaq.com

[UK SQL User Community]


"--CELKO--" <jcelko212@eart hlink.netwrote in message
news:1185890406 .552369.7620@57 g2000hsv.google groups.com...

>>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.
>
>