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]

Re: String / Variable Problem - Dynamic Table Name

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'@Contact ID int, @AddressID int
output',@Contac tID,@AddressID output

select @AddressID


--
-oj

http://www.rac4sql.net

"Martin Feuersteiner" <theintrepidfox @hotmail.com> wrote in message
news:bu3v6m$28m $1@hercules.bti nternet.com...[color=blue]
> 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[/color]
named[color=blue]
> 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[/color]
'ted'[color=blue]
> 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[/color]
'Print[color=blue]
> @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 =[/color]
@ContactID)[color=blue]
>
>
>[/color]

[Martin Feuersteiner]

Re: String / Variable Problem - Dynamic Table Name

Thanks Bob! That does the trick!!
Have a nice day!!!


"Bob" <BRoux@DLBabson .Com> wrote in message
news:09a801c3da d0$3cebe090$a00 1280a@phx.gbl.. .[color=blue]
> 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
>[color=green]
> >-----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[/color]
> contact tables named[color=green]
> >e.g. i2b_ash_contact or i2b_ted_contact .
> >'i2b_' and '_contact' are static but the middle part is[/color]
> dynamic.[color=green]
> >
> >Storing all contacts in one table with an identifier of[/color]
> e.g. 'ash' or 'ted'[color=green]
> >for each record is not possible.
> >Returning the value from the dynamic Query is no problem[/color]
> but I don't know[color=green]
> >how to assign it to a variable.
> >When I try doing this it either runs into problems with[/color]
> evaluating the[color=green]
> >variables or doesn't retuen anything at all e.g. if I[/color]
> say at the end 'Print[color=green]
> >@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[/color]
> ContactID = @ContactID)[color=green]
> >
> >
> >
> >.
> >[/color][/color]