Article View: bit.databases.mssql-l
Article #9Re: String / Variable Problem - Dynamic Table Name
From: oj
Date: Wed, 14 Jan 2004 18:49
Date: Wed, 14 Jan 2004 18:49
59 lines
3175 bytes
3175 bytes
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) > > >
Technical Details
Message-ID:
<#fALr8s2DHA.1760@TK2MSFTNGP10.phx.gbl>Path:
arc1-ca.pugleaf.net!archiver1.google.com!news2.google.com!news.maxwell.syr.edu!feed.news.tiscali.de!newsfeed.freenet.de!newsfeed.r-kom.de!newsfeed00.sul.t-online.de!t-online.de!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gblReferences:
<bu3v6m$28m$1@hercules.btinternet.com>Show Full Headers
Complete Article Headers
X-Google-Language: ENGLISH,ASCII-7-bit X-Google-Thread: 10f695,1ea0e1ec7764ef39 X-Google-Attributes: gid10f695,public X-Google-Thread: f816f,1ea0e1ec7764ef39 X-Google-Attributes: gidf816f,public X-Google-Thread: 1165d6,1ea0e1ec7764ef39 X-Google-Attributes: gid1165d6,public X-Google-Thread: f6611,1ea0e1ec7764ef39 X-Google-Attributes: gidf6611,public X-Google-Thread: ff490,1ea0e1ec7764ef39 X-Google-Attributes: gidff490,public X-Google-Thread: 106afd,1ea0e1ec7764ef39 X-Google-Attributes: gid106afd,public X-Google-Thread: 11301e,1ea0e1ec7764ef39 X-Google-Attributes: gid11301e,public X-Google-Thread: 104de1,1ea0e1ec7764ef39 X-Google-Attributes: gid104de1,public X-Google-ArrivalTime: 2004-01-14 10:51:45 PST From: "oj" <nospam_ojngo@home.com> References: <bu3v6m$28m$1@hercules.btinternet.com> Subject: Re: String / Variable Problem - Dynamic Table Name Date: Wed, 14 Jan 2004 10:49:39 -0800 Lines: 61 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Message-ID: <#fALr8s2DHA.1760@TK2MSFTNGP10.phx.gbl> Newsgroups: bit.databases.mssql-l,comp.databases.ms-sqlserver,cz.comp.microsoft.sql,microsoft.public.de.sqlserver,microsoft.public.jp.sqlserver.server,microsoft.public.sqlserver.misc,microsoft.public.sqlserver.programming,microsoft.public.sqlserver.server NNTP-Posting-Host: fw.pdx.polyserve.com 216.64.170.67 Path: arc1-ca.pugleaf.net!archiver1.google.com!news2.google.com!news.maxwell.syr.edu!feed.news.tiscali.de!newsfeed.freenet.de!newsfeed.r-kom.de!newsfeed00.sul.t-online.de!t-online.de!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl