Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2005
    Posts
    1

    Question Unanswered: dynamic table name (was "Please help !! SQL")

    Hi--

    I am new to T-SQL i got this code from some website but its not working can anyone let me know why
    @cur_tab_name varchar(30) is not decleared while i have decleared
    I want to use this for dynamic name of the table.

    Thanks.






    create table temp_tab
    (
    tab_name varchar(30),
    no_of_rows INTEGER,
    )

    DECLARE
    curREVIEW

    CURSOR FOR
    select name
    from sysobjects
    where xtype = 'U'


    DECLARE @cur_tab_name varchar(30)

    OPEN curREVIEW

    FETCH curREVIEW INTO @cur_tab_name

    WHILE (@@FETCH_STATUS =0)
    BEGIN
    DECLARE @count integer

    select @count = count(*) from @cur_tab_name
    INSERT INTO temp_tab
    (@cur_tab_name, @count)

    FETCH curREVIEW INTO @cur_tab_name

    END

    CLOSE curREVIEW

    DEALLOCATE curREVIEW

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dynamic SQL must be passed as a string to EXEC:

    EXEC ('select @count = count(*) from ' + @cur_tab_name)

    ...but this still will not give you what you want, because the code will EXECute in a different scope than your @count variable.

    Try this:

    Code:
    Declare @SQLString varchar(500)
    Set @SQLString = 'INSERT INTO temp_tab select ''' + @cur_tab_name + ''', count(*) from ' + @cur_tab_name
    EXEC (@SQLString)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •