How do I pass values from a table to a stored procedure as parameters?

Question

I'm struggling with something that looks very simple, and yet I'm not finding any solution:

1. I have Table called Hotels, which the first column in it, is = [hotels_id] [int]
2. I have stored procedure that depends on hotels_id value

Now in the Stored Procedure, I've typed just one value (1) as example, let say I have something like 50 rows.

There is any way to pass all the rows from the table to the SP as parameter each one at the time? I mean one by one.

ALTER PROCEDURE [dbo].[spAvailableRooms]
AS
BEGIN
-- Derived tables
;with

DatesTable as
    (SELECT top (100) PERCENT Dates.dates_id as Id, Dates.dates_date as Date FROM Dates order by Dates.dates_id),

 AvaliablePerHotel as
    (SELECT top (100)percent Available_since AS Since, Available_value AS Value, Available_hotel as Hotel
                    FROM Available_rooms 
                    where  Available_hotel =1 --(HERE I NEED THE VALUES FROM TABLE)
                    ORDER BY Available_hotel, Available_since),

AllDays as
    (Select top (100)percent Id, Date, Value as Rooms, iif(value is null, '0' ,'1') as New, Hotel
        From DatesTable left JOIN AvaliablePerHotel ON Id = Since
        order by id),

AvailableGroups as
    (Select top (100)percent Hotel, Id, Date, Rooms, (sum(isnull(cast(new as float),0))over(order by id)) as RowGroup
    From AllDays
    order by id)
--

-- Query itself

Select Id, Date, iif(Rooms is null,(first_value(rooms) over (partition by RowGroup order by Id)) , Rooms) as  AvailableRooms,
        iif(Hotel is null,(first_value(Hotel) over (partition by RowGroup order by Id)) , Hotel) as  Hotel
From AvailableGroups
order by id

END

Answer 1

If you want to pass values as you said one by one then cursor is your best option

declare @id int

DECLARE cursor CURSOR FOR   
SELECT hotels_id
FROM Hotels

OPEN cursor  

FETCH NEXT FROM cursor   
INTO @id

WHILE @@FETCH_STATUS = 0  
BEGIN  

    exec [dbo].[spAvailableRooms] @id

    FETCH NEXT FROM cursor   
    INTO @id
END   
CLOSE cursor;  
DEALLOCATE cursor;  

Then change in your procedure pass a paramter

ALTER PROCEDURE [dbo].[spAvailableRooms] @id int

And replace where you had hardcoded "1" in your code with @id

Note that cursors are single threaded as your table grows the execution time will grow pretty rapidly.