Table Input Parameter Question

Question

Table Input Parameter Question

Khades

SSCrazy

Points: 2067
More actions
April 5, 2011 at 3:50 pm

#235084

Can you use a table valued input parameter to a stored procedure without having to create a user defined data type first?

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

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

Khades (4/5/2011)
Can you use a table valued input parameter to a stored procedure without having to create a user defined data type first?

First off all Why? you need to avoid the user defined data type.

Table-Valued Parameters : is defined as

Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

After this i think you might have get some idea what is Table-Valued Parameter.

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

parthi-1705 (4/5/2011)
First off all Why? you need to avoid the user defined data type.

Why?

Table-Valued Parameters : is defined as
Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared by using user-defined table types.

You tell him that he should avoid user-defined types, then you tell him that he needs to use a user-defined type.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

It's a "language barrier" thing, Gail. Replace the question mark with the word "do" and you'll see what I mean.

Lot's of folks automatically put a question mark after the word "why" and they don't really mean for it to be the end of the sentance. Yeah... it took me a while to get used to it, too.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

GilaMonster (4/5/2011)
parthi-1705 (4/5/2011)
First off all Why? you need to avoid the user defined data type.
Why?

I think you have missunderstand what i have asked.I asked him why you need to avoid user defined data type.

Why you need to avoid the user defined data type ?

Thanks
Parthi

GSquared SSC Guru Points: 260824 More actions · Answer 5

Khades (4/5/2011)
Can you use a table valued input parameter to a stored procedure without having to create a user defined data type first?

Nope.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Khades SSCrazy Points: 2067 More actions · Answer 6

Thanks everyone!

It wasn't an absolute requirement to avoid creating a new data type. I just wanted to avoid it if I could.

Apparently I had table variables and table valued input types mixed up.