存储过程中怎么使用动态查询条件？

[csproj]

比如表
tGoods


keyid code name price
1 1.1.001 苹果 3
2 1.1.002 香蕉 2
3 1.2.001 橘子 4
4 2.1.001 葡萄 8
5 2.1.002 桃子 2


想写一个储存过程（只通过code和name查询记录）：
sp_chaxun ''，'苹果' --查询name为苹果的记录
sp_chaxun '2.1.001','' --查询code为2.1.001的记录
sp_chaxun '2.1.001','葡萄' --查询code为2.1.001并且name为香蕉的记录
我想这样写
create procedure sp_chaxun
@code varchar(20),
@name varchar(20)
As
select * from tGoods
where code = @code And name = @name

可是这样必须提供两个参数。
请问应该怎么写才对，谢谢大家！

[sgtzzc]

create procedure sp_chaxun 
@code varchar(20), 
@name varchar(20) 
As 
set @code=case when len(isnull(@code,''))=0 then null else @code end
set @name=case when len(isnull(@name,''))=0 then null else @name end
select * from tGoods 
where code = isnull(@code,code) And name = isnull(@name,name) 

[ws_hgo]

create procedure sp_chaxun 
@code varchar(20), 
@name varchar(20) 
As 
exec ('select * from tGoods where code='+@code+' And name ='+@name)

[--小F--]

[Quote=引用 1 楼 sgtzzc 的回复:]
SQL codecreateprocedure sp_chaxun@codevarchar(20),@namevarchar(20)Asset@code=casewhenlen(isnull(@code,''))=0thennullelse@codeendset@name=casewhenlen(isnull(@name,''))=0thennullelse@nameendselect*from ?-
[/Quote]

UP
执行的时候用动态语句就行了

[icelovey]

-- =============================================
-- Author:      T.O.P
-- Create date: 2009/11/23
-- Version:     SQL SERVER 2005
-- =============================================
create procedure sp_chaxun 
(
	@code varchar(20), 
	@name varchar(20) 
)
As 
DECLARE @sqlstr NVARCHAR(1000)

