String / Variable Problem - Dynamic Table Name -- sql server (alternate)

oj
1/14/2004 10:49:39 AM

DECLARE
@AddressID int,
@ProgClient (varchar(10),
@Table varchar(10)
,@sql nvarchar(1000)

SET @Table = 'i2b_' + @ProgClient + '_contact'

SET @sql='SET @AddressID = (SELECT AddressID FROM '+@Table+' WHERE ContactID
= @ContactID)'
exec sp_executesql @sql,N'@ContactID int, @AddressID int
output',@ContactID,@AddressID output

select @AddressID

--
-oj
http://www.rac4sql.net

[quoted text, click to view]

Martin Feuersteiner
1/14/2004 5:44:54 PM

Hi

I'm grateful for any light you can shed on this!!

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.

Storing all contacts in one table with an identifier of e.g. 'ash' or 'ted'
for each record is not possible.
Returning the value from the dynamic Query is no problem but I don't know
how to assign it to a variable.
When I try doing this it either runs into problems with evaluating the
variables or doesn't retuen anything at all e.g. if I say at the end 'Print
@AddressID'. The variable remains empty.

How can I do something like:

DECLARE
@AddressID int,
@ProgClient (varchar(10),
@Table varchar(10)

Note: @Prog is a string e.g. 'ash' or 'ted'

SET @Table = 'i2b_ + @ProgClient + '_contact

SET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)

sql server (alternate) : String / Variable Problem - Dynamic Table Name