Thursday, March 8, 2012

A simple sproc problem

I am trying to do something quite simple but i need some help. I am trying to insert some values into a table in my db. Lets say column country_id and city_id are required fields but state_id is not. I am using a stored proc that looks like
CREATE procedure spAdd_Countries_States_Cities

@.country_id int,
@.state_id int ,
@.city_id int

AS
insert Countries_States_Cities (country_id, state_id, city_id)
values (@.country_id, @.state_id, @.city_id)
GO
The problem is if I only pass two values into this proc I get an error saying that the sproc is looking for state_id. At the same time I need it there in case a state_id is being added. How do I work around this problem?
Thanks

I'd solve this problem by assigning a default value of NULL to the non-required parameter:
CREATE procedure spAdd_Countries_States_Cities

@.country_id int,
@.state_id int= NULL,
@.city_id int

AS
insert Countries_States_Cities (country_id, state_id, city_id)
values (@.country_id, @.state_id, @.city_id)
GO

No comments:

Post a Comment