| Author |
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
| |
| Mike Epprecht \(SQL MVP\) 2005-12-16, 8:24 pm |
| 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
>
| |
| DA Morgan 2005-12-16, 8:24 pm |
| 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)
| |
| David Portas 2005-12-17, 9:23 am |
| 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
| |
| David Portas 2005-12-21, 7:23 am |
| 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
--
|
|
|
|