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