Sql存储过程在生产服务器中不起作用 [英] Sql Stored procedure not working in production Server

查看：78
本文介绍了Sql存储过程在生产服务器中不起作用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

大家好吧

i已经在sql中编写了一个存储过程，在我的本地服务器中可以正常工作，但是在生产服务器中它不能用于一个条件。



如果我试图在我的本地sql server中执行相同的条件，它可以正常工作。但是在生产服务器中它不工作可能是原因请帮助解决这个问题







下面是我的存储过程....这个SP正在工作我的所有条件它没有在我的本地数据库和测试数据库中显示任何错误，但在生产服务器中它显示空白表甚至数据在表中。对于下面给出的条件。



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. 

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







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



\t\t

\t\tclose cursor1

\t\tdeallocate cursor1\t\t\t\t

\t

\t--select * from @outTable

\tselect [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



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

推荐答案

这篇关于Sql存储过程在生产服务器中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