🚀 go-pugleaf

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" <theintrepidfox@hotmail.com> wrote in message
news:bu3v6m$28m$1@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)
>
>
>