none
Must Declare Variable @tableName Error ?

    Question

  • create Procedure dummy
    (
     @tablename nvarchar(20)
    )
    AS
    Begin
    select ID,employeename from @tablename
    End
    

    I dont know why SqlServer is not able to search a table name so thought of getting some help from sql Gurus .

    Any help would highly be appreciated .

    Thanks .


    Rajkumar Yelugu
    Tuesday, August 31, 2010 2:19 PM

Answers

  • Well you cannot pass variable as you table name. If you want to achieve this you need to use dynamic SQL. Something like below

    create Procedure dummy
    (
     @tablename nvarchar(20)
    )
    AS
    Begin
    
    Declare @sql nvarchar(max)
    
    SET @SQL = 'select ID,employeename from ' + @tablename
    
    EXEC SP_EXECUTESQL @SQL
    End
    
    
    

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, August 31, 2010 2:21 PM

All replies

  • Well you cannot pass variable as you table name. If you want to achieve this you need to use dynamic SQL. Something like below

    create Procedure dummy
    (
     @tablename nvarchar(20)
    )
    AS
    Begin
    
    Declare @sql nvarchar(max)
    
    SET @SQL = 'select ID,employeename from ' + @tablename
    
    EXEC SP_EXECUTESQL @SQL
    End
    
    
    

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, August 31, 2010 2:21 PM
  • Can you explain the purpose of such procedure where you need to use dynamic table name?

    Also, if you want to prevent SQL injection attacks, add this code

    if not exists (select 1 from Information_Schema.Tables where Table_Name = @TableName)

      begin

         raiserror('Invalid table name %s passed!',16,1,@TableName)

       return - 1

      end

    -- now go with the dynamic query


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, August 31, 2010 2:26 PM
  • The variables cannot be used in that way. SQL Server defines, where you can use a variable and <table_source> cannot be a variable. (I do not find a link that quickly. Sorry)

    If you want to have dynamic SQL, you have to use something like:

    EXEC('SELECT * FROM ' + @tablename)

    But be sure to read about SQL Injection, because this can be a major security risk!

    With kind regards,

    Konrad

    Tuesday, August 31, 2010 2:32 PM
  • Naom ,

    I'm doing some Reporting Tasks where user will select appropriate table_Name from  dropdown Parameter .

    Thanks


    Rajkumar Yelugu
    Tuesday, August 31, 2010 2:34 PM
  • I see, then Deven's answer is the answer. If the table name comes from the drop down and assuming it's populated with the names of the tables from the database, then you may not even need a check I added.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, August 31, 2010 2:37 PM
  • Hi ,

    Appreciate and value your inputs , time and concern towards the posting[s] .

    Thanks .

     


    Rajkumar Yelugu
    Tuesday, August 31, 2010 2:48 PM
  • Forgot one little detail:

    set @SQL = 'select * from ' + quotename(@TableName) -- quotename is important in case table name contains a space or has other problems.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, August 31, 2010 3:10 PM
  • Naom ,

    Will take the tip into concern and a  quick one if you have time, can you post a sample of using dynamic server as we did for the table above ?


    Rajkumar Yelugu
    Tuesday, August 31, 2010 3:14 PM
  • Naom ,

    Will take that into concern and a quick one if you have time , can u post a sample how to use  dynamic server and dynamic  table  as well .

    Thanks in advance .


    Rajkumar Yelugu
    Tuesday, August 31, 2010 3:18 PM
  • With the server name it's not that simple as the server name must be first added as a linked server.

    Assuming it is, then you may try

    set @SQL = 'select * from ' + quotename(@ServerName) + '.' + quotename(@DbName) + '.' + quotename(@SchemaName) + '.' + quotename(@TableName)


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, August 31, 2010 3:22 PM
  • > I'm doing some Reporting Tasks where user will select appropriate table_Name from  dropdown Parameter .

    In that case, you should not write a stored procedure. It would be a lot easier to compose the SQL in the client. Less pieces of code to keep track of.

    For the full story of dynamic SQL, when you should use it and when you should not, there is a longer article on my web site: http://www.sommarskog.se/dynamic_sql.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Tuesday, August 31, 2010 10:13 PM