Hi there, I have recently started a new job.  They are using SQL server 2008.  When looking at stored procedures.  I have noticed that they are declaring variables, then declaring those variables again as a local variable.  This is to help on the indexing and speed of the stored procedures.  From what I have been told.   An example is in the code attached.  I was curious to know why they might do this ?  

I want to make changes but I am wanting to understand their reasons as to why the stored procedures were written this way. If any one can help or show some links to what the benefits of @ local Variable?  I would be most grateful.  

ALTER PROCEDURE [dbo].[CVDHA]
	-- Add the parameters for the stored procedure here
 @YEAR nvarchar (4) , @MONTH nvarchar (2),@DHB nvarchar (60), @PHO nvarchar (60), @Prac nvarchar (100), @User nvarchar (60)
 , @Where nvarchar (15)
 with recompile
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	declare @To_LOCAL datetime, 
			@YEAR_LOCAL nvarchar (4) , 
			@MONTH_LOCAL nvarchar (2),
			@DHB_LOCAL nvarchar (60), 
			@PHO_LOCAL nvarchar (60), 
			@Prac_LOCAL nvarchar (100), 
			@User_LOCAL nvarchar (60), 
			@Where_LOCAL nvarchar(15), 
			@SqlStatment nvarchar(max), 
			@scope nvarchar(10),
			@tablename nvarchar(max)
	SET @YEAR_LOCAL = @YEAR
	SET @MONTH_LOCAL = @MONTH
	SET @DHB_LOCAL = @DHB
	SET @PHO_LOCAL = @PHO
	SET @Prac_LOCAL = @Prac
	SET @To_LOCAL=DateAdd(Second, -1, dateadd(qq, DateDiff(qq, -1, convert(datetime,(@YEAR_LOCAL+ '-'+ @MONTH_LOCAL + '-01'),102)), 0))
 	SET @User_LOCAL = @User
	SET @Where_LOCAL = @Where

The reason they are doing this is due to parameter sniffing. By declaring local variables you are basically taking parameter sniffing out of the equation, is this a good thing...not always, it really depends on your data distribution within the tables, but generally if you can take advantage of parameter sniffing, it would give you the best performance, however you are taking a risk that in some cases ( based on your data) your stored procedure would suddenly perform worse than usual...

There are a lot of presentation/articles/posts on parameter sniffing, i know Grant Fritchey has a good presentation on the topic.

• Proposed as answer by Jason A Long 4 hours 32 minutes ago

>>  When looking at stored procedures. I have noticed that they are declaring variables, then declaring those variables again as a local variable. This is to help on the indexing and speed of the stored procedures. From what I have been told. An example is in the code attached. I was curious to know why they might do this? <<

You are working with idiots :(  They are still writing COBOL and FORTRAN, but using bad T-SQL to do it.  Did you notice the short names and meta-data prefixes? That is usually a sign of FORTRAN or BASIC. Back in the old days, we had only had six letter names in FORTRAN I, and there were no ISO-11179 rules. That is why you get a procedure named CVDHA written in uppercase, just like a punch card program in 1975! Today we would use a <verb>_<object> syntax, like get_customer() 

Functional and declarable languages do not use local variables. A good book, if you can find it, is FUNCTIONAL PROGRAMMING by Peter Henderson. 

>> the benefits of @ local Variables? I would be most grateful. <<

None. In fact, they prevent the optimizer from working and assure the code cannot be ported. By making parameters into short Chinese Unicode strings, they have destroyed any hope of performance. They do not know that YEAR and MONTH are reserved words in SQL (but not part of FORTRAN I) and are temporal data that never existed before SQL.  Then to screw up thigns further, they did not even use @foo_year CHAR(4)  and @foo_month CHAR(2) 

In the original IBM hardware, there were hardware registers that had to be used to pass data to and from the main program to the subroutines. This was done by putting the data in local variables in the subroutine. I would have to look it, but I think that the 60 character length cam from the same hardware that allowed variable names to only be six characters. 

The sql_statememt and table_name imply dynamic SQL, another horrible mistake. 

A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:  

CREATE TABLE Report_Periods
(report_name CHAR(10) NOT NULL PRIMARY KEY,
report_start_date DATE NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
ordinal_period INTEGER NOT NULL UNIQUE 
  CHECK(ordinal_period > 0)
etc);

These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each Annual Going out Of Business Sale! and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created.

I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. 

You are not going to get any good experience here, so leep your resume up to date and leave when you find a good shop that is hiring 

We need to see the whole procedure, but most likely there is no need for this re-declaration. If it's done to avoid parameter's sniffing, we can just use OPTION (RECOMPILE) at the query. So, I'd say that this is done needlessly, but we can be certain if you post the whole procedure.

Also, it looks like the code will create a dynamic SQL. In this case the whole purpose of the stored procedure is questio