December 22, 2013 at 6:55 am
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
December 22, 2013 at 8:11 am
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.
December 22, 2013 at 2:00 pm
nazaninahmady_sh (12/22/2013)
Hiwhat 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
Change is inevitable... Change for the better is not.
December 23, 2013 at 8:18 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply