Stored Procedure problem!
Hi
There is a strange problem happening with stored procedure.
I have written a stored procedure for my application to update the database.
This stored procedure is working fine in my machine(local server).
But when I hosted it on the Live server. It doesn't work. What may be the problem. Is there anything related to SQL Server used in the Live site.
Awaiting for the response.
Thanks & Regards
Pradeep
The Stored procedure code
Hi MAK
Quoting down is the Stored procedure
CREATE procedure incntrl_create (@hotel_id as int, @roomtypeid as int, @start_date as datetime, @end_date as datetime,@status as varchar(20), @inc as int , @dec as int, @output as varchar(20)) as
declare @max_rooms as int;
declare @inventoryid as int;
declare @inet_avail as int;
declare @i_avail as int;
declare @a_avail as int;
declare @cur_avail as int;
declare @invid as int;
declare @c_avail as int;
declare @countStr as int;
declare @inStr as varchar(20);
declare @updStr as varchar(20);
declare @index as int;
set @countStr = 0;
set @i_avail =0;
set @c_avail = 0;
set @inet_avail = 0;
set @cur_avail = 0;
set @index=0;
select @max_rooms = max_rooms from rd_roomtype
where hotel_id = @hotel_id
and roomtypeid = @roomtypeid
/*print @max_rooms */
while (@start_date <= @end_date)
BEGIN
print @index;
set @invid = 0
set @inet_avail = 0
set @cur_avail = 0
set @i_avail = 0
set @c_avail = 0
set @countStr = @countStr + 1
select @invid = inventoryid, @inet_avail = inet_avail, @cur_avail = cur_avail from rd_inventory
where hotel_id = @hotel_id
and roomtypeid = @roomtypeid
and start_date = @start_date
if @inet_avail is NULL set @inet_avail = 0
if @cur_avail is NULL set @cur_avail = 0
if @inc > 0
BEGIN
select @i_avail = @inet_avail + @inc;
select @c_avail = @cur_avail + @inc;
END
if @dec > 0
BEGIN
select @i_avail = @inet_avail - @dec;
select @c_avail = @cur_avail - @dec;
END
if @dec = 0 and @inc = 0
BEGIN
select @i_avail = @inet_avail;
select @c_avail = @cur_avail;
END
if @i_avail < 0
set @i_avail = 0
if @i_avail > @max_rooms
set @i_avail = @max_rooms
if @c_avail < 0
set @c_avail = 0;
if @c_avail > @max_rooms
set @c_avail = @max_rooms;
if @invid <> 0
begin
/* print @start_date
print @i_avail
print @c_avail */
Update rd_inventory set inet_avail=@i_avail, cur_avail=@c_avail, status=@status where hotel_id=@hotel_id and roomtypeid=@roomtypeid and start_date=@start_date
end
else
begin
Insert into rd_inventory (hotel_id, roomtypeid,inet_avail,cur_avail, start_date,status) values( @hotel_id,@roomtypeid,@i_avail,@c_avail,@start_dat e,@status)
end
set @start_date = DATEADD(day,1,@start_date)
set @index=@index+1;
END
IF @@ERROR <> 0
BEGIN
RETURN(3)
END
the table is rd_inventory
fields are
hotel_id,roomtypeid
inventoryid
inet_avail
cur_avail
start_date
end_date
status
the data is attached with this post.
Thanks
Prithvi