Sql Stored procedure not working in production Server

Question

1.00/5 (1 vote)

See more:

Hi everyone
i have written one stored procedure in sql which works perfectly fine in my local server for given all conditions but in production server its not working for one condition.

if i tried to execute for same condition in my local sql server for it works fine. but in production server its not working what may be the reason please help to solve this one

Below is my Stored Procedure....This SP is working For my all Conditions Its not showing any error in my Local Database and Testing Databse but in Production Server its displaying Blank table even data is there in table. for below given condition.

SQL

DECLARE @return_value int

EXEC    @return_value = [dbo].[Check_Availability]
        @ChkIn = 22,
        @ChkOut = 6,
        @Arrdate1 = N'2014-04-12 00:00:00.000',
        @Arrdate2 = N'2014-04-13 00:00:00.000',
        @ChkInDateTime = N'2014-04-12 22:00:00.000',
        @ChkOutDateTime = N'2014-04-13 07:00:00.000'

SELECT  'Return Value' = @return_value

GO

SQL

ALTER PROCEDURE [dbo].[Check_Availability] 
@ChkIn int,
@ChkOut int,
@Arrdate1 datetime,
@Arrdate2 datetime,
@ChkInDateTime datetime,
@ChkOutDateTime datetime
AS
BEGIN
SET NOCOUNT ON;
declare @rowcount int
declare @roomtypetemp int
declare @stringtemp varchar(100)
declare @chkintime datetime;
declare @chkouttime datetime;
declare @fromtime varchar(50);
declare @totime varchar(50);
declare @differenceslots int;
set @chkintime=@ChkInDateTime;
set @chkouttime=@ChkOutDateTime;


DECLARE @outTable as Table([Room Type] varchar(20),[Description] varchar(100),[Max No Of People] int,
[No_Of Rooms] int,free int,[Min_Price]  varchar(50),PriceType int,[Max No of Childrens] int);

set @fromtime=(select CONVERT(VARCHAR(8),CONVERT(datetime,@ChkInDateTime),108) );
set @totime=(select CONVERT(VARCHAR(8),CONVERT(datetime,@ChkOutDateTime),108) );

declare @RoomType as nvarchar(20),@Description  varchar(100), @Noofrooms int,@Noofpersons as int,@PriceType as int,@Minprice decimal(38,20),@freeslots int,@noofchidrens int;
	
	if(@ChkIn=@ChkOut)
	set @rowcount=(select count(SlotId) FROM LifeStyleWebAvailability where
	(SlotId= @ChkIn) and DocumentType=1 and (AvailabilityDate between @Arrdate1 and @Arrdate2) ) ; 
else if(@ChkIn>@ChkOut)
	set @rowcount=(select count(SlotId) FROM LifeStyleWebAvailability where
	((SlotId between @ChkIn+1 and 23)) and DocumentType=1 and (AvailabilityDate = @Arrdate1)) 
	+
	(select count(SlotId) FROM LifeStyleWebAvailability where
	((SlotId between 0 and @ChkOut)) and DocumentType=1 and (AvailabilityDate = @Arrdate2)); 
else
	set @rowcount=(select count(SlotId) FROM LifeStyleWebAvailability where
	((SlotId between @ChkIn+1 and  @ChkOut)) and DocumentType=1 and (AvailabilityDate between @Arrdate1 and @Arrdate2) ) ; 

delete   from @outTable;
	 INSERT INTO @outTable([Room Type],[Description],[Max No Of People] ,[No_Of Rooms],[Max No of Childrens])	 
select [Room Type],[Description],[Max No Of People],[No_Of Rooms],[Max No of Children] from LifeStyleWebRoomType

--if(@fromtime<'10:00:00')
--begin
--set @fromtime=(select SUBSTRING(@fromtime,2,8));
--end
--if(@totime<'10:00:00')
--begin
--set @totime=(select SUBSTRING(@totime,2,8));
--end	

