Change table name on the fly in a store proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Change table name on the fly in a store proc

Author

Topic

kdaley
Starting Member

2 Posts

Posted - 2004-01-21 : 12:04:26

I need to be able to pass a parameter into a stored procedure that will allow me to change tables on the fly. Example: I have 10 tables that will require data insertion. The table that will require insertion will be chosen and passed into the stored procedure. IS this possible? I get an error asking me to declare @store? Any help would be great..thanks
See below:
CREATE Procedure spEmailCapture
(
@emailAddress varchar(150),
@store varchar(50))

As
declare @now datetime
select @now = getdate()

begin transaction
insert into @store (emailAddress, dateEntered)
values(@emailAddress, @now)
if @@ERROR = 0
commit
else
rollback
return
GO

Many thanks to those that take the Time

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-21 : 12:14:44

Search the forums for dynamic sql examples.

BTW, what you are doing is highly not recommended. You will see performance problem as compared to if you explicitly wrote out 10 different stored procedures for your 10 table inserts.

Tara

kdaley
Starting Member

2 Posts

Posted - 2004-01-21 : 13:02:13

Thanks Tara..I decided to change my approach.

Many thanks to those that take the Time

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-21 : 13:40:39

you should not put data for each store in a different table. All data common to your stores should be stored in 1 table in your database, with a column called "Store" that indicates which store that data is for.

i.e., instead of

Store1
-----
A
B
C

Store2
------
D
E
F

Store3
------
G

you should have 1 table, called Stores, with 1 row per store, and any related tables with all data should include a "Store" column.


Stores:
Store
------
1
2
3

Store Data:
Store  Value
----   -----
1      A
1      B
1      C
2      D
2      E
2      F
3      G

Then selecting from or inserting based on a particular store is trivial and doesn't require dynamic SQL, and you can unlimited stores.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources