太久没写存储过程了,很多都不熟悉拼接语法基本忘光,用存储过程,请高手赐教CREATE PROCEDURE a_b@a1 varchar(50),--传入参数
@a2 varchar(50)--传入参数
set @sql=(1=1) --拼接where 后面的条件,判断非空,就拼接
AS
BEGIN
if(@a1 not is null)@Sql+ (and a1 like '%'+@a1+'%')
if(@a2 not is null)@Sql+ (and a2 like '%'+@a2+'%')select * from table where @sql
END具体思路是这样,不过错误多多,恳请高手帮忙
@a2 varchar(50)--传入参数
set @sql=(1=1) --拼接where 后面的条件,判断非空,就拼接
AS
BEGIN
if(@a1 not is null)@Sql+ (and a1 like '%'+@a1+'%')
if(@a2 not is null)@Sql+ (and a2 like '%'+@a2+'%')select * from table where @sql
END具体思路是这样,不过错误多多,恳请高手帮忙
@a2 varchar(50)--传入参数
AS
BEGIN
declare @sql varchar(max);
set @sql=(1=1); --拼接where 后面的条件,判断非空,就拼接if not isnull(@a1)
begin
set @sql = @sql + 'and a1 like ''%'''+@a1+'%''';
endif not isnull(@a2)
begin
set @sql = @sql + 'and a2 like ''%'''+@a2+'%''';
endset @sql = 'select * from table where ' + @sql;
exec(@sql);
END
@a2 varchar(50)--传入参数
AS
BEGIN
declare @sql varchar(max);
set @sql=(1=1); --拼接where 后面的条件,判断非空,就拼接if @a1 is not null
begin
set @sql = @sql + 'and a1 like ''%'''+@a1+'%''';
endif @a2 is not null
begin
set @sql = @sql + 'and a2 like ''%'''+@a2+'%''';
endset @sql = 'select * from table where ' + @sql;
exec(@sql);
END
AS
BEGIN
declare @sql as varchar(1000)
set @sql = '1=1'
if (@a1 not is null)
set @sql = @Sql + ' and a1 like '%''' + @a1 + '''%'
if (@a2 not is null)
set @sql = @Sql + ' and a2 like '%''' + @a2 + '''%' set @sql = 'select * from table where ' + @sql
exec(@sql)
END
CREATE PROCEDURE a_b
@a1 varchar(50),--传入参数
@a2 varchar(50)--传入参数
AS
BEGIN
declare @sql varchar(max)
declare @where varchar(1000)
set @sql = 'select * from tb where 1 = 1'
set @where = ''
select @where = @where + (case when @a1 is not null then ' and a1 like ''%'''+@a1+'%''' else '' end)
select @where = @where + (case when @a2 is not null then ' and a1 like ''%'''+@a2+'%''' else '' end)
set @sql = @sql + @where
exec(@sql)
end
go
@a2 varchar(50)--传入参数
AS
BEGIN
declare @sql varchar(max);
set @sql=’1=1’; --拼接where 后面的条件,判断非空,就拼接if @a1 is not null
begin
set @sql = @sql + 'and a1 like ''%'+@a1+'%''';
endif @a2 is not null
begin
set @sql = @sql + 'and a2 like ''%'+@a2+'%''';
endset @sql = 'select * from table where ' + @sql;
exec(@sql);
END单引号要这样才对
@a2 varchar(50)--传入参数
AS
BEGIN
declare @sql varchar(max);
set @sql='1=1'; --拼接where 后面的条件,判断非空,就拼接if @a1 is not null
begin
set @sql = @sql + 'and a1 like ''%'+@a1+'%''';
endif @a2 is not null
begin
set @sql = @sql + 'and a2 like ''%'+@a2+'%''';
endset @sql = 'select * from table where ' + @sql;
exec(@sql);
END单引号要这样才对