print @fromtime;
print @totime;
	
			declare cursor1 cursor for
			select [Room Type],[Description],[Max No Of People] ,[No_Of Rooms],[Max No of Childrens] from @outTable
			OPEN cursor1 
				FETCH NEXT FROM cursor1 INTO @RoomType,@Description, @Noofpersons,@Noofrooms,@noofchidrens
					WHILE @@FETCH_STATUS = 0
			BEGIN
			set @PriceType=-1;		
		set @freeslots=0;
			if(EXISTS(Select COUNT(*) from LifeStyleWebRoomPrice where [Room Type]=@RoomType having Count(*)=1))
				BEGIN
				print 'if'
					if( exists(select pricetype from LifeStyleWebRoomPrice where [pricetype]=0 and [Room Type]=@RoomType and 
					CONVERT(VARCHAR(8),[Valid From],108)<= @fromtime and CONVERT(VARCHAR(8),[Valid To],108)>= @totime and @fromtime<=@totime))
					BEGIN
					select @PriceType=pricetype,@Minprice=Cast([Min Price] As  varchar(50)) from LifeStyleWebRoomPrice  where [pricetype]=0 and [Room Type]=@RoomType and 
					CONVERT(VARCHAR(8),[Valid From],108)<= @fromtime and CONVERT(VARCHAR(8),[Valid To],108)>= @totime and @fromtime<=@totime
					END
					else if( exists(select pricetype from LifeStyleWebRoomPrice where [pricetype]=1 and [Room Type]=@RoomType and 
					CONVERT(VARCHAR(8),[Valid From],108)<= @fromtime and CONVERT(VARCHAR(8),[Valid To],108)>= @totime and @fromtime>=@totime))	
					select @PriceType=pricetype,@Minprice=Cast([Min Price] As  varchar(50)) from LifeStyleWebRoomPrice where [pricetype]=1 and [Room Type]=@RoomType and 
					CONVERT(VARCHAR(8),[Valid From],108)<= @fromtime and CONVERT(VARCHAR(8),[Valid To],108)>= @totime and @fromtime>=@totime
					
				END
				ELSE
				BEGIN
				print 'else 2'
				if(EXISTS(SELECT  top 1 price2.[Valid From],price1.[Valid To]FROM LifeStyleWebRoomPrice price1, LifeStyleWebRoomPrice price2
					WHERE price1.[Room Type]= price2.[Room Type] AND price2.[Room Type]=@RoomType and CONVERT(VARCHAR(8),price2.[Valid From],108)>= CONVERT(VARCHAR(8),price1.[Valid To],108)
					and CONVERT(VARCHAR(8),price2.[Valid To],108)= CONVERT(VARCHAR(8),price1.[Valid From],108)	))
					BEGIN
						if( exists(select pricetype from LifeStyleWebRoomPrice where [pricetype]=0 and [Room Type]=@RoomType and 
						CONVERT(VARCHAR(8),[Valid From],108)<= @fromtime and CONVERT(VARCHAR(8),[Valid To],108)>= @totime and @fromtime<=@totime))
						BEGIN		
						select @PriceType=pricetype,@Minprice=Cast([Min Price] As  varchar(50)) from LifeStyleWebRoomPrice where [pricetype]=0 and [Room Type]=@RoomType and 
						CONVERT(VARCHAR(8),[Valid From],108)<= @fromtime and CONVERT(VARCHAR(8),[Valid To],108)>= @totime and @fromtime<=@totime
						END
						else if( exists(select pricetype from LifeStyleWebRoomPrice where [pricetype]=1 and [Room Type]=@RoomType and 
						CONVERT(VARCHAR(8),[Valid From],108)<= @fromtime and CONVERT(VARCHAR(8),[Valid To],108)>= @totime and @fromtime>=@totime))
						BEGIN		
						select @PriceType=pricetype,@Minprice=Cast([Min Price] As  varchar(50)) from LifeStyleWebRoomPrice where [pricetype]=1 and [Room Type]=@RoomType and 
						CONVERT(VARCHAR(8),[Valid From],108)<= @fromtime and CONVERT(VARCHAR(8),[Valid To],108)>= @totime and @fromtime>=@totime
						END
						
						else
						BEGIN				
							print 'else'
							if(Exists(SELECT  top 1 price2.[Valid From],price1.[Valid To] FROM LifeStyleWebRoomPrice price1, LifeStyleWebRoomPrice price2
							WHERE price1.[Room Type]= price2.[Room Type] AND price2.[Room Type]=@RoomType and CONVERT(VARCHAR(8),price2.[Valid From],108)>= CONVERT(VARCHAR(8),price1.[Valid To],108)
							and CONVERT(VARCHAR(8),price2.[Valid To],108)= CONVERT(VARCHAR(8),price1.[Valid From],108) and price2.[Valid From]=price1.[Valid To]))
								
							SELECT  top 1 @PriceType=price1.pricetype,@Minprice=Cast(price1.[Min Price] As varchar(50))FROM LifeStyleWebRoomPrice price1, LifeStyleWebRoomPrice price2
							WHERE price1.[Room Type]= price2.[Room Type] AND price2.[Room Type]=@RoomType and CONVERT(VARCHAR(8),price2.[Valid From],108)>= CONVERT(VARCHAR(8),price1.[Valid To],108)
							and CONVERT(VARCHAR(8),price2.[Valid To],108)= CONVERT(VARCHAR(8),price1.[Valid From],108) and price2.[Valid From]=price1.[Valid To];

							else
							BEGIN				

							if(@fromtime < @totime)
							BEGIN								
								
								if(exists(SELECT  price1.pricetype FROM LifeStyleWebRoomPrice price1, LifeStyleWebRoomPrice price2
								WHERE price1.[Room Type]= price2.[Room Type] AND price2.[Room Type]=@RoomType and CONVERT(VARCHAR(8),price2.[Valid From],108)>= CONVERT(VARCHAR(8),price1.[Valid To],108)
								and CONVERT(VARCHAR(8),price2.[Valid To],108)= CONVERT(VARCHAR(8),price1.[Valid From],108)
								and price2.[Valid From]>price1.[Valid To]  
								and CONVERT(VARCHAR(8),(price1.[Valid To]+'01:00:00'),108)  not between @fromtime and @totime
								and CONVERT(VARCHAR(8),(price2.[Valid From]-'01:00:00'),108) not  between @fromtime and @totime	))
								
								select @PriceType=pricetype,@Minprice=Cast([Min Price] As  varchar(50)) from LifeStyleWebRoomPrice where  [Room Type] =@RoomType and PriceType=1;				
							END
							else
							BEGIN
							
							if(exists(SELECT  price2.pricetype FROM LifeStyleWebRoomPrice price1, LifeStyleWebRoomPrice price2
								WHERE price1.[Room Type]= price2.[Room Type] AND price2.[Room Type]=@RoomType and CONVERT(VARCHAR(8),price2.[Valid From],108)>= CONVERT(VARCHAR(8),price1.[Valid To],108)
								and CONVERT(VARCHAR(8),price2.[Valid To],108)= CONVERT(VARCHAR(8),price1.[Valid From],108)
								and price2.[Valid From]>price1.[Valid To]  and (price1.pricetype=1 or price2.pricetype=1)
								and @fromtime   between CONVERT(VARCHAR(8),(price2.[Valid From]),108) and '23:59:29' 
								and @totime    between '00:00:00'  and CONVERT(VARCHAR(8),(price1.[Valid To]),108)))
								
								select @PriceType=pricetype,@Minprice=Cast([Min Price] As  varchar(50)) from LifeStyleWebRoomPrice where  [Room Type] =@RoomType and PriceType=1;				
							END
									
							END
						
						END
					END
				END
			
			
			update @outTable set PriceType=@PriceType ,Min_Price =@Minprice,free = @rowcount
			  where  [Room Type]=@RoomType
			  
			  Declare @totalrooms as int;
			  set @totalrooms=0;
			  
				if(@ChkIn=@ChkOut)
				BEGIN
					select top 1 @totalrooms=Count(slotid) FROM LifeStyleWebAvailability where
					DocumentType=2 and AvailabilityDate between  @Arrdate1 and @Arrdate2
					and SlotId = @ChkIn and RoomType=@RoomType	
					GROUP by slotid order by Count(slotid) desc
				END
				else if(@ChkIn>@ChkOut)
				BEGIN
					select top 1 @totalrooms=Count(slotid) FROM LifeStyleWebAvailability where
					DocumentType=2 and AvailabilityDate between  @Arrdate1 and @Arrdate2
					and ((SlotId between @ChkIn and 23) or (SlotId between 0 and @ChkOut)) and RoomType=@RoomType	
					GROUP by slotid order by Count(slotid) desc					
				End 
				else
				BEGIN
					select top 1 @totalrooms=Count(slotid) FROM LifeStyleWebAvailability where
					DocumentType=2 and AvailabilityDate between  @Arrdate1 and @Arrdate2
					and (SlotId between @ChkIn+1 and  @ChkOut) and RoomType=@RoomType	
					GROUP by slotid order by Count(slotid) desc
				END
	
			update @outTable set [No_Of Rooms]=(@Noofrooms-@totalrooms)
			  where  [Room Type]=@RoomType			  
			  
			  
			FETCH NEXT FROM cursor1 INTO  @RoomType,@Description, @Noofpersons,@Noofrooms,@noofchidrens
	END

