怎么定义参数和局部变量

如何定义参数和局部变量
太久没写存储过程了,很多都不熟悉

拼接语法基本忘光,用存储过程,请高手赐教

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

具体思路是这样,不过错误多多,恳请高手帮忙

------解决方案--------------------
CREATE PROCEDURE a_b

@a1 varchar(50),--传入参数
@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+'%''';
end

if not isnull(@a2)
begin
 set @sql = @sql + 'and a2 like ''%'''+@a2+'%''';
end

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);
set @sql=(1=1); --拼接where 后面的条件,判断非空,就拼接

if @a1 is not null
begin
 set @sql = @sql + 'and a1 like ''%'''+@a1+'%''';
end

if @a2 is not null
begin
 set @sql = @sql + 'and a2 like ''%'''+@a2+'%''';
end

set @sql = 'select * from table where ' + @sql;
exec(@sql);
END


------解决方案--------------------

SQL code

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

------解决方案--------------------
CREATE PROCEDURE a_b

@a1 varchar(50),--传入参数
@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+'%''';
end

if @a2 is not null
begin
  set @sql = @sql + 'and a2 like ''%'+@a2+'%''';
end

set @sql = 'select * from table where ' + @sql;
exec(@sql);
END

单引号要这样才对