String / Variable Problem - Dynamic Table Name - Article 8 - go-pugleaf

Article View: bit.databases.mssql-l

Article #8

Back to bit.databases.mssql-l Previous Next

String / Variable Problem - Dynamic Table Name

From: Martin Feuersteiner
Date: Wed, 14 Jan 2004 17:44

29 lines
2931 bytes

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: <bu3v6m$28m$1@hercules.btinternet.com>

Path:

arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-02!sn-xit-04!sn-xit-01!sn-xit-08!supernews.com!freenix!ircam.fr!news.completel.fr!ciril.fr!news.cs.univ-paris8.fr!newsfeed.stueberl.de!newsr1.ipcore.viaginterkom.de!btnet-peer1!btnet-feed5!btnet!news.btopenworld.com!not-for-mail

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: 10f695,1ea0e1ec7764ef39,start
X-Google-Attributes: gid10f695,public
X-Google-Thread: f816f,1ea0e1ec7764ef39,start
X-Google-Attributes: gidf816f,public
X-Google-Thread: ff490,1ea0e1ec7764ef39,start
X-Google-Attributes: gidff490,public
X-Google-Thread: f6611,1ea0e1ec7764ef39,start
X-Google-Attributes: gidf6611,public
X-Google-Thread: 1165d6,1ea0e1ec7764ef39,start
X-Google-Attributes: gid1165d6,public
X-Google-Thread: 104de1,1ea0e1ec7764ef39,start
X-Google-Attributes: gid104de1,public
X-Google-Thread: 106afd,1ea0e1ec7764ef39,start
X-Google-Attributes: gid106afd,public
X-Google-Thread: 11301e,1ea0e1ec7764ef39,start
X-Google-Attributes: gid11301e,public
X-Google-ArrivalTime: 2004-01-14 09:45:09 PST
Path: arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-02!sn-xit-04!sn-xit-01!sn-xit-08!supernews.com!freenix!ircam.fr!news.completel.fr!ciril.fr!news.cs.univ-paris8.fr!newsfeed.stueberl.de!newsr1.ipcore.viaginterkom.de!btnet-peer1!btnet-feed5!btnet!news.btopenworld.com!not-for-mail
From: "Martin Feuersteiner" <theintrepidfox@hotmail.com>
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
Subject: String / Variable Problem - Dynamic Table Name
Date: Wed, 14 Jan 2004 17:44:54 +0000 (UTC)
Organization: BT Openworld
Lines: 32
Message-ID: <bu3v6m$28m$1@hercules.btinternet.com>
NNTP-Posting-Host: host81-128-85-235.in-addr.btopenworld.com
X-Trace: hercules.btinternet.com 1074102294 2326 81.128.85.235 (14 Jan 2004 17:44:54 GMT)
X-Complaints-To: news-complaints@lists.btinternet.com
NNTP-Posting-Date: Wed, 14 Jan 2004 17:44:54 +0000 (UTC)
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MSMail-Priority: Normal
X-Priority: 3
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106