如何实现这样的多参数查询的存储过程，希望高手指教

[jm]

我需要写一个包含许多参数查询的存储过程，存储过程能根据传递来的参数执行WHERE后边的条件。
例如：有下面的存储过程：
create procedure
@counntryid integer,
@name char,
@typeid integer
@fee_max
@fee_min
as
select * from table
WHERE countryid = @countryid
AND name = @name
AND typeid = @typeid
AND fee <= fee_max
AND fee >= fee_min

我希望存储过程能够根据传递来的参数值自动执行AND后边相应的条件。
比如：当存储过程只收到@countryid和@name两个参数，那么就
执行：
WHERE countryid = @countryid
AND name = @name
而不执行下面的这三个条件
AND typeid = @typeid
AND fee <= fee_max
AND fee >= fee_min

[bee6803]

http://community.csdn.net/Expert/topic/4792/4792485.xml?temp=.2153894
请参看上面的

[LouisXIV]

改写条件为

WHERE
(countryid = @countryid or @countryid is null)
AND (name = @name or @name is null)
AND (typeid = @typeid or @typeid is null)
AND (fee <= @fee_max or @fee_max is null)
AND (fee >= @fee_min or @fee_min is null)

[gaobochina]

模糊查询！
我用的方法是 like
if @name=''
set @name='%%'

if @countryid=''
set @countryid='%%'

select * from tablename where name like @name and counttryid like @counttryid

以前用的方法
declare @sqlwhere varchar(1024)
set @sqlwhere='1=1 '
if @name=''
set @sqlwhere=@sqlwhere+' and name='+@name

if @name1=''
set @sqlwhere=@sqlwhere+' and name1='+@name1

if @name2=''
set @sqlwhere=@sqlwhere+' and name2='+@name2


set @sqlwhere='select * from tablename where '+@sqlwhere

exec(@sqlwhere)

[gaobochina]

修正一下
以前用的方法
declare @sqlwhere varchar(1024)
set @sqlwhere='1=1 '
if @name<>''
set @sqlwhere=@sqlwhere+' and name='+@name

if @name1<>''
set @sqlwhere=@sqlwhere+' and name1='+@name1

if @name2<>''
set @sqlwhere=@sqlwhere+' and name2='+@name2


set @sqlwhere='select * from tablename where '+@sqlwhere

exec(@sqlwhere)

[liangpei2008]

--这样试一下！
Create PROC Pro_test
@CounntryId Integer=NULL,
@Name VarChar(10)=NULL,
@TypeId Integer=NULL,
@Fee_Max Integer=NULL,
@Fee_Min Integer=NULL
AS
SET NOCOUNT ON
DECLARE @sql varchar(8000)
SET @sql='SELECT * FROM Table1 WHERE 1=1'
IF @CounntryId IS NOT NULL
SET @sql=@sql+' AND Countryid='+Cast(@CounntryId as Varchar)+''
IF @Name IS NOT NULL
SET @sql=@sql+' AND Name ='''+@Name+''''
IF @TypeId IS NOT NULL
SET @sql=@sql+' AND Name ='+Cast(@TypeId as Varchar)+''
IF @Fee_Max IS NOT NULL
SET @sql=@sql+' AND Fee_Max <='+Cast(@Fee_Max as Varchar)+''
IF @Fee_Min IS NOT NULL
SET @sql=@sql+' AND Fee_Min >='+Cast(@Fee_Min as Varchar)+''
Print @sql --Exec (@sql)
SET NOCOUNT OFF

---测试
Exec pro_test 1,'2'