Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Posts
    9

    Question Unanswered: dynamic declaration of table type -- any solution??

    We got to build a table dynamically using the following code,
    ====================================
    declare @Part int
    declare @examId int
    declare @tSQL varchar(2000)
    declare @colname varchar(100)
    select @Part = 6

    select @tSQL = 'declare @Report table ('
    Declare mycolumns cursor for
    SELECT SubjectCode FROM tblSubjectsMaster WHERE (Part = @Part) order by SubjectName
    OPEN mycolumns
    FETCH NEXT FROM mycolumns
    INTO @colname
    WHILE @@FETCH_STATUS = 0
    BEGIN
    Select @tSQL = @tSQL + @colname + ' varchar(100),'
    FETCH NEXT FROM mycolumns
    INTO @colname
    END
    CLOSE mycolumns
    DEALLOCATE mycolumns
    Select @tSQL = @tSQL + ' Total int, Result varchar(200), Place int)'
    exec(@tSQL)
    ==================================
    Then in the next line, I'm giving the following line.
    select * from @Report
    which is gioving the following error
    ======================
    Must declare the variable '@Report'.

    Assumption is that there is some scope related problem....But the requirement is demanding to think in this lines.

    Any body of any guess on any resolution.

    Thanks in advance.

    Thanks & Regards
    Srinivasa Reddy

  2. #2
    Join Date
    Aug 2002
    Posts
    20
    Hi,

    A table variable just exists until a go or exec command are executed. You have to create a temp table.

    /Mats

Posting Permissions

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