CREATE FUNCTION remove_equipment()
RETURNS @equipment_table TABLE (
	car_navigation_cnt INT NOT NULL,
	etc_cnt INT NOT NULL
)
AS
BEGIN
	DECLARE @get_car_navigation_cnt INT;
	DECLARE @set_car_navigation_cnt INT;
	DECLARE @remainder_car_navigation_cnt INT;
	DECLARE @get_etc_cnt INT;
	DECLARE @set_etc_cnt INT;
	DECLARE @remainder_etc_cnt INT;

	-- 取り外すカーナビの台数
	SELECT @get_car_navigation_cnt = COUNT(car_management_no)
	FROM nmrm_rent_car
	WHERE smoking_kbn = '1'
	  AND car_navigation = '1';

	-- カーナビの取り付け対象となる車の台数
	SELECT @set_car_navigation_cnt = COUNT(car_management_no)
	FROM nmrm_rent_car
	WHERE smoking_kbn = '2'
	  AND car_navigation = '0';

	-- カーナビを喫煙者→禁煙車に移した時のカーナビの残数
	SET @remainder_car_navigation_cnt = @get_car_navigation_cnt - @set_car_navigation_cnt;
	IF @remainder_car_navigation_cnt < 0
	BEGIN
		SET @remainder_car_navigation_cnt = 0;
	END

	-- 取り外すETCの台数
	SELECT @get_etc_cnt = COUNT(car_management_no)
	FROM nmrm_rent_car
	WHERE smoking_kbn = '1'
	  AND etc = '1';

	-- ETCの取り付け対象となる車の台数
	SELECT @set_etc_cnt = COUNT(car_management_no)
	FROM nmrm_rent_car
	WHERE smoking_kbn = '2'
	  AND etc = '0';

	-- ETCを喫煙者→禁煙車に移した時のETCの残数
	SET @remainder_etc_cnt = @get_etc_cnt - @set_etc_cnt;
	IF @remainder_etc_cnt < 0
	BEGIN
		SET @remainder_etc_cnt = 0;
	END

	INSERT INTO @equipment_table VALUES(@remainder_car_navigation_cnt, @remainder_etc_cnt);

	RETURN;
END;
