存储过程里面写查找有关问题,解决了立即给分

存储过程里面写查找问题,急急急,解决了立即给分
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Consignimport] 
(
  @a nvarchar(20),
  @b DATETIME,
  @c DATETIME,
  @d nvarchar(20)
)
AS

select * from test where ()

条件能够单独每一项查找,好能够组合一起查找.比如说,我输入a_txtbox,b_txtbox 那么就只按这两项来查,如果我只佃a_txtbox,那么只按这一项来查,
在where 里面怎么写啊,很郁闷啊,请高手批点


[解决办法]
你直接在程序里面把where 写好不就成了吗？

SQL code

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[Consignimport] (      @where nvarchar(4000) ) AS select * from test where @where
[解决办法]
SQL code
CREATE PROCEDURE [dbo].[Consignimport] (       @a nvarchar(20),       @b DATETIME,       @c DATETIME,       @d nvarchar(20),       @a_txtbox varchar(50),      @b_txtbox varchar(50)) AS  declare @sql varchar(5000)set @sql='select * from test where 1=1'if @a_txtbox<>''begin set @sql+=' and name1 =@a_txtbox 'endif @a_txtbox<>''begin set @sql+=' and name2 =@b_txtbox 'endexec(@sql)go
[解决办法]
SQL code
CREATE PROCEDURE [dbo].[Consignimport] (       @a nvarchar(20),       @b DATETIME,       @c DATETIME,       @d nvarchar(20),       @a_txtbox varchar(50),      @b_txtbox varchar(50)) AS  declare @sql varchar(5000)set @sql='select * from test where 1=1'if @a_txtbox<>''begin set @sql+=' and name1 =''' +@a_txtbox+'''  'endif @a_txtbox<>''begin set @sql+=' and name2 =''' +@b_txtbox+''' 'endexec(@sql)go
[解决办法]
SQL code
CREATE PROCEDURE [dbo].[Consignimport] (             @a_txtbox varchar(50),      @b_txtbox varchar(50)) AS  declare @sql varchar(5000)set @sql='select * from test where 1=1'if @a_txtbox<>''begin set @sql+=' and name1 =''' +@a_txtbox+'''  'endif @a_txtbox<>''begin set @sql+=' and name2 =''' +@b_txtbox+''' 'endexec(@sql)go
[解决办法]
select * from table  
where
(@a='' or col like '%'+@a+'%')
and
(@b='' or col2 like '%'+@b+'%')
and
...
[解决办法]
探讨

SQL code
CREATE PROCEDURE [dbo].[Consignimport] 
( 
      @a nvarchar(20), 
      @b DATETIME, 
      @c DATETIME, 
      @d nvarchar(20), 
      @a_txtbox varchar(50),
      @b_txtbox varchar(50)
) 
AS 
 declare @sql varchar(5000)
set @sql='select * from test where 1=1'

if @a_txtbox<>''
begin
 set @sql+=' and name1 =''' +@a_txtbox+'''  '
end
if @a_txtbox<>''
begin
 set @sql…

[解决办法]
探讨

SQL code
CREATE PROCEDURE [dbo].[Consignimport] 
( 
      
      @a_txtbox varchar(50),
      @b_txtbox varchar(50)
) 
AS 
 declare @sql varchar(5000)
set @sql='select * from test where 1=1'

if @a_txtbox<>''
begin
 set @sql+=' and name1 =''' +@a_txtbox+'''  '
end
if @a_txtbox<>''
begin
 set @sql+=' and name2 =''' +@b_txtbox+''' '
end
exec(@sql)
go

[解决办法]
SQL code
create proc getData@where varchar(1000)asdeclare @sql varchar(2000)set @sql='select * from test'if @where<>'' set @sql=@sql+' where '+@whereexec (@sql) 
 
[解决办法]
CREATE PROCEDURE [dbo].[Consignimport]  
(  
    
     @a_txtbox varchar(50),
     @b_txtbox varchar(50)
)  
AS  
  declare @sql varchar(5000)
set @sql='select * from test where 1=1'

if @a_txtbox<>''
begin
  set @sql+=' and name1 =''' +@a_txtbox+'''  '
end
if @a_txtbox<>''
begin
  set @sql+=' and name2 =''' +@b_txtbox+''' '
end
exec(@sql)
go
[解决办法]
直接用SQL解决：
SQL code
CREATE PROCEDURE [dbo].[Consignimport] (   @a nvarchar(20)=null,   @b DATETIME=null,   @c DATETIME=null,   @d nvarchar(20)=null ) AS     BEGIN        SET NOCOUNT ON;        SELECT * FROM TEST         WHERE COL1=ISNULL(@a,COL1)           AND COL2=ISNULL(@b,COL2)           AND COL3=ISNULL(@c,COL3)           AND COL4=ISNULL(@d,COL4)    END