Thanks for your help Dave.
I've to admit, it's an unusual design but I've multiple contact tables named
e.g. i2b_ash_contact or i2b_ted_contact.
i2b_ and _contact are static but the middle part is dynamic. Reason for this
design is that I've a web application in which someone logs in and depending
on the login, a specific table for this login is used.
Storing all contacts in one table with an identifier of e.g. ash ted for
each record is not possible as from a security point, I need to keep
contacts for each login in a separate table.
So here we are again at the beginning. How can I do something like:
DECLARE
@AddressID int,
@ProgClient (varchar(10),
@Table varchar(10)
-- @Prog is e.g. 'ash'
SET @Table = 'i2b_ + @ProgClient + '_contact
SET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)
"David Portas" <RE****************************@acm.org> wrote in message
news:V9********************@giganews.com...
I need to evaluate @Table first otherwise it can't find it.
Unless you make it just one table and put the Progclient value in as a
column. Seems like an unusual design to have multiple tables of Contact
details.
CREATE TABLE Contacts (progclient VARCHAR(10), contactid INTEGER,
addressid INTEGER NOT NULL, ... PRIMARY KEY (progclient, contactid))
SET @AddressID =
(SELECT AddressID
FROM Contacts
WHERE ContactID = @ContactID
AND progclient = @ProgClient)
--
David Portas
------------
Please reply only to the newsgroup
--