Click here to Skip to main content
14,599,188 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Posted
Updated 31-Jan-19 1:33am
Comments
MadMyche 31-Jan-19 7:36am
   
What happens if you run the SELECT statement directly with values 4,5? 3,2?
The procedure looks OK, but if the data isn't there it will return nothing
Richard Deeming 4-Feb-19 14:15pm
   
NB: What you've shown there is assigning default values to the parameters. You don't have any local variables. :)

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

It returns no rows because your table contains no rows where the CityId is 4 and the StateId is 5. If you add such a row to your table, it will be returned.

If you don't supply values, the SP definition says they should be defaulted - but that doesn't change the data in any way!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100