pass table name as a parameter in stored procedure

Question

pass table name as a parameter in stored procedure

Viewing 4 posts - 16 through 18 (of 18 total)

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

mjbkm Ten Centuries Points: 1104 More actions · Answer 1

OK - Thanks. What I will do is start from the beginning and post a new question about my indexes first to make sure I have that setup correct.

mjbkm Ten Centuries Points: 1104 More actions · Answer 2

mjbkm

Ten Centuries

Points: 1104

September 25, 2011 at 5:28 pm

#1386254

I'll check it out. Thanks for everything!

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

Seggerman-675349 (4/17/2008)
I am not sure of the feasibility of this but I would recommend writing your program to use a synonym for the table name - then use dynamic sql to create the synonym from whatever the table name is. Then DROP the synonym at the end. That way the logic of the stored procedure is static and only the synonym creation is dynamic.

And if two instances of the same proc run at the same time, what then? 😉

And, yeah... I realize the post is more than 3 years old but since this thread has been reactivated, I wanted to make sure folks knew this is likely a bad idea.

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

jessie 20540 Grasshopper Points: 22 More actions · Answer 4

you may try this:

create a table type and define the table structure;

declare the SP that has a parameter of tble type;

declare a table type variable and reference the table type;

insert the data from your table to the variable;

Now you can pass the variable to the procedure and execute it.

Here is the link by Pinaldave

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

Good luck

Jessie