How to pass table names to a Stored procedure

[adi]

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

[Mike Epprecht \(SQL MVP\)]

Hi

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

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

IM: mi**@epprecht.net

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

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

"adi" <Ad*******@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.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

[DA Morgan]

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
da******@x.washington.edu
(replace x with u to respond)

[David Portas]

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

[adi]

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

[David Portas]

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