How to execute a Stored Procedure with Parameters, Variables and Literals in SQL Server

How to define and declare Parameters, Variables and Literals in a SP of SQL Server
The Parameters are values that are being passed into a Stored Procedure to execute it if they are part of the Call to execute. Since these are part of the call to execute the procedure; these are “known” during compilation / optimization.

The Variables are assigned at runtime and therefore are “unknown” during compilation / optimization.

The Literals are known at all times as they are exactly that – a simple / straightforward value(s).

Example :

Let us suppose we have a Table called [EMP_Details] from which if you want to filter only the Particular Dept Emp details, by Passing the Dept_Name via Parameter or Variable or as a Literal into the Stored Procedure, you can do this by following ways...

USE TAMATAM
GO

CREATE PROCEDURE [PARAM_VAR_LIT]
   (
       -- Defining the Parmeter Name @P_Dept and the DataType
         @P_Dept    VARCHAR(15)

   )
AS
-- Declarations / Assignments

-- External Declaration of the Variable Name @@V_Var1 and the Data Type

   DECLARE @V_Var1 VARCHAR(15); 
BEGIN

-- Assigning the Variable to the Parameter Input Value

   SELECT @V_Var1 = @P_Dept;  

-- Internally defining the Variable name @V_Var2 and the Data Type

   DECLARE @V_Var2 VARCHAR(15);

 --Assigning the Varibale to a Literal Value

   SELECT @V_Var2 = 'Business Intelligence'; 

--Also you can declare this way

--DECLARE @V_Var2 VARCHAR(15)= 'Business Intelligence'

--Statement 1 (Using a Parameter)

  SELECT [E].*

  FROM [DBO].[EMP_Details] AS [E]

  WHERE [E].[Dept_Name] = @P_Dept 

--Statement 2 (Using a Variable)

  SELECT [E].*

  FROM [DBO].[EMP_Details] AS [E]

  WHERE [E].[Dept_Name] = @V_Var1; -- OR @V_Var2, These will work in the same way!

--Statement 3 (Using a Literal)

  SELECT [E].*

  FROM [DBO].[EMP_Details] AS [E]

  WHERE [E].[Dept_Name] = 'Business Intelligence';

END

GO

Note:
We can declare variable either externally before main BEGIN, or with in the BEGIN..END block. If declare externally, they will available through out the Procedure.

-------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------