Hi,
Is it better to declare all variables at the top (even if some variables will not get used sometimes due to code execution case blocks) in a stored procedure? I think SQL Engine handles this better when "preparing" a sp.
This is for SQL2008. I also would need a "white paper" / genuine link for me to produce to the developers here at work.
Many thanks,
Bond Ridhoo.
I am in the camp that espouses the point that variable declarations should be made in the context in which they are used. I think it enhances readability, so that I can see the variable's data type at the spot of first use (one could argue that it's harder to track down a variable declaration in the middle of an sp rather than the beginning, but if that's the case then perhaps the sp is too long to begin with and should be broken into multiple smaller sp's).
I agree with Kev! You should move your "code execution case blocks" to different stored procedures and call them from your "main" procedure. If you don't, you may end up with stored procedures that are recompiled every time, or even worse incorrect execution plans.
Even if your statements looks the same, they may benefit from different execution plans due to data distribution in indexes, etc.
This causes performance issues in the end.
I don't think that the variables being declared at the top helps performance. However I do believe it makes it easier to read/understand.
I prefer to declare my procedure variables at the top but not just for readability. In T-SQL, the scope of a variable is the entire batch where it is declared. In .NET languages such as C#, however, the scope is the code block. I think it's generally best to declare all variables at the beginning of their scope. If the usage is further limited to a smaller block of code, that can be explained in comments. Usage range and scope range are not always the same, especially in a language like T-SQL that doesn't support multi-level code-block scope. Remember: If you start a new batch in T-SQL, all previously-declared variables are lost.
Examples:
SQL Server misleading (@v1 and @v2 have identical scope -- entire procedure)
Note: @v2 cannot be accessed until after it is declared regardless of scope
CREATE PROCEDURE sample
AS
BEGIN
DECLARE @v1 int = 1;
IF @v1 = 1
BEGIN
DECLARE @v2 int = 2;
-- do something with @v2
END
END
SQL Server better (@v1 and @v2 show identical scope -- entire procedure)
CREATE PROCEDURE sample
AS
BEGIN
DECLARE @v1 int = 1;
DECLARE @v2 int = 2; -- only used in "IF @v1 = 1" block
IF @v1 = 1
BEGIN
-- do something with @v2
END
END
C# not as good (v1 and v2 have identical scopes, but v2 is only used in the if block)
public int sample()
{
int v1 = 1;
int v2 = 2; //only used in "IF @v1 = 1" block
if (v1 == 1)
{
// do something with v2
}
}
C# better (v1 and v2 have different scopes; v2's scope is limited to the if block)
public int sample()
{
int v1 = 1;
if (something)
{
int v2 = 2;
// do something with v2
}
}
I would say "near" the top. I wouldn't take it too literally, or you end up with:
declare @somedate datetime
declare @thistable table(
id int
, thisfield varchar(50)
, anotherdate datetime
)
declare @setting as int
select @somedate = getdate()
insert into @thistable(
...)
select ... from ...
set @setting = 500
I don't find this helpful. Like most, it is what you are use to and how consistent you are.
