我目前在两张表里面做搜索,如下:CREATE PROCEDURE SearchHotel
@BeginTime varchar(20),
@EndTime varchar(20),
@Province varchar(10),
@City varchar(10),
@Area varchar(10),
@Star varchar(10),
@HotelName varchar(20)='%'
ASselect Fi_HotelID,Fs_Province,Fs_City,Fs_Area,Fi_Star,Fs_Link,Fs_Tel,Fs_Local,Fs_Pic1,Fi_OurPoint,Fs_Intro_More,Fs_HotelName from THotel
where Fi_HotelID in(select Fi_HotelID From TRoom where Fs_BeginTime between @BeginTime And @EndTime) And Fs_Province=@Province
And Fs_City=@City And Fs_Area=@Area And Fi_Star=@Star And Fs_HotelName like @HotelName
go以下参数都可能为空,@BeginTime
@EndTime
@Province
@City
@Area
@Star
请问怎样在存储过程里面做判断?如果客户端提交的参数为空在查询的时候就忽略掉这一项。谢谢!
@BeginTime varchar(20),
@EndTime varchar(20),
@Province varchar(10),
@City varchar(10),
@Area varchar(10),
@Star varchar(10),
@HotelName varchar(20)='%'
ASselect Fi_HotelID,Fs_Province,Fs_City,Fs_Area,Fi_Star,Fs_Link,Fs_Tel,Fs_Local,Fs_Pic1,Fi_OurPoint,Fs_Intro_More,Fs_HotelName from THotel
where Fi_HotelID in(select Fi_HotelID From TRoom where Fs_BeginTime between @BeginTime And @EndTime) And Fs_Province=@Province
And Fs_City=@City And Fs_Area=@Area And Fi_Star=@Star And Fs_HotelName like @HotelName
go以下参数都可能为空,@BeginTime
@EndTime
@Province
@City
@Area
@Star
请问怎样在存储过程里面做判断?如果客户端提交的参数为空在查询的时候就忽略掉这一项。谢谢!
不知道此方法行不通不?
這樣寫條件不行嗎?
where Fi_HotelID in(select Fi_HotelID From TRoom where Fs_BeginTime between @BeginTime And @EndTime or @BeginTime is null or @EndTime is null) And (Fs_Province=@Province or @Province is null)
And (Fs_City=@City or @City is null) And (Fs_Area=@Area or @Area is null) And (Fi_Star=@Star or @Star is null) And (Fs_HotelName like @HotelName or @HoteName is null)還是說我理解錯題意了?
Create Table TEST(ID Int,Name Varchar(10))
Insert TEST Select 1,'aa'
Union All Select 2,'bb'
Union All Select 3,'cc'
GO
--建立存儲過程
Create Proc SP_Test(@ID Int=0,@Name Varchar(10)=Null)
As
Begin
Declare @S Varchar(1000)
Select @S='Select * From TEST Where 1=1'
If @ID<>0
Set @S=@S+' And ID='+Rtrim(@ID)
If @Name Is Not Null
Set @S=@S+' And Name='''+@Name+''''
EXEC(@S)
End
GO
--測試
EXEC SP_Test
EXEC SP_Test 1
EXEC SP_Test 2,'bb'
GO
--刪除測試環境
Drop Table TEST
Drop Proc SP_Test
--結果
/*
ID Name
1 aa
2 bb
3 ccID Name
1 aaID Name
2 bb
*/
@BeginTime varchar(20),
@EndTime varchar(20),
@Province varchar(10),
@City varchar(10),
@Area varchar(10),
@Star varchar(10),
@HotelName varchar(20)='%'
AS
declare @sql varchar(2000)
select @sql='select Fi_HotelID,Fs_Province,Fs_City,Fs_Area,Fi_Star,Fs_Link,Fs_Tel,Fs_Local,Fs_Pic1,Fi_OurPoint,Fs_Intro_More,Fs_HotelName
from THotel
where Fi_HotelID in(
select Fi_HotelID
From TRoom
where 1=1 '
if isnull(@BeginTime,'')<>'' and isnull(@EndTime,'')<>''
select @sql=@sql+' and Fs_BeginTime between '''+@BeginTime+''' And '''+@EndTime+''''
if isnull(@Citym,'')<>''
select @sql=@sql+' And Fs_Province='''+@Province+''''
if isnull(@Citym,'')<>''
select @sql=@sql+' And Fs_City='''+@City+''''
if isnull(@Area,'')<>''
select @sql=@sql+' And Fs_Area='''+@Area+''''
if isnull(@Star,'')<>''
select @sql=@sql+' And Fi_Star='''+@Star+''''
select @sql=@sql+' And Fs_HotelName like''%'+@Star+''''
exec(@sql)
if Province <>"" then
SQL=SQL&" and Fi_Province =Province "
end if
...
...这样的。。
也就是说,执行存储过程时,输入完整了应该是:exec SearchHotel '2006-5-1','2006-5-3','239','15','45','5','%酒%'但如果客户输入不完整,就可能是exec SearchHotel '2006-5-1','2006-5-3','','','','','%酒%'
exec SearchHotel '2006-5-1','2006-5-3','239','15','','5','%酒%'
exec SearchHotel '2006-5-1','2006-5-3','239','15','45','5','%%'
...
等等,不知道能不能从存储过程本身进行判断。。
@BeginTime varchar(20),
@EndTime varchar(20),
@Province varchar(10),
@City varchar(10),
@Area varchar(10),
@Star varchar(10),
@HotelName varchar(20)='%'
AS
declare @sql varchar(2000)
select @sql='select Fi_HotelID,Fs_Province,Fs_City,Fs_Area,Fi_Star,Fs_Link,Fs_Tel,Fs_Local,Fs_Pic1,Fi_OurPoint,Fs_Intro_More,Fs_HotelName
from THotel
where Fi_HotelID in(
select Fi_HotelID
From TRoom
where Fs_BeginTime between '''+@BeginTime+''' And '''+@EndTime+''')'if isnull(@City,'')<>''
select @sql=@sql+' And Fs_Province='''+@Province+''''
if isnull(@City,'')<>''
select @sql=@sql+' And Fs_City='''+@City+''''
if isnull(@Area,'')<>''
select @sql=@sql+' And Fs_Area='''+@Area+''''
if isnull(@Star,'')<>''
select @sql=@sql+' And Fi_Star='''+@Star+''''
if isnull(@HotelName,'')<>''
select @sql=@sql+' And Fs_HotelName like'''+@HotelName+''''
exec(@sql)开始日期和结束日期是必须的,所以无需判断。。测试成功!