close cursor1
deallocate cursor1

--select * from @outTable
select [Room Type],[Description],[Max No Of People] ,[No_Of Rooms],free,Min_Price,PriceType,[Max No of Childrens] from @outTable where PriceType>=0 and (free>0 and [No_Of Rooms]>0);

END
Thanks& Regards

Pradeep

Posted 7-Apr-14 3:16am

Pradeepkumar patil sonu

Updated 8-Apr-14 0:28am

Er. Puneet Goel

v3

Add a Solution

Comments

Suk@nta 7-Apr-14 9:28am

it will be better if you put the query here

Andrius Leonavicius 7-Apr-14 11:36am

Could you post the code of your stored procedure? And what exactly do you mean by saying "not working"?

Raul Iloc 7-Apr-14 13:43pm

You should provide the SQL code and the test condition (in your question above) in order to can help you!

ashok rathod 8-Apr-14 0:51am

i would also suggest to put your procedure and had to show which condition is exactly not working,because there may me many scenarios due to which your said problem might occur...

Er. Puneet Goel 8-Apr-14 4:18am

What error you getting when you execute?

Try running SP like Exe SpName parameter for error

Pradeepkumar patil sonu 8-Apr-14 6:08am

GO/****** Object: StoredProcedure [dbo].[Check_Availability] Script Date: 04/08/2014 11:26:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Check_Availability]
@ChkIn int,
@ChkOut int,
@Arrdate1 datetime,
@Arrdate2 datetime,
@ChkInDateTime datetime,
@ChkOutDateTime datetime
AS
BEGIN
SET NOCOUNT ON;
declare @rowcount int
declare @roomtypetemp int
declare @stringtemp varchar(100)
declare @chkintime datetime;
declare @chkouttime datetime;
declare @fromtime varchar(50);
declare @totime varchar(50);
declare @differenceslots int;
set @chkintime=@ChkInDateTime;
set @chkouttime=@ChkOutDateTime;

DECLARE @outTable as Table([Room Type] varchar(20),[Description] varchar(100),[Max No Of People] int,
[No_Of Rooms] int,free int,[Min_Price] varchar(50),PriceType int,[Max No of Childrens] int);

set @fromtime=(select CONVERT(VARCHAR(8),CONVERT(datetime,@ChkInDateTime),108) );
set @totime=(select CONVERT(VARCHAR(8),CONVERT(datetime,@ChkOutDateTime),108) );

declare @RoomType as nvarchar(20),@Description varchar(100), @Noofrooms int,@Noofpersons as int,@PriceType as int,@Minprice decimal(38,20),@freeslots int,@noofchidrens int;

if(@ChkIn=@ChkOut)
set @rowcount=(select count(SlotId) FROM LifeStyleWebAvailability where
(SlotId= @ChkIn) and DocumentType=1 and (AvailabilityDate between @Arrdate1 and @Arrdate2) ) ;
else if(@ChkIn>@ChkOut)
set @rowcount=(select count(SlotId) FROM LifeStyleWebAvailability where
((SlotId between @ChkIn+1 and 23)) and DocumentType=1 and (AvailabilityDate = @Arrdate1))
+
(select count(SlotId) FROM LifeStyleWebAvailability where
((SlotId between 0 and @ChkOut)) and DocumentType=1 and (AvailabilityDate = @Arrdate2));
else
set @rowcount=(select count(SlotId) FROM LifeStyleWebAvailability where
((SlotId between @ChkIn+1 and @ChkOut)) and DocumentType=1 and (AvailabilityDate between @Arrdate1 and @Arrdate2) ) ;

delete from @outTable;
INSERT INTO @outTable([Room Type],[Description],[Max No Of People] ,[No_Of Rooms],[Max No of Childrens])
select [Room Type],[Description],[Max No Of People],[No_Of Rooms],[Max No of Children] from LifeStyleWebRoomType

--if(@fromtime<'10:00:00')
--begin
--set @fromtime=(select SUBSTRING(@fromtime,2,8));
--end
--if(@totime<'10:00:00')
--begin
--set @totime=(select SUBSTRING(@totime,2,8));
--end

print @fromtime;
print @totime;

declare cursor1 cursor for
select [Room Type],[Description],[Max No Of People] ,[No_Of Rooms],[Max No of Childrens] from @outTable
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @RoomType,@Description, @Noofpersons,@Noofrooms,@noofchidrens
WHILE @@FETCH_STATUS = 0
BEGIN
set @PriceType=-1;
set @freeslots=0;
if(EXISTS(Select COUNT(*) from LifeStyleWebRoomPrice where [Room Type]=@RoomType having Count(*)=1))
BEGIN
print 'if'
if( exists(select pricetype from LifeStyleWebRoomPrice where [pricetype]=0 and [Room Type]=@RoomType and
CONVERT(VARCHAR(8),[Valid From],108)<= @fromtime and CONVERT(VARCHAR(8),[Valid To],108)>= @totime and @fromtime<=@totime))
BEGIN
select @PriceType=pricetype,@Minprice=Cast([Min Price] As varchar(50)) from LifeStyleWebRoomPrice where [pricetype]=0 and [Room Type]=@RoomType and
CONVERT(VARCHAR(8),[Valid From],108)<= @fromtime and CONVERT(VARCHAR(8),[Valid To],108)>= @totime and @fromtime<=@totime
END
else if( exists(select pricetype from LifeStyleWebRoomPrice where [pricetype]=1 and [Room Type]=@RoomType and
CONVERT(VARCHAR(8),[Valid From],108)<= @fromtime and CONVERT(VARCHAR(8),[Valid To],108)>= @totime and @fromtime>=@totime))
select @PriceType=pricetype,@Minprice=Cast([Min Price] As varchar(50)) from LifeStyleWebRoomPrice where [pricetype]=1 and [Room Type]=@RoomType and
CONVERT(VARCHAR(8),[Valid From],108)<= @fromtime and CONVERT(VARCHAR(8),[Valid To],108)>= @totime and @fromtime>=@totime

END
ELSE
BEGIN
print 'else 2'
if(EXISTS(SELECT top 1 price2.[Valid From],pric

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)