Hi all,
I have a set of tables for different users of my application.
Eg. user name = jj
table for customers = jj_customers

user name = kk
table for customers = kk_customers

How can I pass the prefix of the table name to a stored procedure?
Eg stored procedure = select * <prefix>_customers

Is there a way of passing in the prefix as a string and then append it
to the table name (string manipulation?)?

Cheers
Jules

You will have to use dynamic sql.

e.g.
create proc usp
@c sysname
as
declare @sql nvarchar(4000)
set @sql='select * from '+@c+'_customers'
exec(@sql)
go


"Jullietta" <j.jung (AT) gmbsupport (DOT) com> wrote

Why would you want to do this? Assuming all your table structures are
the same, just combine them in one table and add the user_name column
to it. Then you can do:

SELECT ...
FROM Customers
WHERE user_name = @user_name

--
David Portas
SQL Server MVP
--

In a properly designed database, there is "set of tables"; tables are
sets. Sets are made of elments of the same kind of things. There
ought to be single table for Customers.

This design flaw is called attribute splitting. You have taken an
attribute -- customer identity -- and made its values into tables.


No, SQL is a compiled language. You would have to use (ugghh!) dynamic
SQL which is slow, proprietary and dangerous.

Get a book on data modeling and DB design; you have completely missed
the foundations.

Thanx oj for introducing me to dynamic sql.
Yes, it is a poorly designed DB.... what can I do ?? I've been hired to
work with this material without modifying it....

Cheers


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Send them a registered letter explaining why what they are doing is
wrong and walk away. If you would like support for the legal actions
that might occur afterwards, I will offer myself as an expert witness.

I have a friend (now a lawyer in Los Angeles)who was the database person
at MSA. She found herself in a situation where her boss thought that
currency exchange were fixed values. Her objections were found in
discovery after the lawsuit. It protected her from being the goat when
the company was hit with triple damages.

That is what professionals do.

I get called into clean up database disasters. This year, a bad design
crippled and might have killed several children in Africa by messing up
the drug orders for a relief program. You do not get to run to the
local Walgreeen's to make up the missing drugs in a shipment.

Quit acting and thinking like a whining kid with an MS certification and
start being a professional. Did you know it takes SIX YEARS to become a
union journeyman carpenter in New York State? But only six weeks to
cram for an MS database exam.. and whose failures can kill more people??

Quit whining and start being a real professional. Or find another
career.

--CELKO--
Please post DDL in a human-readable format and not a machne-generated
one. This way people do not have to guess what the keys, constraints,
Declarative Referential Integrity, datatypes, etc. in your schema are.
Sample data is also a good idea, along with clear specifications.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

On Sun, 12 Dec 2004 19:09:28 -0800, Jullietta Jung wrote:

Hi Jullietta,

First, create ONE table Customers, with all columns of you current
xx_customers tables, plus one extra column (Company). The extra column
will be part of the primary key, together with the columns that now make
up the primary key. Also, add a Companies table and create a foreign key
constraint from the companny column in Customers to the Companies table.

If you can, change all references to xx_Customers to use the new Customers
table instead. As a temporary fix, you could prepare xx_Customers views
that select all columns except Company from Customers, with a WHERE clause
to limit the selection to "Company = 'xx'".

Schedule some down time and use this time slot to move all data from the
current xx_Customers tables to the new Customers table. Then drop the old
tables, create the replacement views (if you need them) and activate the
changed version of the code.

If your boss refuses to take the time for this, then follow Joe's advice.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)