INF: Object Names Cannot be Variables in TRANSACT-SQL

• Microsoft SQL Server for OS/2, version 4.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:

1. 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);
         ...
    




2. 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
    




3. 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."
    

Keywords : kbprg SSrvGen SSrvTrans SSrvServer
Version : 4.2
Platform : OS/2
Issue type :