用字符串连接SQL语句并用EXEC执行时,出现名称 '这里是字符串连接的一条SQL语句‘不是有效的标识符
才发现,在写exec @sql 时,忘了在@sql加(),这样写 exec (@sql) 就不会出错了!
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <根据输入的职位类别、地区、关键字搜索>
-- =============================================
ALTER PROCEDURE [dbo].[GetHomeSearchPosts]
@postType varchar(100),
@address varchar(10),
@key varchar(100)
--- @postType @address 默认全部
AS
BEGIN
SET NOCOUNT ON;
declare @sql varchar(5000),@where varchar(100),@where1 varchar(200)
set @sql ='SELECT A.ID, A.InputTime,A.PositionName,B.CompanyName,A.Adress,A.job_place1 from Position AS A inner join Company AS B ON A.CompID = B.CompanyID where ( A.PositionName like ''% '+ @key +' %'' or PrimaryPosition like ''% '+ @key
+' %'') '
if(@postType='')
begin
set @where =''
end
else
begin
set @where = ' A.PrimaryPosition like ''%' + @postType + '%'''
end
if(@address = '' )
begin
set @where1 =''
end
else
begin
set @where1 = '( A.Adress like ''%' + @address + '%'' or A.job_place1 like ''%' + @address +'%'')'
end
if(@where != '')
set @sql = @sql +' and ' + @where
if(@where1 != '')
set @sql = @sql +' and ' + @where1
print @sql
exec (@sql)
END