Untitled

CREATE procedure sp_getCity
(
	@idCity int = null ,
	@city varchar(150) = null ,
	@idState int = null 
) as
set nocount on
begin
	if ( @idCity is not null )
	begin

		select _cities.id , _cities.name , _cities.idState , _states.name as state , _states.abrev as state_abrev
		from _cities
			inner join _states on _states.id = _cities.idState
		where _cities.id = @idCity
	
	end else if ( @city is not null and @idState is not null ) begin

		if ( not exists(select id from _cities where idState = @idState and name = @city) )
		begin

			insert into _cities ( idState , name ) values ( @idState , @city )

		end
		
		select _cities.id , _cities.name , _cities.idState , _states.name as state , _states.abrev as state_abrev
		from _cities
			inner join _states on _states.id = _cities.idState
		where _cities.idState = @idState and _cities.name = @city

	end
end
GO