Tuesday, February 7, 2017

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog