The information in this article applies to:
- Microsoft SQL Server version 4.2 for OS/2
SUMMARY
Because object resolution occurs at compile time, TRANSACT-SQL
variables (@variable) cannot be used in place of object names.
For example, TRANSACT-SQL does not allow variable database names.
MORE INFORMATION
However, you can use any of the following three alternatives to
simulate variable table names:
- Create a DB-Library (DB-Lib) procedure using C. The following
example demonstrates this technique. Note that this example
is not a complete program. See the "Microsoft SQL Server
Programmer's Reference" for further information.
...
char table[40];
char *storid = "6380";
printf ("Please Enter the table to extract: ");
gets(table);
dbfcmd(dbproc, "select * from %s ", table);
dbfcmd(dbproc, "where stor_id = '%s' ", storid);
dbsqlexec (dbproc);
while (dbresults(dbproc)) != NO_MORE_RESULTS)
dbprrow(dbproc);
...
- Create an OS/2 command file that accepts parameters. The
following example selects all from a table: file GETDAT.CMD.
It is called by "getdat PUBS Titles BU1032".
@echo off
set db=%1
set tb=%2
set data=%3
echo /* SQL Input file (getdat %db% %tb% %data%) */> isql.in
echo use %db% >> isql.in
echo go >> isql.in
echo select * from %tb% >> isql.in
echo where title_id = '%data%' >> isql.in
echo go >> isql.in
:*
:* Select from table
:*
isql /U sa /P /S SERVERNAME /n /e /p <isql.in >getdat.out
- If you have a limited number of databases and tables, you can
write a procedure to check the value of dbname and tabname and
run the appropriate select statements. For example:
create procedure getdata @dbname varchar (30),
@tabname varchar (20),
@dataname varchar(24)
as
if @dbname = "pubs"
if @tabname = "Titles" . . .
select * from pubs..titles
where title_id = @dataname
if @tabname = "titleauthor"
select * from pubs..titleauthor
where title_id = @dataname
if @dbname = "tst"
print "oops."
|