Set variables to Null at the end of stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Set variables to Null at the end of stored proc

Author

Topic

fasttrack
Starting Member

12 Posts

Posted - 2006-11-22 : 11:12:52

I have built up a big stored proc that has lots of variable, between them some varchar are very long.

My question is:

Is it better to put all the variables to NULL at the end of the stored proc?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 11:15:43

It doesn't matter. SQL will kill the variables when scope is ending.

Peter Larsson
Helsingborg, Sweden

fasttrack
Starting Member

12 Posts

Posted - 2006-11-22 : 11:55:50

thank

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-22 : 12:11:59

quote:
Originally posted by fasttrack

I have built up a big stored proc that has lots of variable, between them some varchar are very long.

My question is:

Is it better to put all the variables to NULL at the end of the stored proc?

Only in front end, you need to close all your Recordsets

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 12:20:16

What about connections?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-22 : 20:12:05

quote:
Originally posted by Peso

What about connections?

Peter Larsson
Helsingborg, Sweden

Yes. it also should be closed

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-22 : 20:52:24

Just in case you happen to be using a .net language, you need to call Dispose() on all IDisposables (such as SqlConnection, SqlCommand, SqlDataReader). otherwise you will leak native resources. best way to do this is to use the "using" keyword.

Many .net programmers do not understand this point - they think the GC will clean up after them, and this is not true. GC handles memory only, not resources in general.

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources