passing table data type to SP

Question

Post reply

passing table data type to SP

VT-176769

SSChasing Mays

Points: 637
More actions
June 14, 2006 at 8:50 am

#153041

HI
Is it possible to pass table datatype to stored procedure as parameter??
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

RyanRandall SSChampion Points: 13623 More actions · Answer 1

Nope.

If you describe what you want to do, there might be other options...

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

JeffB Hall of Fame Points: 3541 More actions · Answer 2

You cannot. Temporary tables have scope for the current procedure and any sub procedure so you can do something like the following:

create procedure proc2

as

select sum(col1) from #a

go

create procedure proc1

as

create table #a(col1 int)

insert #a values(1)

insert #a values(2)

exec proc2

go

exec proc1

go

drop procedure proc1

drop procedure proc2

go

VT-176769 SSChasing Mays Points: 637 More actions · Answer 3

This is what I would like to know...actualy this was an interview question I had

1. If a stored procedure is taking a table data type, how it looks?

1. How do you know which index a table is using?

1. If there is failure during updation of certain rows, what will be the state?

JeffB Hall of Fame Points: 3541 More actions · Answer 4

My answers would be:

A stored procedure can't take a table datatype as an argument

Using for what query? A table doesn't use an index, a query does.

The 2 virtual tables available in a trigger are INSERTED and DELETED

None of the updates will be applied.

mmarovic SSC Enthusiast Points: 160 More actions · Answer 5

See: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14259

Eric Wilson SSChasing Mays Points: 611 More actions · Answer 6

There is a trick way to allow a function (procedure) take a rowset (a.k.a. table) as an incoming parameter.

Let's say I had a procedure that I wanted to accept a table, defined as: T{CustomerID, EmployeeID, SalesAmt, TaxAmt}.

Because the table is well defined in advance, you can create the table *first* as an actual table. Then, place an "INSTEAD OF INSERT" trigger on that table to do whatever you would have had the function/procedure do.

To "invoke" the function, you simply insert a set of rows into that table.

This has limitations: (a) it won't be good for producing a result-set; (b) it cannot replace a true function signature--with a true functional approach you could accept N different tables, plus M scalar arguments also; (c) it does not work on "general" tables-- it must be pre-defined. This only works on one, well-defined table definition.