April 25, 2010 at 11:39 pm
Hi All,
Please help me for the last time.
This is my SP. I am not able to EXECUTE it. I removed BEGIN and END but it didnt help.
ALTER PROCEDURE [dbo].[sp_TEMP_TLCOMSSearchCand] (@CandID varchar(50) = NULL,@CandName varchar(50) = NULL
,@EmailID varchar(50) = NULL,@MobileNo varchar(15) = NULL
,@CentreID bigint = NULL ,@TotalExperience decimal(18,2) = NULL
,@PrefLoc varchar(100) = NULL)
AS
DECLARE @sql nvarchar(4000)
Declare @LocID bigint
Select @LocID = locationid from hc_resume_preffered_location hpl join hcm_resume_locations hrl on hpl.locationid = hrl.rid
SELECT @sql =
'
SELECT hrb.uniqueno,hrb.FirstName,hrb.MiddleName,hrb.LastName,hrb.Father_Occupation,hrb.Monthly_FamilyIncome
,hrb.EmailID,hrb.Mobile,hrb.PhoneH,hrb.DoB,hrb.Gender,hrb.PresentAddress,hrb.Landmark,hrb.CountryID,hrb.StateID,hrb.LocationID
,hrb.ResumeConvertedText,hrb.TotalExp,hrb.IndustryText,hrb.FunctionText,hrb.Working,hrb.WorkingFrom,hrb.PresentEmployer
,hrb.PresentCTC,hrb.ExpectedCTC,dbo.fn_GetSeparatedText(hrb.rid) as PrevEmployer,hrb.resumesourceID
FROM hc_resume_bank hrb
JOIN hc_resume_preffered_location hpl on hrb.rid = hpl.resumeid
WHERE 1 = 1'
IF @CandID IS NOT NULL
SELECT @sql = @sql + ' AND hrb.uniqueno like @CandID'
IF @CandName IS NOT NULL
SELECT @sql = @sql + ' AND hrb.firstname like @CandName'
IF @EmailID IS NOT NULL
SELECT @sql = @sql + ' AND hrb.EmailID like @EmailID'
IF @MobileNo IS NOT NULL
SELECT @sql = @sql + ' AND hrb.Mobile = @MobileNo'
IF @CentreID IS NOT NULL
SELECT @sql = @sql + ' AND hrb.resumesourceid = @CentreID'
IF @TotalExperience IS NOT NULL
SELECT @sql = @sql + ' AND hrb.TotalExp like @TotalExperience'
IF @PrefLoc IS NOT NULL
SELECT @sql = @sql + ' AND hrb.locationid = @LocID'
EXEC sp_executesql @sql,
N'@_CandID vaRchar(50), @_CandName vaRchar(50), @_EmailID varchar(50), @_MobileNo varchar(15),@_CentreID bigint,@_@TotalExperience decimal(18,2)',
@_CandID = @CandID, @_CandName = @CandName, @_EmailID = @EmailID, @_MobileNo = @MobileNo,
@_CentreID = @CentreID,@_TotalExperience = @CandID,@_PrefLoc = @PrefLoc
GO
exec sp_TEMP_TLCOMSSearchCand @CandID = '' ,@CandName = 'Abraham',@EmailID = 'abraham@123.com',
@MobileNo = '',@CentreID = '',@TotalExperience = 0
April 26, 2010 at 1:43 am
What error are you getting?
-- Gianluca Sartori
April 26, 2010 at 2:46 am
hi,
you should avoid using prefix sp_ in your stored procedures. this is not related to your execution error, but just wanted to inform you.
Regards,
MShenel
April 26, 2010 at 2:59 am
shen-dest (4/26/2010)
hi,you should avoid using prefix sp_ in your stored procedures. this is not related to your execution error, but just wanted to inform you.
shen-dest, you're right. You could also tell the OP the reason why this is not a good choice. Stored procedures with the "sp_" prefix are first looked up in the master database, then in the current database context, so it could lead to a performance hit.
-- Gianluca Sartori
April 26, 2010 at 6:55 am
It really depends on the error you're getting. I was able to compile the procedure on my machine, but since I don't have your structures, I'm getting an error because the tables are missing. Presumably that's different than your error.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply