table name as input param

Question

table name as input param

Snargables

SSCrazy Eights

Points: 8855
More actions
January 11, 2013 at 7:37 am

#287470

Doesnt sql server 2008 have a table type. i need to pass a table name into a proc.

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Snargables (1/11/2013)
Doesnt sql server 2008 have a table type. i need to pass a table name into a proc.

I have a bad feeling that you want to pass in your name so you can execute some dynamic sql against this table. Generic procedures are possible in sql server but they have lots of reason to avoid them like the plague and almost no real reason to use them.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Snargables SSCrazy Eights Points: 8855 More actions · Answer 2

u'r right. It's more of an excercise for my team. It wont be used in production environment. Theying to convert something they do everyday into a "generic" proc call.

So, i can pass a table name to a proc. Do i have to use dynbamic sql to select from it? i thought 2008 allowed u to pass in a table and reference it w/o using dynamic sql.

Snargables SSCrazy Eights Points: 8855 More actions · Answer 3

i think i found it.

http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Remember that doesn't mean you suddenly have any table in your system. You have to define TVP as a type. Then you can pass that table to a proc as parameter. It has some pretty awesome uses.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Snargables (1/11/2013)
i think i found it.
http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/

If I read you correctly, you want to pass the name of a table, any table, to a stored procedure and run a query against that table. Correct?

Table Valued Parameters aren't going to help you. You will need to write dynamic SQL in your stored procedure to query against a randomly desired table.