怎么定义参数和局部变量

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

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

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 [email protected]+'%')
if(@a2 not is null)@Sql+ (and a2 like [email protected]+'%')

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 [email protected]+'%''';
end

if not isnull(@a2)
begin
 set @sql = @sql + 'and a2 like [email protected]+'%''';
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 [email protected]+'%''';
end

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

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


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

SQL code

CREATE PROCEDURE a_b@a1 varchar(50),--传入参数@a2 varchar(50)--传入参数ASBEGINdeclare @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 [email protected]+'%''' else '' end)select @where = @where + (case when @a2 is not null then ' and a1 like [email protected]+'%''' else '' end)set @sql = @sql + @whereexec(@sql)endgo
------解决方案--------------------
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 [email protected]+'%''';
end

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

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

单引号要这样才对