different with the variables

Question

different with the variables

nazaninahmady_sh

Default port

Points: 1465
More actions
December 22, 2013 at 6:55 am

#281508

Hi
what is the difference between these 2 variables in store procedure:
1- the variables before As
2- the variable with declare after As
thanks alot

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Care to provide a better description of the question?

I am not sure if you are asking what the difference between the parameters declared as input/output to the procedure and variables declared locally to the procedure, which is what it sounds like you are asking.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

nazaninahmady_sh (12/22/2013)
Hi
what is the difference between these 2 variables in store procedure:
1- the variables before As
2- the variable with declare after As
thanks alot

The variables before the "AS" are paremeters that you can pass into the stored procedure.

The variables with the DECLARE that come after the "AS" are local variables to the procedure.

I strongly recommend that you get into SSMS , press the {f1} key to get into "Books Online" (the help system that comes with SQL Server), and do a search for "CREATE PROCEDURE" to learn more about the different parts of Stored Procedures and how to build them correctly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

Also know that the optimizer, with one exception, treats a parameter (before the AS) and a variable (after the AS) differently. A parameter value is known to the optimizer when it compiles the query since the parameter has to be passed with the execution statement, while a variable value is not known (except in the case of a recompile, the one exception). The known values are used to compare against the statistics to arrive at a more specific plan for the data being passed. This process is known as parameter sniffing. A variable, because it's value is unknown, will get an average plan based on an average of the statistics (except, again, in the case of recompiles).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning