I recently learned about the concept of parameter sniffing and I am having a confusion about a specific scenario where I am not able to understand the behavior of SQL Server. Anybody who can help me to understand will be great:
SCENARIO:
For instance we have a below stored procedure:
create procedure GetEmployeeDetails @a int as
BEGIN
IF @a = 1
BEGIN
select * from HumanResources.employee where BusinessEntityID = @a
END
IF @a = 2
BEGIN
select * from HumanResources.employee
END
END
And when I execute a stored procedure like below, will parameter sniffing should happen? I already tried it is not happening but I am amazed that why it is not happening. should the exact code be running for different parameters for parameter sniffing to happen? Is it a pre-requisite?
DBCC FREEPROCCACHE;
exec GetEmployeeDetails @a = 1
exec GetEmployeeDetails @a = 2
Any inputs are appreciated. Thanks.