Microsoft KB Archive/65466

= INFO: Object Names Cannot be Variables in TRANSACT-SQL =

Article ID: 65466

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q65466



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 either of the following 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 &quot;Microsoft SQL Server Programmer's Reference&quot; for further information. ...     char  table[40]; char *storid = &quot;6380&quot;; printf (&quot;Please Enter the table to extract: &quot;); gets(table);

dbfcmd(dbproc, &quot;select * from %s &quot;, table); dbfcmd(dbproc, &quot;where stor_id = '%s' &quot;, storid);

dbsqlexec (dbproc);

while (dbresults(dbproc)) != NO_MORE_RESULTS)        dbprrow(dbproc);      ...

  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 = &quot;pubs&quot; if @tabname = &quot;Titles&quot;. . .          select * from pubs..titles where title_id = @dataname if @tabname = &quot;titleauthor&quot; select * from pubs..titleauthor where title_id = @dataname if @dbname = &quot;tst&quot; print &quot;oops.&quot;



Keywords: kbinfo kbprogramming KB65466

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.