469,950 Members | 2,053 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

String / Variable Problem - Dynamic Table Name

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)

Jul 20 '05 #1
2 29542
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)

Jul 20 '05 #2
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)

.

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

134 posts views Thread by James A. Donald | last post: by
1 post views Thread by adit | last post: by
13 posts views Thread by Ramon F Herrera | last post: by
7 posts views Thread by bprocopio | last post: by
2 posts views Thread by Looch | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.