set @query = 'select * from table_name where 1 = 1 '
set @condition = ''
if (@field_1 is not null) begin set @condition = @condition + ' and field_1 = ''' + @field_1 + '''' end if (@field_1 is not null) begin set @condition = @condition + ' and field_1 = ''' + @field_1 + '''' end if (@field_1 is not null) begin set @condition = @condition + ' and field_1 = ''' + @field_1 + '''' end . . . set @query = @query + @condition exec (@query) Go
那条语句在。NET数据服务C#高级编程一书有提级,因为比较复杂,我忘了。
msdn有也没有使用procedure的例子
明天给大家分.
create procedure dbo.po_enable_purchase_order
(
@field_1 varchar(50)= null,
@field_2 varchar(50)= null,
@field_3 varchar(50)= null,
.
.
.
)AS
declare @query varchar(4000),
@order varchar(1000),
@condition varchar(1000)
declare @CurrentError int
set @query = 'select * from table_name where 1 = 1 '
set @condition = ''
if (@field_1 is not null)
begin
set @condition = @condition + ' and field_1 = ''' + @field_1 + ''''
end if (@field_1 is not null)
begin
set @condition = @condition + ' and field_1 = ''' + @field_1 + ''''
end if (@field_1 is not null)
begin
set @condition = @condition + ' and field_1 = ''' + @field_1 + ''''
end
.
.
.
set @query = @query + @condition
exec (@query)
Go