Tuesday, 7 February 2017

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

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
-- Defining the Variable Name @@V_Var1 and the Data Type
DECLARE @V_Var1 VARCHAR(15); 
-- Assigning the Variable to the Parameter Input Value
SELECT @V_Var1 = @P_Dept; 

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

GO

Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts