Correct data returned by the stored procedure - SQLServer 2014

Seeing the data model that you added in a comment (I remind you that it is best to add it to the question), I see the subject a little clearer.

I understand that what you want to obtain is a list of occupation by category date and hotel. Based on that I made some changes to your coansulta. First of all, since you are not interested only in the occupied rooms but also the empty ones, modify the FROM and the OCUPACION we access it with a LEFT . I hope it serves you:

DECLARE @Fecha_Reserva_Desde DATE = '20170601'
DECLARE @Fecha_Reserva_Hasta DATE = '20170603'

SELECT  Ho.Descripcion as Hotel, 
    O.Fecha, 
    C.Decripcion, 
    SUM(CASE WHEN ISNULL(O.Cantidad_ocupantes,0) > 0 THEN 1 ELSE 0 END)     as 'Habitaciones ocupadas',
    SUM(CASE WHEN ISNULL(O.Cantidad_ocupantes,0) = 0 THEN 1 ELSE 0 END)     as 'Habitaciones Libres'
    FROM HABITACIONES AS H
    LEFT JOIN OCUPACION AS O
        ON (O.Codigo_habitacion = H.Codigo_habitacion)
    INNER JOIN HOTELES AS Ho
        ON (H.Codigo_hotel = Ho.Codigo_hotel)
    INNER JOIN CATEGORIAS AS C
        ON (C.Codigo_categoria = H.Codigo_categoria)
    WHERE O.Fecha BETWEEN @Fecha_Reserva_Desde and @Fecha_Reserva_Hasta 
    GROUP BY Ho.Descripcion, O.Fecha, C.Decripcion
    ORDER BY Ho.Descripcion

I think what happens is that you are not giving an identifier to your second query, that's why it gives you any value, for example, and it is what I imagine, you must pass it a left join instead of using it in the select:

SELECT Ho.Descripcion as Hotel, O.Fecha, C.Decripcion, SUM(Cantidad_ocupantes) as 'Habitaciones ocupadas',

IFNULL(Oc.cnt,0) as 'Habitaciones Libres' 

FROM OCUPACION AS O
INNER JOIN HABITACIONES AS H
    ON (O.Codigo_habitacion = H.Codigo_habitacion)
INNER JOIN HOTELES AS Ho
    ON (H.Codigo_hotel = Ho.Codigo_hotel)
INNER JOIN CATEGORIAS AS C
    ON (C.Codigo_categoria = H.Codigo_categoria)
LEFT JOIN
(select identificador,COUNT(Cantidad_ocupantes) AS cnt from Ocupacion where Cantidad_ocupantes = 0 GROUP BY identificador) AS Oc ON H.Codigo_habitacion = Oc.identificador

WHERE O.Fecha BETWEEN @Fecha_Reserva_Desde and @Fecha_Reserva_Hasta 

GROUP BY Ho.Descripcion, O.Fecha, C.Decripcion
ORDER BY Ho.Descripcion;

Where Identifier is as a is the result of occupation with the corresponding Row (for what I can see would be H.codigo_habitacion).

In any case if you put the test tables in a SQL we would gladly guide you a little more.

The results will depend a lot on how you have organized your data model. In the same way, you can do things with greater or lesser ease according to how your tables are organized.

I would imagine a data model more or less like this:

• A table hoteles in which you would have a fixed column called habitaciones . In this column you would have the total number of rooms available to the hotel. It would be the field that would be used to calculate the total of free rooms, be the total of occupied.
• A table called reservas in which you would record the date of entry, the date of departure, the number of the room, the hotel id, the number of people occupying the room, etc.

The model could be extended more clearly, having for example a table habitaciones where the type of room, number, etc. would be indicated.

Here I show you a model sketch, the PROCEDURE as you can see supports two parameters that would be two date ranges to look for available and occupied rooms.

The code can perhaps be improved in many aspects, the idea is to show a possible solution path.

I hope it serves you.

Código: Ver Demo

-- Procedure que busca habitaciones libres y ocupadas
-- en un rango de fechas
-- Ejemplo de uso:  EXEC verLibres '01.06.2017','03.06.2017';


CREATE PROCEDURE verLibres
    @NewEntrada DATETIME,
    @NewSalida DATETIME
AS
    SELECT hs.hotel_id, hs.hotel_nom, h.libres, h.ocupadas, 
    @NewEntrada as desde, @NewSalida as hasta 
    FROM hoteles hs 
        INNER JOIN 
        (
        SELECT COUNT(*) as ocupadas, (MAX(ht.habitaciones) - COUNT(*)) as libres , MAX(ht.hotel_id) as hotel_id
        FROM reservas AS r
        INNER JOIN hoteles as ht
        ON r.hotel_id = ht.hotel_id
        WHERE
            (@NewEntrada < r.fecha_entrada 
            OR
             @NewEntrada > r.fecha_salida)
            AND
            (@NewSalida < r.fecha_entrada 
            OR
             @NewSalida > r.fecha_salida)
        GROUP BY  ht.hotel_id
        ) 
        AS h ON hs.hotel_id=h.hotel_id
GO


CREATE TABLE  hoteles
 ( 
    hotel_id          INT              NOT NULL  , 
    hotel_nom         VARCHAR(20)          NULL  , 
    habitaciones      INT              NOT NULL  
 );


INSERT INTO hoteles (hotel_id, hotel_nom, habitaciones) 
    VALUES 
            (1,'ISTEA 1',17400),
            (2,'ISTEA 2',17400);


-- Mostrar registros en la tabla hoteles

SELECT * FROM hoteles;



CREATE TABLE  reservas
 ( 
    reserva_id        INT              NOT NULL  , 
    hotel_id          INT              NOT NULL  , 
    habitacion_id     INT              NOT NULL  ,
    ocupantes         INT              NOT NULL  ,
    fecha_entrada     DATETIME         NOT NULL  ,
    fecha_salida      DATETIME         NOT NULL  


 );


INSERT INTO reservas 
    (
    reserva_id, hotel_id, habitacion_id, 
    ocupantes, fecha_entrada, fecha_salida
    ) 
    VALUES 
    (1,1,65,2,'2017-06-01','2017-06-03'),
    (2,1,23,1,'2017-06-01','2017-06-03'),
    (3,1,90,2,'2017-06-01','2017-06-04'),
    (4,2,40,1,'2017-06-01','2017-06-05'),
    (5,2,86,2,'2017-06-01','2017-06-02');

-- Mostrando todas las reservas
SELECT * FROM reservas;


-- Probando la procedure

EXEC verLibres '01.06.2017','03.06.2017';

Resultados

--Tabla hoteles

hotel_id    hotel_nom   habitaciones
1           ISTEA 1     17400
2           ISTEA 2     17400

--Tabla reservas

reserva_id  hotel_id    habitacion_id   ocupantes   fecha_entrada           fecha_salida
1           1           65                  2       01.06.2017 00:00:00     03.06.2017 00:00:00
2           1           23                  1       01.06.2017 00:00:00     03.06.2017 00:00:00
3           1           90                  2       01.06.2017 00:00:00     04.06.2017 00:00:00
4           2           40                  1       01.06.2017 00:00:00     05.06.2017 00:00:00
5           2           86                  2       01.06.2017 00:00:00     02.06.2017 00:00:00

--Habitaciones libres y disponibles en rango de fechas

hotel_id    hotel_nom   libres  ocupadas    desde                           hasta
1           ISTEA 1     17397       3       06.01.2017 00:00:00             06.03.2017 00:00:00
2           ISTEA 2     17398       2       06.01.2017 00:00:00             06.03.2017 00:00:00