June 18, 2009 at 2:01 am
Sir,
I am a begginner to SQL Server, when i execute my stored procedure i got the error like this,
Location: tmpilb.cpp:2530
Expression: fFalse
SPID: 159
Process ID: 1664
Description: Attempt to access expired blob handle (3)
Msg 3624, Level 20, State 1, Procedure SP400_SEARCHAPPLICANT, Line 787
A system assertion check has failed. Check the SQL Server error log for details
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I am using temporary table in this stored procedure when i searched in google i am not getting the way to handle it, i thought it is better to ask you.
Please help me to solve this because this is a big procedure for having many search criterias.
Thanking You
ShameerKhan.S
June 18, 2009 at 2:09 am
Can you post the code that you are trying to run 🙂
June 18, 2009 at 2:43 am
Sir,
This is the stored procedure i want to run but sometimes when i execute the blob handle error is coming.
If any mistake is there please let me know
Thanks
Shameer
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/*
exec HRMS_SP.SP400_SEARCHAPPLICANT
'','',1,0,0,1,0,0,'','','',0,'',0,'','',0,0,0,'','',2,0,
'',
'','','','','','','','','','',0,0,0,0,0,0,0,0,0,
'','','','','','','','',0,'',0,0,''
*/
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [HRMS_SP].[SP400_SEARCHAPPLICANT]
(
@cvsearch VARCHAR(1000)='' -- resume text box like 'asp,java,c,c++'
,@keyword VARCHAR(1000)='' -- keyword text box like '11,12,23'
,@cvitae INT=0 -- curriculam vitae group box + has no formatted cv values between 1 to 5
,@noformat_cv INT=0-- ??
,@keyoption INT=0 -- key word group box values b/w 1 to 3
,@permanent INT=0--- new???
,@contract INT=0--- new???
,@temporary INT=0--- new???
,@availabile_from VARCHAR(100)=''
,@availabile_to VARCHAR(100)=''
,@noticeperiod VARCHAR(100)='' -- general availability using notice period LIKE '3,week'
,@current_available INT=0--- new???
,@excldertndapplnt CHAR(1)=''-- exclude retained applicants only 'n' if checked else 'null'
,@datediff INT=''--- ???to get the applicants updated from last given days from getdate
,@address VARCHAR(200)='' -- search in address tab 3
,@postcode VARCHAR(200)='' -- search in postcode
,@only_permanent INT=0
,@available_now INT=0
,@include_CV INT=0-- Search applicant in which logged user is the FAO ??
,@applicanttypeincl VARCHAR(200)='' -- allpicant type included LIKE '13,23'
,@applicanttypeexcl VARCHAR(200)='' -- allpicant type excluded '13,23'
,@status INT=0-- archived like 1 to 3
,@cleared_status INT=0-- ??
,@exclude_result VARCHAR(MAX)=''-- ??
,@created_from VARCHAR(100)='' ---created date from
,@created_to VARCHAR(100)='' ---created date to
,@lastcnt_from VARCHAR(100)='' -- last contacted from
,@lastcnt_to VARCHAR(100)='' -- last contacted to
,@lastcvupdtd_from VARCHAR(100)=''
,@lastcvupdtd_to VARCHAR(100)=''
,@modifydate_from VARCHAR(100)=''
,@modifydate_to VARCHAR(100)=''
,@archived_from VARCHAR(100)=''---??
,@archived_to VARCHAR(100)=''---??
,@resp_options INT=0--- 1=Actioned, 2=Not Actioned
,@fao INT=0 -----------
,@faoteam INT=0 -- fao team textbox will be the user team id
,@createdby INT=0 -- created by textbox will be the user id
,@owner INT=0 -- applicant owner textbox will be the user id
,@group INT=0 -- group textbox will be group id
,@nation INT=0 -- nationality text box will be national id
,@media INT=0 -- from media selected media id
,@statusgeneral INT=0 -- status id
,@name VARCHAR(200)='' -- applicant name
,@email VARCHAR(100)='' -- mail
,@ltdconame VARCHAR(100)='' -----------
,@hometele VARCHAR(60)='' -- home telephone
,@worktele VARCHAR(60)=''-- work telephone
,@mobtele VARCHAR(60)=''-- mob
,@QuickNotes VARCHAR(MAX)='' -----------
,@aID VARCHAR(100)='' -- applicant id b/w,
,@linkd_cont INT=0-- If linked then '1' if not linked then '2' -----------
,@ZipCode VARCHAR(10)=''
,@Distance INT=0
,@relocate INT=0
,@resultcount INT=''-- to retrieve the no. of records
,@RESULT VARCHAR(MAX)='' OUTPUT
)
AS
BEGIN --TRY
SET NOCOUNT ON;
DECLARE @condition VARCHAR(MAX)
--DECLARE @wherecvsearch VARCHAR(MAX)
DECLARE @wherekeyword VARCHAR(MAX)
DECLARE @wherepostcodes VARCHAR(MAX)
DECLARE @whereaddr VARCHAR(MAX)
DECLARE @WhereClause VARCHAR(MAX)
DECLARE @Whereexclude VARCHAR(MAX)
DECLARE @whereapptypeid VARCHAR(MAX)
DECLARE @wherecondition VARCHAR(MAX)
DECLARE @wheregeneral VARCHAR(MAX)
DECLARE @wherezipcode VARCHAR(MAX)
DECLARE @totalwhere VARCHAR(MAX)
DECLARE @count INT
DECLARE @GET NVARCHAR(MAX)
--SET @wherecvsearch=''
SET @WhereClause=''
SET @Whereexclude=''
SET @wherekeyword=''
SET @wheregeneral=''
SET @wherecondition=''
SET @wherezipcode=''
SET @totalwhere=''
/* SEARCH ACCORDING TO CV/KEYWORDS */
IF(@cvitae=2)
BEGIN
SET @WhereClause=@WhereClause+' AND (APP.APPLICANT_ORIGINAL '''' OR APP.APPLICANT_ORIGINAL IS NOT NULL)'
END
ELSE IF(@cvitae=3)
BEGIN
SET @WhereClause=@WhereClause+' AND (APP.APPLICANT_ORIGINAL = '''' OR APP.APPLICANT_ORIGINAL IS NULL) '
END
ELSE IF(@cvitae=4)
BEGIN
SET @WhereClause=@WhereClause +' AND (APP.APPLICANT_FORMATED '''' OR APP.APPLICANT_FORMATED IS NOT NULL)'
END
IF(@noformat_cv0)
BEGIN
SET @WhereClause=@WhereClause+' AND (APP.APPLICANT_FORMATED = '''' OR APP.APPLICANT_FORMATED IS NULL)'
END
IF(@keyword'' AND (@keyoption=1 OR @keyoption=2))
BEGIN
EXEC [HRMS_SP].[SP400_Applicant_KeywordSearch] @keyword,@GET OUTPUT
SET @wherekeyword=@wherekeyword+' AND APP.APPLICANT_ID IN ('+@GET
END
IF (@keyoption=2)
BEGIN
SET @wherekeyword=@wherekeyword+' AND APP.APPLICANT_ID IN (SELECT APPLICANT_ID FROM HRMS_TBL.Applicant_Keywords_Selected) '
END
ELSE IF (@keyoption=3)
BEGIN
SET @wherekeyword=@wherekeyword+' AND APP.APPLICANT_ID NOT IN (SELECT APPLICANT_ID FROM HRMS_TBL.Applicant_Keywords_Selected) '
END
--ELSE IF(LEN(@wherecvsearch)>5)
--BEGIN
--SET @wherecvsearch=@wherecvsearch+')'
--END @permanent
IF (@Permanent0 AND @Contract0 AND @Temporary0)
BEGIN
SET @WhereClause=@WhereClause+' AND APP.APPLICANT_JOBCAT = ''9'''
END
IF @Permanent0 AND @Contract=0 AND @Temporary=0
BEGIN
SET @WhereClause=@WhereClause+' AND APP.APPLICANT_JOBCAT = ''2'''
END
IF @Permanent=0 AND @Contract0 AND @Temporary=0
BEGIN
SET @WhereClause=@WhereClause+' AND APP.APPLICANT_JOBCAT = ''4'''
END
IF @Permanent=0 AND @Contract=0 AND @Temporary0
BEGIN
SET @WhereClause=@WhereClause+' AND APP.APPLICANT_JOBCAT = ''3'''
END
IF @Permanent0 AND @Contract0 AND @Temporary=0
BEGIN
SET @WhereClause=@WhereClause+' AND APP.APPLICANT_JOBCAT = ''6'''
END
IF @Permanent0 AND @Contract=0 AND @Temporary0
BEGIN
SET @WhereClause=@WhereClause+' AND APP.APPLICANT_JOBCAT = ''5'''
END
IF @Permanent=0 AND @Contract0 AND @Temporary0
BEGIN
SET @WhereClause=@WhereClause+' AND APP.APPLICANT_JOBCAT = ''7'''
END
IF @Permanent=0 AND @Contract=0 AND @Temporary=0
BEGIN
SET @WhereClause=@WhereClause+' AND (APP.APPLICANT_JOBCAT = ''2'' OR APP.APPLICANT_JOBCAT = ''3'' OR APP.APPLICANT_JOBCAT = ''4'' OR APP.APPLICANT_JOBCAT = ''5'' OR APP.APPLICANT_JOBCAT = ''6'' OR APP.APPLICANT_JOBCAT = ''7'' OR APP.APPLICANT_JOBCAT = ''9'')'
END
/* SEARCH ACCORDING TO AVAILABILITY TAB */
IF (@availabile_from '')
BEGIN
SET @WhereClause=@WhereClause+ ' AND ((APP.APPLICANT_AVAILABLE >= '''+@availabile_from+''') AND (APP.APPLICANT_EMPLOYMENT_TYPE = ''2'' OR APP.APPLICANT_EMPLOYMENT_TYPE = ''3''))'
END
IF (@availabile_to '')
BEGIN
SET @WhereClause=@WhereClause+ ' AND ((APP.APPLICANT_AVAILABLE <= '''+@availabile_to+''') AND (APP.APPLICANT_EMPLOYMENT_TYPE = ''2'' OR APP.APPLICANT_EMPLOYMENT_TYPE = ''3''))'
END
IF(@noticeperiod'')
BEGIN
SET @count=CHARINDEX(',',@noticeperiod)
SET @WhereClause=@WhereClause+' AND (APP.APPLICANT_NOTICE='+''''+ SUBSTRING(@noticeperiod,1, @count-1)+''''
+' AND APP.APPLICANT_NOTICE_TYPE='+''''+ SUBSTRING(@noticeperiod,@count+1, LEN(@noticeperiod))+''')
AND (APP.APPLICANT_EMPLOYMENT_TYPE = ''1'' OR APP.APPLICANT_EMPLOYMENT_TYPE = ''3'' OR APP.APPLICANT_EMPLOYMENT_TYPE = ''4'')'
END
IF(@current_available0)
BEGIN
SET @WhereClause=@WhereClause+' AND (APP.APPLICANT_AVAILABLE <= '''+CONVERT(VARCHAR,GETDATE())+''' AND (APP.APPLICANT_EMPLOYMENT_TYPE = ''2'' OR APP.APPLICANT_EMPLOYMENT_TYPE = ''3'')) '
END
IF(@excldertndapplnt '')
BEGIN
SET @WhereClause=@WhereClause + ' AND APP.APPLICANT_ID IN (SELECT APPLICANT_ID FROM HRMS_TBL.Applicant_Media_Selected
WHERE APPLICANT_MEDIA_ID IN (SELECT APPLICANT_MEDIA_ID FROM HRMS_TBL.Applicant_Media
WHERE APPLICANT_MEDIA_RETAIN = ''N''))'
END
IF (@datediff'')
BEGIN
DECLARE @DATE VARCHAR(1000)
SET @DATE=DATEADD(day,@datediff ,getdate())
SET @WhereClause=@WhereClause +' AND (APP.APPLICANT_AVAILABLE_UPDATEDDATE >= '''+@DATE+''' AND (APP.APPLICANT_EMPLOYMENT_TYPE = ''2'' OR APP.APPLICANT_EMPLOYMENT_TYPE = ''3'')) '
END
/* SEARCH ACCORDING TO THE FIELDS IN GENERAL REQUIREMENTS TAB */
/* Search in Address textbox */
IF(@address'')
BEGIN
SET @count=CHARINDEX(',',@address)
SET @whereaddr=' AND ('
WHILE(@count>0)
BEGIN
SET @whereaddr=@whereaddr+ 'APP.APPLICANT_ADDRESS LIKE '+ '''' +'%'+
SUBSTRING(@address,1,@count-1)+'%'+'''' +' OR '
SET @address=SUBSTRING(@address,@count+1,LEN(@address))
SET @count=CHARINDEX(',',@address)
END
IF(LEN(@address)>0)
BEGIN
SET @whereaddr=@whereaddr+ ' APP.APPLICANT_ADDRESS LIKE '+ '''' +'%'+
@address+'%'+''''+')'
END
END
/* Search in Post Code textbox */
IF(@postcode'')
BEGIN
SET @count=CHARINDEX(',',@postcode)
SET @wherepostcodes=' AND ('
WHILE(@count>0)
BEGIN
SET @wherepostcodes=@wherepostcodes+ 'APP.APPLICANT_POSTALCODE LIKE '+ ''''+
SUBSTRING(@postcode,1,@count-1)+'%'+'''' +' OR '
SET @postcode=SUBSTRING(@postcode,@count+1,LEN(@postcode))
SET @count=CHARINDEX(',',@postcode)
END
IF(LEN(@postcode)>0)
BEGIN
SET @wherepostcodes=@wherepostcodes+ ' APP.APPLICANT_POSTALCODE LIKE '+ '''' +
@postcode+'%'+''''+')'
END
END
IF(@only_permanent0)
BEGIN
SET @WhereClause=@WhereClause+' AND (APP.APPLICANT_EMPLOYMENT_TYPE = ''1'' OR APP.APPLICANT_EMPLOYMENT_TYPE = ''4'')'
END
IF(@available_now0)
BEGIN
SET @WhereClause=@WhereClause+' AND (APP.APPLICANT_AVAILABLE <= '''+CONVERT(VARCHAR,GETDATE())+''' AND (APP.APPLICANT_EMPLOYMENT_TYPE = ''2'' OR APP.APPLICANT_EMPLOYMENT_TYPE = ''3'')) '
END
/* Just include CVs for my attention checkbox */
IF (@include_CV0)
BEGIN
SET @WhereClause=@WhereClause + ' AND APP.APPLICANT_ID IN(SELECT APPLICANT_ID FROM HRMS_TBL.Applicant_Owner
WHERE FAO = '''+CONVERT(VARCHAR,@include_CV)+''')'
END
/* Status Groupbox */
IF(@status0)
BEGIN
IF(@status=1)
BEGIN
SET @WhereClause=@WhereClause + ' AND APP.APPLICANT_ARCHIVED =''Y'''
END
ELSE IF(@status=2)
BEGIN
SET @WhereClause=@WhereClause + ' AND APP.APPLICANT_ARCHIVED =''N'''
END
END
/* Cleared Status Groupbox */
IF (@cleared_status0)
BEGIN
IF (@cleared_status=1)
BEGIN
SET @WhereClause=@WhereClause + ' AND (APP.APPLICANT_CLEARED ''Y'' OR APP.APPLICANT_CLEARED IS NULL)'
END
ELSE IF (@cleared_status=2)
BEGIN
SET @WhereClause=@WhereClause + ' AND APP.APPLICANT_CLEARED =''Y'''
END
END
/* Exclude other search result button */
IF (@exclude_result'')
BEGIN
DECLARE @inputlength BIGINT
DECLARE @i INT;
DECLARE @table TABLE (ID BIGINT UNIQUE);
SET @inputlength=LEN(@exclude_result)
SET @i=1;
INSERT INTO @table VALUES(1);
WHILE (@i * 2 < @inputlength)
BEGIN
INSERT INTO @table (ID)
SELECT ID+@i FROM @table;
SET @i=@i * 2
END
INSERT INTO @table SELECT ID+@i FROM @table WHERE ID+@i <= @inputlength
CREATE TABLE #TEMP_EXCLUDE (ID BIGINT NOT NULL IDENTITY(1,1), APPLICANT_ID BIGINT)
INSERT INTO #TEMP_EXCLUDE
SELECT SUBSTRING(@exclude_result,ID,CHARINDEX(',',@exclude_result + ',',ID)- ID)
FROM @table WHERE SUBSTRING(',' + @exclude_result,ID,1) = ',';
SET @Whereexclude=@Whereexclude+ ' AND APP.APPLICANT_ID NOT IN (SELECT APPLICANT_ID FROM #TEMP_EXCLUDE)'
END
/* Applicant Types Groupbox */
IF((@applicanttypeincl'' AND @applicanttypeexcl'') OR (@applicanttypeincl='' AND @applicanttypeexcl'') OR (@applicanttypeincl'' AND @applicanttypeexcl=''))
BEGIN
SET @whereapptypeid=''
SET @count=CHARINDEX(',',@applicanttypeincl)
IF(@applicanttypeincl'')
BEGIN
SET @whereapptypeid=' AND ( '
WHILE(@count>0)
BEGIN
SET @whereapptypeid=@whereapptypeid+ 'APP.APPLICANT_TYPE_ID ='+
SUBSTRING(@applicanttypeincl,1,@count-1)+' OR '
SET @applicanttypeincl=SUBSTRING(@applicanttypeincl,@count+1,LEN(@applicanttypeincl))
SET @count=CHARINDEX(',',@applicanttypeincl)
END
IF(LEN(@applicanttypeincl)>1)
BEGIN
SET @whereapptypeid=@whereapptypeid+ ' APP.APPLICANT_TYPE_ID = '+@applicanttypeincl+')'
END
END
SET @count=CHARINDEX(',',@applicanttypeexcl)
IF(@applicanttypeexcl'')
BEGIN
WHILE(@count>0)
BEGIN
SET @whereapptypeid=@whereapptypeid+ ' AND APP.APPLICANT_TYPE_ID '+
SUBSTRING(@applicanttypeexcl,1,@count-1)+' AND '
SET @applicanttypeexcl=SUBSTRING(@applicanttypeexcl,@count+1,LEN(@applicanttypeexcl))
SET @count=CHARINDEX(',',@applicanttypeexcl)
END
IF(LEN(@applicanttypeexcl)>1)
BEGIN
SET @whereapptypeid=@whereapptypeid+ ' APP.APPLICANT_TYPE_ID '+@applicanttypeexcl
END
END
END
/* SEARCH ACORDING TO THE DATE RANGES IN DATE RANGES TAB */
/* Search according to the Applicant Created Date */
IF(@created_from'')
BEGIN
SET @wherecondition=@wherecondition+ ' AND APP.APPLICANT_CREATED_DATE >= '''+@created_from+''''
END
IF(@created_to'')
BEGIN
SET @wherecondition=@wherecondition+ ' AND APP.APPLICANT_CREATED_DATE <= '''+@created_to+''''
END
/* Search according to the Applicant Last contacted Date */
IF(@lastcnt_from'')
BEGIN
SET @wherecondition=@wherecondition+ ' AND APP.CONTACTED_DATE >= '''+@lastcnt_from+''''
END
IF(@created_to'')
BEGIN
SET @wherecondition=@wherecondition+ ' AND APP.CONTACTED_DATE <= '''+@created_to+''''
END
/* Search according to the Applicant Last modified Date */
IF(@modifydate_from'')
BEGIN
SET @wherecondition=@wherecondition+ ' AND APP.MODIFY_DATE >= '''+@modifydate_from+''''
END
IF(@modifydate_to'')
BEGIN
SET @wherecondition=@wherecondition+ ' AND APP.MODIFY_DATE <= '''+@modifydate_to+''''
END
/* Search according to the Applicant Last CV Updated Date */
IF(@lastcvupdtd_from'')
BEGIN
SET @wherecondition=@wherecondition+ ' AND APP.APPLICANT_ORIGINAL_DATE >= '''+@lastcvupdtd_from+''''
END
IF(@lastcvupdtd_to'')
BEGIN
SET @wherecondition=@wherecondition+ ' AND APP.APPLICANT_ORIGINAL_DATE <= '''+@lastcvupdtd_to+''''
END
/* Search according to the Applicant Archived Date */
IF(@archived_from'')
BEGIN
SET @wherecondition=@wherecondition+ ' AND APP.APPLICANT_ARCHIVED_DATE >= '''+@archived_from+''''
END
IF(@archived_to'')
BEGIN
SET @wherecondition=@wherecondition+ ' AND APP.APPLICANT_ARCHIVED_DATE <= '''+@archived_to+''''
END
IF (@resp_options0)
BEGIN
IF (@resp_options=1)
BEGIN
SET @wherecondition=@wherecondition+' AND APP.APPLICANT_ID IN (SELECT APPLICANT_ID FROM HRMS_TBL.Applicant_Responded
WHERE APPLICANT_RESPONDED = ''Yes'')'
END
ELSE IF (@resp_options=2)
BEGIN
SET @wherecondition=@wherecondition+' AND APP.APPLICANT_ID IN (SELECT APPLICANT_ID FROM HRMS_TBL.Applicant_Responded
WHERE APPLICANT_RESPONDED = ''No'')'
END
END
/* SEARCH ACCORDING TO THE VALUES IN THE TEXTBOXES */
IF (@fao0)
BEGIN
SET @wheregeneral=@wheregeneral+' APP.APPLICANT_ID IN (SELECT APPLICANT_ID FROM HRMS_TBL.Applicant_Owner
WHERE FAO= '+CONVERT(VARCHAR,@fao)+')'
END
IF(@faoteam0)
BEGIN
SET @wheregeneral=@wheregeneral+' AND APP.APPLICANT_ID IN (SELECT APPLICANT_ID FROM HRMS_TBL.Applicant_Owner
WHERE FAO IN (SELECT USER_ID FROM HRMS_TBL.User_Table
WHERE USER_TEAM_ID ='+CONVERT(VARCHAR,@faoteam) +'))'
END
IF(@createdby0)
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.USER_ID='+CONVERT(VARCHAR,@createdby)
END
IF(@owner0)
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.APPLICANT_ID IN (SELECT APPLICANT_ID FROM HRMS_TBL.APPLICANT_OWNER WHERE OWNER_ID='+CONVERT(VARCHAR,@owner)+')'
END
IF(@group0)
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.APPLICANT_GROUP_ID='+CONVERT(VARCHAR,@group)
END
IF(@nation0)
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.APPLICANT_NATIONAL_ID='+CONVERT(VARCHAR,@nation)
END
IF(@media0)
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.APPLICANT_ID IN (SELECT APPLICANT_ID FROM HRMS_TBL.APPLICANT_MEDIA_SELECTED WHERE APPLICANT_MEDIA_ID='+CONVERT(VARCHAR,@media)+')'
END
IF(@statusgeneral0)
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.APPLICANT_STATUS_ID='+CONVERT(VARCHAR,@statusgeneral)
END
IF(@name'')
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.APPLICANT_FIRST_NAME LIKE '+''''+'%'+@name+'%'+''''
END
IF(@email'')
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.APPLICANT_EMAIL LIKE '+''''+'%'+@email+'%'+''''
END
IF(@ltdconame'')
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.APPLICANT_LTD_NAME LIKE '+''''+'%'+@ltdconame+'%'+''''
END
IF(@hometele'')
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.APPLICANT_HOME LIKE '+''''+'%'+@hometele+'%'+''''
END
IF(@worktele'')
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.APPLICANT_WORK LIKE '+''''+'%'+@worktele+'%'+''''
END
IF(@mobtele'')
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.APPLICANT_MOBILE LIKE '+''''+'%'+@mobtele+'%'+''''
END
IF(@QuickNotes'')
BEGIN
SET @wheregeneral=@wheregeneral +' AND APP.APPLICANT_Q_NOTE LIKE '+''''+'%'+@QuickNotes+'%'+''''
END
IF(@aID'')
BEGIN
SET @count=CHARINDEX(',',@aID)
IF(@count>1)
BEGIN
SET @wheregeneral=@wheregeneral+ ' AND (APP.APPLICANT_ID_TXT BETWEEN '+'''' + SUBSTRING(@aID,1,@count-1)+''''
+' AND '+'''' + SUBSTRING(@aID,@count+1,LEN(@aID))+''''+')'
END
END
IF (@linkd_cont0)
BEGIN
IF (@linkd_cont=1)
BEGIN
SET @wheregeneral=@wheregeneral+ ' AND APP.CONTACT_ID IS NOT NULL'
END
ELSE IF (@linkd_cont=2)
BEGIN
SET @wheregeneral=@wheregeneral+ ' AND APP.CONTACT_ID IS NULL'
END
END
/* SEARCH ACCORDING TO THE UK Post Code */
IF (@ZipCode'' AND @Distance0)
BEGIN
EXEC [HRMS_SP].[SP0504a_Post_Code_Search] @ZipCode,@Distance
SET @wherezipcode=@wherezipcode+ ' AND APP.APPLICANT_POSTALCODE IN (SELECT ZIP1 FROM TempPostCode)'
END
IF (@relocate0)
BEGIN
SET @wheregeneral=@wheregeneral+ ' AND APP.APPLICANT_RELOCATE ''N'' '
END
--IF(@wherecvsearch'')
--BEGIN
--SET @totalwhere=@totalwhere+@wherecvsearch
--END
IF(@wherekeyword'')
BEGIN
SET @totalwhere=@totalwhere+@wherekeyword
END
IF(@wherepostcodes'')
BEGIN
SET @totalwhere=@totalwhere+@wherepostcodes
END
IF(@whereaddr'')
BEGIN
SET @totalwhere=@totalwhere+@whereaddr
END
IF(@WhereClause'')
BEGIN
SET @totalwhere=@totalwhere+@WhereClause
END
IF (@Whereexclude'')
BEGIN
SET @totalwhere=@totalwhere+@Whereexclude
END
IF(@whereapptypeid'')
BEGIN
SET @totalwhere=@totalwhere+@whereapptypeid
END
IF(@wherecondition'')
BEGIN
SET @totalwhere=@totalwhere+@wherecondition
END
IF(@wheregeneral'')
BEGIN
SET @totalwhere=@totalwhere+@wheregeneral
END
IF (@wherezipcode'')
BEGIN
SET @totalwhere=@totalwhere+@wherezipcode
END
IF (@totalwhere'')
BEGIN
DECLARE @TRIM_AND VARCHAR(10)
SET @TRIM_AND=SUBSTRING(@totalwhere,0,6)
IF LTRIM(RTRIM(@TRIM_AND))='AND'
BEGIN
SET @totalwhere=SUBSTRING(@totalwhere,6,LEN(@totalwhere))
END
SET @totalwhere=' WHERE '+@totalwhere+ ' AND APP.APPLICANT_TEMP''Y'''
END
IF(@cvsearch='')
BEGIN
SET @condition= ' SELECT DISTINCT APP.APPLICANT_ID as IDs,
APP.APPLICANT_ID_TXT AS ID,
APP.APPLICANT_FIRST_NAME +'' ''+ APP.APPLICANT_LAST_NAME as Name,
APP.APPLICANT_ADDRESS as Address,
APP.APPLICANT_Q_NOTE as Notes,
APP.APPLICANT_HOME as [Home Telephone],
APP.APPLICANT_MOBILE as [Mobile Telephone],
REPLACE(CONVERT(VARCHAR,APP.APPLICANT_AVAILABLE,106),'' '',''-'') as Available,
REPLACE(CONVERT(VARCHAR,APP.CONTACTED_DATE,106),'' '',''-'') as Contacted,
REPLACE(CONVERT(VARCHAR,APP.APPLICANT_ORIGINAL_DATE,106),'' '',''-'') as [Last CV],
'''' AS ''OutPut'' INTO #TEMPAPP
FROM HRMS_TBL.Applicants APP
INNER JOIN HRMS_TBL.User_Table UT
ON UT.USER_ID=APP.USER_ID
LEFT OUTER JOIN HRMS_TBL.Applicant_Group AG
ON APP.APPLICANT_GROUP_ID=AG.APPLICANT_GROUP_ID
LEFT OUTER JOIN HRMS_TBL.Applicant_National AN
ON APP.APPLICANT_NATIONAL_ID=AN.APPLICANT_NATIONAL_ID
LEFT OUTER JOIN HRMS_TBL.Applicant_Status ST
ON APP.APPLICANT_STATUS_ID=ST.APPLICANT_STATUS_ID'+ @totalwhere
END
IF(@cvsearch'')
BEGIN
SET @condition= ' SELECT DISTINCT APP.APPLICANT_ID as IDs,
APP.APPLICANT_ID_TXT AS ID,
APP.APPLICANT_FIRST_NAME +'' ''+ APP.APPLICANT_LAST_NAME as Name,
APP.APPLICANT_ADDRESS as Address,
APP.APPLICANT_Q_NOTE as Notes,
APP.APPLICANT_HOME as [Home Telephone],
APP.APPLICANT_MOBILE as [Mobile Telephone],
REPLACE(CONVERT(VARCHAR,APP.APPLICANT_AVAILABLE,106),'' '',''-'') as Available,
REPLACE(CONVERT(VARCHAR,APP.CONTACTED_DATE,106),'' '',''-'') as Contacted,
REPLACE(CONVERT(VARCHAR,APP.APPLICANT_ORIGINAL_DATE,106),'' '',''-'') as [Last CV],
'''' AS ''OutPut'' INTO #TEMPAPP
FROM HRMS_TBL.Applicants APP
JOIN CONTAINSTABLE(HRMS_TBL.Applicants,APPLICANT_ORIGINAL,'''+@cvsearch+''') AS KEY_TBL
ON APP.APPLICANT_ID=KEY_TBL.
INNER JOIN HRMS_TBL.User_Table UT
ON UT.USER_ID=APP.USER_ID
LEFT OUTER JOIN HRMS_TBL.Applicant_Group AG
ON APP.APPLICANT_GROUP_ID=AG.APPLICANT_GROUP_ID
LEFT OUTER JOIN HRMS_TBL.Applicant_National AN
ON APP.APPLICANT_NATIONAL_ID=AN.APPLICANT_NATIONAL_ID
LEFT OUTER JOIN HRMS_TBL.Applicant_Status ST
ON APP.APPLICANT_STATUS_ID=ST.APPLICANT_STATUS_ID'+ @totalwhere
END
SET @RESULT=@condition
IF @totalwhere=''
BEGIN
SET @condition=@condition+ ' WHERE APP.APPLICANT_TEMP''Y'''
END
EXECUTE (@condition)
IF @resultcount 0
BEGIN
SELECT TOP (@resultcount) * FROM #TEMPAPP
SELECT @RESULT
END
ELSE
BEGIN
SELECT * FROM #TEMPAPP
SELECT @RESULT
END
IF OBJECT_ID('#TEMP_EXCLUDE','U') IS NOT NULL
BEGIN
DROP TABLE #TEMP_EXCLUDE
END
IF OBJECT_ID('#TEMPAPP','U') IS NOT NULL
BEGIN
DROP TABLE #TEMPAPP
END
IF OBJECT_ID('dbo.TESTTABLE1','U') IS NOT NULL
BEGIN
DROP TABLE dbo.TESTTABLE1
END
IF OBJECT_ID('dbo.TempPostCode','U') IS NOT NULL
BEGIN
drop table dbo.TempPostCode
END
END
June 18, 2009 at 10:14 am
Please check the forum before posting. Moving to t-SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply