CREATE PROCEDURE [dbo].[Find_]
@daeStart smalldatetime , @daeEnd smalldatetime , @strBmph varchar(20) , @strField varchar(5) , @strText varchar(20)
asdeclare @i int , @strSql varchar(100)
set @i = 1if @strBmph <> '' set @strSql = ' and bmph = ' + @strBmph
if @strField <> '' and @strText <> '' set @strSql = @strSql + ' and ' + @strField + ' = ' + @strTextbegin/*以下语法错误,请大家赐教。*/
select * from aaa where bDel = 0 + @strSql end
go
exec [Find_] '2011-6-10','2011-6-30' , '人事部' , '' ,''
go
drop procedure find_
@daeStart smalldatetime , @daeEnd smalldatetime , @strBmph varchar(20) , @strField varchar(5) , @strText varchar(20)
asdeclare @i int , @strSql varchar(100)
set @i = 1if @strBmph <> '' set @strSql = ' and bmph = ' + @strBmph
if @strField <> '' and @strText <> '' set @strSql = @strSql + ' and ' + @strField + ' = ' + @strTextbegin/*以下语法错误,请大家赐教。*/
select * from aaa where bDel = 0 + @strSql end
go
exec [Find_] '2011-6-10','2011-6-30' , '人事部' , '' ,''
go
drop procedure find_
@daeStart smalldatetime , @daeEnd smalldatetime , @strBmph varchar(20) , @strField varchar(5) , @strText varchar(20)
asdeclare @i int , @strSql varchar(100)
set @i = 1if @strBmph <> '' set @strSql = ' and bmph = ' + @strBmph
if @strField <> '' and @strText <> '' set @strSql = @strSql + ' and ' + @strField + ' = ' + @strTextbegin/*以下语法错误,请大家赐教。*/
exec('select * from aaa where bDel = 0 '+ @strSql )end
go
exec [Find_] '2011-6-10','2011-6-30' , '人事部' , '' ,''
go
drop procedure find_
@daeStart smalldatetime ,
@daeEnd smalldatetime ,
@strBmph varchar(20) ,
@strField varchar(5) ,
@strText varchar(20)
asdeclare @i int , @strSql varchar(100)
set @i = 1
set @strSql='select * from aaa where bDel = 0 '
if @strBmph <> '' set @strSql = ' and bmph = ' + @strBmph
if @strField <> '' and @strText <> '' set @strSql = @strSql + ' and ' + @strField + ' = ' + @strText
print (@strSql)
exec (@strSql)
然后执行
例如
declare @sql varchar(1000)
set @sql='select * from tb'
exec(@sql)
@daeStart smalldatetime ,
@daeEnd smalldatetime ,
@strBmph varchar(20) ,
@strField varchar(5) ,
@strText varchar(20)
asdeclare @i int , @strSql nvarchar(100)
set @i = 1if @strBmph <> '' set @strSql = ' select * from aaa where bDel = 0 and bmph = @strBmph'
if @strField <> '' and @strText <> '' set @strSql = 'select * from aaa where bDel = 0 and bmph = @strBmph and @strField = @strText'exec sp_executesql @strSQL,N'@daeStart smalldatetime , @daeEnd smalldatetime , @strBmph varchar(20) , @strField varchar(5) , @strText varchar(20)',@daeStart , @daeEnd , @strBmph , @strField , @strText
试一下
CREATE PROCEDURE [dbo].[Find_]
@daeStart smalldatetime , @daeEnd smalldatetime , @strBmph varchar(20) , @strField varchar(5) , @strText varchar(20)
as
begindeclare @i int , @strSql varchar(max) --改成这个数据类型
set @i = 1if @strBmph <> '' set @strSql = ' and bmph = ' + @strBmph
if @strField <> '' and @strText <> '' set @strSql = @strSql + ' and ' + @strField + ' = ' + @strTextset @strsql=@strsql+....... --可以反复处理直到拼完全部SQL 语句
exec( @strSql)end
go
exec [Find_] '2011-6-10','2011-6-30' , '人事部' , '' ,''
go
drop procedure find_
这句。
你会更蒙个人建议,像这样写更好点:
select * from test where (字段1=条件 or 1=1) and (字段2<>条件 or 1=1) and (字段3 like 条件 or 1=1)