table var after fetch

Question

Post reply

table var after fetch

chisholmd

SSCrazy

Points: 2490
More actions
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)

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

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

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]

gljjr SSChampion Points: 10080 More actions · Answer 2

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.

chisholmd SSCrazy Points: 2490 More actions · Answer 3

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)

gljjr SSChampion Points: 10080 More actions · Answer 4

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.

chisholmd SSCrazy Points: 2490 More actions · Answer 5

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)