22,207
社区成员
发帖
与我相关
我的任务
分享
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <简少辉>
-- Create date: <2010-318>
-- Description: <企业查找用户>
-- =============================================
alter PROCEDURE getPersonSearch
(
@pagesize int,
@pageindex int, --从0开始
@positiontitle int,
@specialty nvarchar(20),
@edustate nvarchar(20),
@language nvarchar(20),
@languageleve nvarchar(20),
@work_year int,
@talent_type nvarchar(20),
@sex nvarchar(10),
@minage int,
@maxage int,
@workplacestr nvarchar(1000),
@locnow nvarchar(20),
@locreg nvarchar(20),
@positionstr nvarchar(1000),
@applytype nvarchar(50),
@up int, --1表示学历包括以上,0不包括
@state int,--简历状态
@count int output
)
AS
BEGIN
SET NOCOUNT ON;
declare @sql nvarchar(4000)
declare @startIndex int
set @startIndex = @pageindex * @pagesize
set @sql='select top '+CONVERT(nvarchar(5), @pagesize,0)+' * from ( select PersonalInfo.Station1,PersonalInfo.Edu,PersonalInfo.UserID,PersonalInfo.Work_Year,PersonalInfo.Update_Time,PublicInfo.Birthday,PublicInfo.Sex as truesex ,ROW_NUMBER() over(order by personalinfo.update_time desc) as row from PersonalInfo inner join PublicInfo on PersonalInfo.UserID = PublicInfo.UserID where 1=1 '
if @positiontitle<>0
begin
if (@positiontitle % 1000)= 0
begin
set @sql = @sql + ' and (PositionTitle1 >= '+CONVERT(nvarchar(10), @positiontitle,0)+' and PositionTitle1<'+CONVERT(nvarchar(10), @positiontitle+1000,0)+') or (PositionTitle2 >= '+CONVERT(nvarchar(10), @positiontitle,0)+' and PositionTitle2<'+CONVERT(nvarchar(10), @positiontitle+1000,0)+')'
end
else
begin
set @sql = @sql + ' and ((PositionTitle1-1)='+CONVERT(nvarchar(10), @positiontitle,0)+' or (PositionTitle2-1)='+CONVERT(nvarchar(10), @positiontitle,0)+')'
end
end
if @specialty<>''
begin
set @sql = @sql + ' and (Specialty1 = '''+@specialty+''' or Specialty2='''+@specialty+''')'
end
if @up>0 --包括以上
begin
set @sql = @sql + ' and (edustate & ' +CONVERT(nvarchar(10),@edustate,0)+')='+CONVERT(nvarchar(10),@edustate,0)+''
end
else
begin
set @sql = @sql +' and (edustate-1)='+CONVERT(nvarchar(10),@edustate,0)+''
end
if @language<>''
begin
if @languageleve<>''
begin
set @sql = @sql +' and ((Language1='''+@language+''' and Level1 = '''+@languageleve+''')'
set @sql = @sql+' or (Language2='''+@language+''' and Level2 = '''+@languageleve+'''))'
end
else
begin
set @sql = @sql +' and ((Language1='''+@language+''')'
set @sql=@sql+' or (Language2='''+@language+'''))'
end
end
if @work_year<>0
begin
set @sql = @sql + ' and Work_Year>='+CONVERT(nvarchar(5), @work_year,0)+''
end
if @talent_type<>''
begin
set @sql= @sql+' and Talent_Type='''+@talent_type+''''
end
if @sex<>'不限'
begin
set @sql = @sql +' and PublicInfo.Sex='''+@sex+''''
end
set @sql = @sql +' and ( YEAR(GETDATE())- PublicInfo.Birthday between '+CONVERT(nvarchar(3),@minage,0)+' and '+CONVERT(nvarchar(3),@maxage,0)+')'
if @locnow <> '不限'
begin
set @sql = @sql + ' and Loc_Now = '''+@locnow+''''
end
if @locreg <> '不限'
begin
set @sql = @sql + ' and Loc_Reg = '''+@locreg+''''
end
if @applytype<>''
begin
set @sql = @sql +' and Apply_Type='''+@applytype+''''
end
if @positionstr<>''
begin
set @sql = @sql + @positionstr
end
if @workplacestr<>''
begin
set @sql = @sql+@workplacestr
end
set @sql = @sql+') as dc where row >= '+CONVERT(nvarchar(5), @startIndex,0)+''
exec sp_executesql @sql
END
GO
@@ROWCOUNT
返回受上一语句影响的行数。
语法
@@ROWCOUNT
返回类型
integer
注释
任何不返回行的语句将这一变量设置为 0 ,如 IF 语句。
示例
下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。
UPDATE authors SET au_lname = 'Jones'
WHERE au_id = '999-888-7777'
IF @@ROWCOUNT = 0
print 'Warning: No rows were updated'