If we assign some value to the local variable, and call that procedure with some value. Then?

we create a stored procedure, and assign some value to local variable.

create procedure prcGetCity @cityid int = 4, @stateid int =5
as
select * from TblMasterCity where cityid=@cityid and StateID=@stateid

then, call procedure:
execute prcGetCity 3,2

it gives correct result. but if we call procedure without value.

execute prcGetCity

it returns 0 rows.

So, my question is what is the benefit to assign value in local variable. if it working like without assigning value.

What I have tried:

create procedure prcGetCity @cityid int = 4, @stateid int =5
as
select * from TblMasterCity where cityid=@cityid and StateID=@stateid


execute prcGetCity

Tags: SQL Server