Need advice re: best practices with Table variables 3

[Sheco]

When I create a temp table inside a stored procedure, it is my habbit to explictly drop it when I'm done... rather than just letting it go out of scope.

Is there anything like this I should be doing to table variables?

 

[vongrunt]

DROP TABLE @blah?

Mission impossible

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[Sheco]

Is it a good idea for any reason other than consistancy with the way the temp tables are done?

 

[Sheco]

pffffttt.

[tt][red]Server: Msg 170, Level 15, State 1, Procedure spV2_ClientSearch_New, Line 343[/red]
Line 343: Incorrect syntax near '@ResultSet'.[/tt]

 

[gmmastros]

Is there anything like this I should be doing to table variables?

No.

You can't drop a table variable. Just let it go out of scope.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Since @variables cannot be dropped or set to Empty or Nothing or TRUNCATEd... the only eventual option is DELETE FROM.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[Sheco]

Is there anything to be gained by explicitly deleting?

 

[SQLDenis]

>>Is there anything to be gained by explicitly deleting?

wasted CPU cycles perhaps

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Here is one hypothetical scenario:

create proc someproc
as

	declare @table table (... )
	
	insert into @table
		<several million rows>
		
	-- some calculations on @table
	
	-- from this point @table is no more necessary
	
	-- other pieces of sproc taking long time to finish
go

In other words: very likely - not.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[Sheco]

Thanks everyone!