By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,377 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

how to release table variables

P: 18
how to release table variables when work is done in cursor

Declare @Querydeleted varchar(1000)
May 1 '08 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
how to release table variables when work is done in cursor

Declare @Querydeleted varchar(1000)
I don't think you can. It automatically goes out of scope once the sp/function terminates.

Here are some good readings:

Using the Table Data Type in SQL Server 2000

INF: Frequently Asked Questions - SQL Server 2000 - Table Variable

Temporary tables in SQL Server vs. table variables

-- CK
May 1 '08 #2

P: 18
they said like "If you are using table variables in cursors, make sure you release them after the work is done"

Declare @objectdelete1 varchar(200)
Declare curiskeydeleted1 cursor for (Select fieldname from dbo.SchemaFields where ClassID in (select ID
from dbo.SchemaClasses as sc left outer join dbo.RuleDependency as rd on rd.ObjectName = sc.classfqn
where Rd.ruleID = @baseObject and sc.ClassStoreName not like 'base%' )And ISKey = 1)
OPEN curiskeydeleted1

FETCH NEXT FROM curiskeydeleted1 INTO @Objectdelete1
WHILE (@@FETCH_STATUS = 0)
BEGIN
Declare @Querydeleted1 varchar(3000)
select @querydeleted1=''
Select @querydeleted1 = 'Select ' + 'b.'+ @objectdelete1 + ' From '+ @testname + ' n Right Join ' + @testnamebase + ' b ' +
'On' + Right(@WhereClause1, Len(@WhereClause1) - 4) + ' Where'
+ Right(@Nullcheck, Len(@Nullcheck) - 4)

Delete @TempTableSecondary

Insert Into @TempTableSecondary (clmValue)
execute (@querydeleted1)
--select * from @TempTableSecondary

Insert Into SPResultset (GroupId,ChangeType , FieldName, FieldValue, fieldtype, ruleid,rulename)
Select row_number( ) over(order by (select 0)), 'Deleted Record', @Objectdelete1, clmValue, 'KeyFields', @rule,@rulename From @TempTableSecondary


FETCH NEXT FROM curiskeydeleted1 INTO @Objectdelete1
END
CLOSE curiskeydeleted1
DEALLOCATE curiskeydeleted1
May 1 '08 #3

ck9663
Expert 2.5K+
P: 2,878
They're talking about the CURSOR not the variable.

-- CK
May 1 '08 #4

P: 18
i deallocated the cursor
May 1 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.