如何定义参数和局部变量
太久没写存储过程了,很多都不熟悉
拼接语法基本忘光,用存储过程,请高手赐教
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
[解决办法]
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 ''%'''+@a1+'%''' else '' end)select @where = @where + (case when @a2 is not null then ' and a1 like ''%'''+@a2+'%''' 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 ''%'+@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
单引号要这样才对
