How to pass table names to a Stored procedure

Hi all,
Seems like a fundamental question to me but I dont have a definite
answer for it, Gurus please enlighten me.

I have a table 'Table1' whose structure changes dynamically  based on
some configuration values from another table. This table is being used
by a program, It was initially used by this program which ran as a
single task (executing at only a specific interval) but now the program
has to be run mutiple times some coinciding with each othe - which
meant that table structure will change as 2 programs are running
simultaneously... and therefore I have decided to use seperate table
names that each has a structure of its now.

I use this table name 'Table1' in about 10-15 stored procedures and
UDF's

to make the long story short: Since I will not know which table I will
be using in the program I want to pass the table name as an argument to
the SP and UDF's and then access this param in the
'select's/updates/inserts' - but this doesn't work unless I use Dynamic
SQL.

Is there any other way of passing table names as parameters and then
using then in the procs?

any ideas will be really helpful.
adi

Hi

Dynamic SQL is your answer here.
http://www.sommarskog.se/dynamic_sql.html

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"adi" <Adityanad@gmail.com> wrote in message
news:1134759987.633298.274030@g49g2000cwa.googlegroups.com...
> Hi all,
>    Seems like a fundamental question to me but I dont have a definite
> answer for it, Gurus please enlighten me.
>
> I have a table 'Table1' whose structure changes dynamically  based on
> some configuration values from another table. This table is being used
> by a program, It was initially used by this program which ran as a
> single task (executing at only a specific interval) but now the program
> has to be run mutiple times some coinciding with each othe - which
> meant that table structure will change as 2 programs are running
> simultaneously... and therefore I have decided to use seperate table
> names that each has a structure of its now.
>
> I use this table name 'Table1' in about 10-15 stored procedures and
> UDF's
>
> to make the long story short: Since I will not know which table I will
> be using in the program I want to pass the table name as an argument to
> the SP and UDF's and then access this param in the
> 'select's/updates/inserts' - but this doesn't work unless I use Dynamic
> SQL.
>
> Is there any other way of passing table names as parameters and then
> using then in the procs?
>
> any ideas will be really helpful.
> adi
>

adi wrote:

> I have a table 'Table1' whose structure changes dynamically  based on
> some configuration values from another table.
>
> any ideas will be really helpful.
> adi

Redesign the application. This is absurd.

Well you asked.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

adi wrote:

> Hi all,
>     Seems like a fundamental question to me but I dont have a definite
> answer for it, Gurus please enlighten me.
>
> I have a table 'Table1' whose structure changes dynamically  based on
> some configuration values from another table. This table is being used
> by a program, It was initially used by this program which ran as a
> single task (executing at only a specific interval) but now the program
> has to be run mutiple times some coinciding with each othe - which
> meant that table structure will change as 2 programs are running
> simultaneously... and therefore I have decided to use seperate table
> names that each has a structure of its now.
>
> I use this table name 'Table1' in about 10-15 stored procedures and
> UDF's
>
> to make the long story short: Since I will not know which table I will
> be using in the program I want to pass the table name as an argument to
> the SP and UDF's and then access this param in the
> 'select's/updates/inserts' - but this doesn't work unless I use Dynamic
> SQL.
>
> Is there any other way of passing table names as parameters and then
> using then in the procs?
>
> any ideas will be really helpful.
> adi

I have to agree with Daniel. This sounds like a mess and not the way to
build any kind of scalable or reliable solution. Dynamic SQL may buy
you some time but I'd only go that route as a last resort and only as a
stop-gap for some better plan.

--
David Portas
SQL Server MVP
--

Advice noted. I will re-design the app. Meanwhile can anyone of you
give me some nove ideas on how the whole thing can be scalable.
Frankly I am new to SQL Server and new to 'planning'

:-)

thanks
adi

adi  wrote:
> Advice noted. I will re-design the app. Meanwhile can anyone of you
> give me some nove ideas on how the whole thing can be scalable.
> Frankly I am new to SQL Server and new to 'planning'
>
> :-)
>
> thanks
> adi

Did you read the article in the link that Mike posted? That answers
your original question.

What I'm saying is that any efforts to support such a design would be
better invested in replacing it. If you don't have anyone with
experience of database design then you should hire someone because I
think you'll need more help than you can get in a newsgroup. If you
have a more specific question then come back and give us some details.

--
David Portas
SQL Server MVP
--