table variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

table variable

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-06-28 : 12:14:34

Hi all,

I was converting oracle code to sql and found that a variable is declared %rowtype
( in oracle %rowtype means say for eg. v_prod_row tab%rowtype
it will declare a variable of entire table col accesed by table.col
i,e. v_prod_row.col1.. so on)

I decided to use #tmp table for this, however the next statement is
a call to function


v_prod_row = get_product_row(cPrId,cLegEnt,cMCent,'O')

followed by stm which is passing entire row to a function


vProduct_type = get_its_product_type(v_prod_row,null,cLegEnt,cMCent,'A')

can I do this in sql using table variable

Can get entire table rec (v_pord_row) into a table variable at one go as above.

Can I pass the entire table record as in second statement (v_prod_row)

any help in moving me in rt direction would be of great help.

I am using sql2005.

Regards,

-Neil

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 12:51:29

Whoa...it does not work that way...there is no implicit ANYTHING in SQL Server

You must implicity define everything...rememeber dynamic Cursors????

Well they are out...as well as ALL Cursors that you encounter

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-28 : 14:27:22

There is not a concept of a Row type in SQL, but there is a TABLE type that you can define using the CREATE TYPE statement. You can define a variable of that table type and pass that to a function using a Table Valued Parameter.

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 15:05:48

Right...ROWTYPE% is an implicit way to dynamically declare variables based on an object implicitly...

S/He must Explicitly define the table variable

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources