String / Variable Problem - Dynamic Table Name

[Martin Feuersteiner]

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)

[oj]

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
"Martin Feuersteiner" <th************@hotmail.com> wrote in message
news:bu**********@hercules.btinternet.com...

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)

[Martin Feuersteiner]

Thanks Bob! That does the trick!!
Have a nice day!!!
"Bob" <BR***@DLBabson.Com> wrote in message
news:09****************************@phx.gbl...

Provided you are passing in the @ContractID somewhere
else outside your example, your select statement only
returns one record, and the user has at least
dbdatareader access to table, I think this should do the
trick.

I'm not sure if it's the most efficient design, but it
should work:

DECLARE
@SQL varchar(1000),
@AddressID int,
@ProgClient varchar(10),
@Table varchar(100)

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

SET @SQL = 'SELECT AddressID FROM ' + @Table + ' WHERE
ContactID = ' + CONVERT(varchar, @ContactID)

CREATE TABLE #TmpAddressID
(AddressID int)

INSERT INTO #TmpAddressID
EXEC(@SQL)

SET @AddressID = (SELECT AddressID FROM #TmpAddressID)

PRINT @AddressID

--Not really needed, but good clean up
DROP TABLE #TmpAddressID

-----Original Message-----
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)

.