Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    4

    Unanswered: Select statement

    Hi All,

    I wanted to execute a statement like this in T-SQL,

    Select * from @tabelname

    where @tablename is a variable, the value is returned from a previous T-SQL statement. It gives @tablename must be decalred error. Please help how to resolve this issue.

    Here is my T-SQL code:

    DECLARE @tablename varchar(255)
    DECLARE @filename varchar(255)
    DECLARE @bcpcmd varchar(255)
    DECLARE @no_rec int
    DECLARE @row_count int
    DECLARE @row_total int
    DECLARE @sqlstr varchar(255)

    SET @row_count = 0

    SELECT @no_rec = (SELECT COUNT(Repl_Filename) from Repl_TableNames)

    IF @no_rec <> 0
    BEGIN

    SELECT @tablename = (SELECT Repl_TableName from Repl_TableNames)
    SELECT @filename = (SELECT Repl_FileName from Repl_TableNames)
    SET @sqlstr = 'SELECT Count(*) from ' + @tablename

    exec (@sqlstr)

    --DELETE FROM Repl_TableNames WHERE Repl_filename=@filename
    WHILE (exists (Select * from @tabelname)
    BEGIN
    END

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Assuming you have more than one record in Repl_TableNames, which one did you want to assign to @tablename? I think you may end up needing a cursor to do what you want to do, but in the meantime replace your exec statement with a print statement to see exactly what it is attempting to run:

    print @sqlstr
    --exec (@sqlstr)

    I don't have access to SQLServer at the moment to test any code, but I suspect you need a statement like

    SET @tablename = (SELECT Top 1 Repl_TableName from Repl_TableNames)


    blindman

  3. #3
    Join Date
    Oct 2002
    Posts
    7

    Re: Select statement

    There seem to be a couple of issues and one of them may simply be a typo.

    1. Check the variable name - @tabelname Vs @tablename
    2. The bigger issue is with the "while" statement - you cannot do a "select * from @tabelname". You have to use a dynamic statement.

    Hope this helps.

    Originally posted by kamalsinghania
    Hi All,

    I wanted to execute a statement like this in T-SQL,

    Select * from @tabelname

    where @tablename is a variable, the value is returned from a previous T-SQL statement. It gives @tablename must be decalred error. Please help how to resolve this issue.

    Here is my T-SQL code:

    DECLARE @tablename varchar(255)
    DECLARE @filename varchar(255)
    DECLARE @bcpcmd varchar(255)
    DECLARE @no_rec int
    DECLARE @row_count int
    DECLARE @row_total int
    DECLARE @sqlstr varchar(255)

    SET @row_count = 0

    SELECT @no_rec = (SELECT COUNT(Repl_Filename) from Repl_TableNames)

    IF @no_rec <> 0
    BEGIN

    SELECT @tablename = (SELECT Repl_TableName from Repl_TableNames)
    SELECT @filename = (SELECT Repl_FileName from Repl_TableNames)
    SET @sqlstr = 'SELECT Count(*) from ' + @tablename

    exec (@sqlstr)

    --DELETE FROM Repl_TableNames WHERE Repl_filename=@filename
    WHILE (exists (Select * from @tabelname)
    BEGIN
    END

Posting Permissions

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