动态SQL的存储过程,带like字段时报错
高手帮看一下,这个存储过程,我应该怎么写?
CREATE PROCEDURE GetListbyArea
@Province_Name nvarchar(20),
@City_Name nvarchar(20),
@StrWhere nvarchar(256)
AS
DECLARE @SQL nvarchar(1000)
DECLARE @Strw nvarchar(256)
set @SQL= 'select * from Provide '
set @SQL=@SQL+ 'where (Province_Name like '% '+@Province_Name+ '% ' or City_Name like '% '+@City_Name+ '% ') ' -----这句话有错
set @Strw=@StrWhere
if @Strw <> ' '
begin
set @SQL=@SQL+@Strw
end
set @SQL=@SQL+ 'order by Provide_Reldate desc '
exec(@SQL)
GO
[解决办法]
CREATE PROCEDURE GetListbyArea
@Province_Name nvarchar(20),
@City_Name nvarchar(20),
@StrWhere nvarchar(256)
AS
DECLARE @SQL nvarchar(1000)
DECLARE @Strw nvarchar(256)
set @SQL= 'select * from Provide '
set @SQL=@SQL+ 'where (Province_Name like ' '% '+@Province_Name+ '% ' ' or City_Name like ' '% '+@City_Name+ '% ' ') ' -----这句话有错
set @Strw=@StrWhere
if @Strw <> ' '
begin
set @SQL=@SQL+@Strw
end
set @SQL=@SQL+ 'order by Provide_Reldate desc '
exec(@SQL)
GO
肯定可以
[解决办法]
你是构造sql少了 '号 sql的 '类似C#中的\
set @SQL=@SQL+ 'where (Province_Name like ' '% '+@Province_Name+ '% ' ' or City_Name like ' '% '+@City_Name+ '% ' ') '