SET @sqlstr = 'select * from tGoods where '
	+(CASE WHEN @code IS NOT NULL AND @code<>'' THEN ' code = '''+@code+'''' ELSE '' END)
	+(CASE WHEN @name IS NOT NULL AND @name<>'' THEN (CASE WHEN @code IS NOT NULL AND @code<>'' THEN ' AND ' ELSE '' END)+' name = '''+@name+'''' ELSE '' END)

EXEC(@SQLSTR)
GO


if object_id('[tGoods]') is not null drop table [tGoods]
go
create table [tGoods]([keyid] int,[code] datetime,[name] varchar(4),[price] int)
insert [tGoods]
select 1,'1.1.001','苹果',3 union all
select 2,'1.1.002','香蕉',2 union all
select 3,'1.2.001','橘子',4 union all
select 4,'2.1.001','葡萄',8 union all
select 5,'2.1.002','桃子',2
GO

EXEC sp_chaxun '','苹果'      --查询name为苹果的记录 
EXEC sp_chaxun '2.1.001',''    --查询code为2.1.001的记录 
EXEC sp_chaxun '2.1.001','葡萄' --查询code为2.1.001并且name为香蕉的记录 

DROP PROCEDURE sp_chaxun
DROP TABLE [tGoods]

--测试结果:
/*
keyid       code                    name price
----------- ----------------------- ---- -----------
1           2001-01-01 00:00:00.000 苹果   3

(1 row(s) affected)

keyid       code                    name price
----------- ----------------------- ---- -----------
4           2001-02-01 00:00:00.000 葡萄   8

(1 row(s) affected)

keyid       code                    name price
----------- ----------------------- ---- -----------
4           2001-02-01 00:00:00.000 葡萄   8

(1 row(s) affected)


*/

[luoyoumou]

--老问题了

[华夏小卒]

--> 测试数据: #tGoods
if object_id('tGoods') is not null drop table tGoods
go
create table tGoods (keyid int,code datetime,name varchar(4),price int)
insert into tGoods
select 1,'1.1.001','苹果',3 union all
select 2,'1.1.002','香蕉',2 union all
select 3,'1.2.001','橘子',4 union all
select 4,'2.1.001','葡萄',8 union all
select 5,'2.1.002','桃子',2


if object_id('sp_chaxun') is not null drop proc sp_chaxun
go 
create proc sp_chaxun
@code varchar(20), 
@name varchar(20) 
as
declare @sql varchar(400)

if isnull(@code,'')='' and isnull(@name,'')='' 
	print '2个变量不能都为空'
else
	begin
		if @code='' and @name!=''
			set @sql='select * from tGoods where name='''+@name+''''
		else 
			if @code!='' and @name=''
				set @sql='select * from tGoods where code='''+@code+''''
			else
				set @sql='select * from tGoods where code='+@code +' and name='''+@name+''''
	end
 
 
exec(@sql)

go 



exec sp_chaxun '','桃子'

keyid       code                    name price
----------- ----------------------- ---- -----------
5           2002-01-02 00:00:00.000 桃子   2

[csproj]

谢谢大家
select * from tGoods
select * from tGoods Where code = code
效率有差别吗？优化器会优化第二个语句吗？

[kasin000]

select 
*
from tGoods 
where Code=@Code or ISNULL(@Code,'')=''
      ANd name=@Name or ISNULL(@Name,'')=''

[lrjt1980]

为什么要写代变量的.

[ChinaJiaBing]

---try

if object_id('tGoods')is not null
    drop table tGoods
if object_id('sp_chaxun') is not null
   drop procedure sp_chaxun
go
create table tGoods (keyid int,code nvarchar(10),name nvarchar(10),price int)
insert into tGoods select 1,'1.1.001','苹果',3
      union all    select 2,'1.1.002','香蕉',2
      union all    select 3,'1.2.001','橘子',4
      union all    select 4,'2.1.001','葡萄',8
      union all    select 5,'2.1.002','桃子',2
go
create procedure sp_chaxun
(
@code nvarchar(10),
@name nvarchar(10)
)
as
set nocount off
declare @str nvarchar(1000)
set @str='select * from tGoods where 1=1  '
if @code<>''
set @str=@str+' and code ='''+ @code+''''
if @name<>''
set @str=@str+' and name='''+@name+''''
exec (@str)
--print @str
set nocount on
go
sp_chaxun  '','苹果' 
go
sp_chaxun  '2.1.001',''    
go
sp_chaxun  '2.1.001','葡萄' 

/*


(5 行受影响)
keyid       code       name       price
----------- ---------- ---------- -----------
1           1.1.001    苹果         3

(1 行受影响)

keyid       code       name       price
----------- ---------- ---------- -----------
4           2.1.001    葡萄         8

(1 行受影响)

keyid       code       name       price
----------- ---------- ---------- -----------
4           2.1.001    葡萄         8

(1 行受影响)

select * from tGoods where 1=1   and code ='2.1.001' and name='葡萄'


*/

[kasin000]

if OBJECT_ID('tb') is not null drop table tb
GO
if object_id('sp_QueryByParam') is not null drop procedure sp_QueryByParam
go
create table tb (keyid int,code nvarchar(10),name nvarchar(10),price int)
insert into tb select 1,'1.1.001','苹果',3
      union all    select 2,'1.1.002','香蕉',2
      union all    select 3,'1.2.001','橘子',4
      union all    select 4,'2.1.001','葡萄',8
      union all    select 5,'2.1.002','桃子',2
go
create procedure sp_QueryByParam

@code nvarchar(10),
@name nvarchar(10)

as
select * from tb where (Code=@Code or ISNULL(@Code,'')='')ANd (name=@Name or ISNULL(@Name,'')='')
GO

sp_QueryByParam '',''
GO
sp_QueryByParam '','苹果'
GO
sp_QueryByParam '1.1.002',''
GO
sp_QueryByParam '1.1.001','苹果'



(5 行受影响)
keyid       code       name       price
----------- ---------- ---------- -----------
1           1.1.001    苹果         3
2           1.1.002    香蕉         2
3           1.2.001    橘子         4
4           2.1.001    葡萄         8
5           2.1.002    桃子         2

(5 行受影响)

keyid       code       name       price
----------- ---------- ---------- -----------
1           1.1.001    苹果         3

(1 行受影响)

keyid       code       name       price
----------- ---------- ---------- -----------
2           1.1.002    香蕉         2

(1 行受影响)

keyid       code       name       price
----------- ---------- ---------- -----------
1           1.1.001    苹果         3

(1 行受影响)