Post reply

table var after fetch

  • January 14, 2004 at 1:32 pm

    #162944

    Hi this was posted as "Closed Recordset returned?? WHY?"

    but I have new info and a more concise question.

    It appears that if you do a fetch in a stored proc you can not return a result set based on a table var regardless of having nocount set on.

    in psueda code:

    set nocount on

    declare @tblVar (somefld int)

    insert into @tblVar (somefld) values (1)

    declare cursor

    open cursor

    destroy cursor

    select * from @tblVar

    go

    Running the above from ADO gives you desired results

    However:

    set nocount on

    declare @tblVar (somefld int)

    insert into @tblVar (somefld) values (1)

    declare cursor

    open cursor

    FETCH FROM CURSOR

    destroy cursor

    select * from @tblVar

    go

    Produces an "Operation is not allowed when the object is closed. " error.

    Does anyone know a way around this?

    I have to do the fetch and a tbl var is the prefered way to do this.

    Is there some modifier tothe way the fetch is performed maybe?

     

     

     

     

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

  • January 14, 2004 at 1:50 pm

    #490062

    You can't use variables as db object names. You need to build a sting and then execute it.

    Declare @SQL varchar(1000)

    Set @SQL = 'insert into ' + @tblVar + ' (somefld) values (1)'

    Exec (@SQL)

    Set @SQL = 'select * from ' + @tblVar

    Exec (@SQL)

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • January 14, 2004 at 2:11 pm

    #490069

    chisholmd,

    I have never had a problem with it. Try the following and see if it works for you...

    SET NOCOUNT ON

    DECLARE  @tblVar TABLE(somefld int)

    DECLARE @iVal int

    DECLARE @C cursor

    INSERT INTO @tblVar (somefld) VALUES (1)

    SET @C = cursor FOR SELECT somefld FROM @tblVar FOR READ ONLY

    OPEN @C

    FETCH @C INTO @iVal

    CLOSE @C

    DEALLOCATE @C

    SELECT * FROM @tblVar

    GO

     




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 14, 2004 at 2:30 pm

    #490076

    Thanks Gary!

    I modified your script to more closely resemble mine and it worked. I still am having troubles getting my main proc to work, but it's a big proc and I'm sure the answer is in here somewhere now.  It must either be the FOR READ ONLY or the way you are declaring the cursor.  Or perhaps it is the other items i have runing within the while loop. i'll keep plugging away but you have given my hope

    SET NOCOUNT ON

    DECLARE  @tblVar TABLE(somefld int)

    DECLARE @iVal int

    DECLARE @C cursor

    SET @C = cursor FOR SELECT uid FROM chapter where course_fk=78 FOR READ ONLY

    OPEN @C

    FETCH @C INTO @iVal

    WHILE @@FETCH_STATUS = 0

    BEGIN

       FETCH NEXT FROM @C

       INTO @iVal

       INSERT INTO @tblVar (somefld) VALUES (@iVal)

    END

    CLOSE @C

    DEALLOCATE @C

    SELECT * FROM @tblVar

    GO

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

  • January 14, 2004 at 2:37 pm

    #490081

    The FOR READ ONLY shouldn't make any difference. The way I have declared the cursor just makes it use a local variable rather than using a global cursor (I firmly believe it is better to limit the scope when ever possible!). Also notice that I close and deallocate the cursor as well.

    What I would do is to copy the SP and remark out much of the SP as you try to solve this problem. Then slowly unremark blocks of code until it works. Once you have narrowed down where the problem is you are home free! I don't think that using the "Debugger" in QA will help you here as stepping through line by line won't really help you find the problem.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 14, 2004 at 3:10 pm

    #490089

    woot! Your solution got me moved ahead enough to find another problem that was causing it to not function.

    Within the WHILE I was doing an insert like this:

    insert into A select from B where uid=@id

    set @trgID = @@identity

    For some reason it was throwing an ansi warning about nulls???

    So I'll look into why later, for now SET ANSI_WARNINGS OFF seems to do the trick

    Thanks Loads!

    a day and a half of grief and frustration comes to an end just in time to pick the little ones up from school

     

     

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply