INF: Object Names Cannot be Variables in TRANSACT-SQL

Last reviewed: April 25, 1997
Article ID: Q65466

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:

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


Additional query words:
Keywords : kbprg SSrvGen SSrvServer SSrvTrans
Version : 4.2
Platform : OS/2


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 25, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.