SP to search a record - Urgent!!!

Question

Post reply

SP to search a record - Urgent!!!

malavika.ramanathan

SSCrazy

Points: 2790
More actions
April 24, 2010 at 5:54 am

#220802

Hi All,
I have a search screen in frontend which has the foll fields:
@CandID.@CandName,@EmailID,@MobileNo,@TotExp.
The user can enter all or any of these. when he hits the search button, the record is retrieved without any duplication.
As of now,to avoid duplication i am making all the search fields compulsory. but this should not be the case. he may enter name, or name and email id etc...
how to write query for this?
Below is my query:
ALTER PROCEDURE [dbo].[sp_TLCOMSSearchCand] (@CandID varchar(50),@CandName varchar(50),@EmailID varchar(50),
@MobileNo varchar(15),@CentreID bigint,@TotalExperience decimal(18,2))
AS
BEGIN
SELECT @CandName = NullIf(RTrim(@CandName), ''),
@CandID = NullIf(@CandID, ''),
@CentreID = NullIf(@CentreID, 0),
@TotalExperience = NullIf(@TotalExperience, 0)
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
WHERE (hrb.uniqueno = @CandID OR @CandID is null)
OR (hrb.FirstName like @CandName OR @CandName is null)
AND (hrb.LastName like @CandName OR @CandName is null)
AND (hrb.EmailId like @EmailID OR @EmailID is null)
AND (hrb.Mobile = @MobileNo OR @MobileNo is null)
AND (hrb.ResumeSourceID = @CentreID OR @CentreID is null)
AND (hrb.TotalExp = @TotalExperience OR @TotalExperience is null)
END

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 1

Hi,

Are you in a position to change the table definition for these fields ? Will you be able to make it unique field? .

As you know if these fields are not unique then the table can have duplicate values and for sure it will return those duplicates if you try to search with any one of the field value.

Thanks & Regards,
MC

malavika.ramanathan SSCrazy Points: 2790 More actions · Answer 2

the CandID column is unique. My prob is the user may search by entering any key word. He may give only one or a combination of parameters.

My SP must work for all possible combinations.

For ex, if he enters name and email id , it must return the record with that name and email combination only.

suggestions are most welcome.

thanks.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Start with this article: http://www.sommarskog.se/dyn-search-2005.html

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 4

Hi Ramanathan,

In that case better go with the dynamic SQL as Jeffrey mentioned.

You can check whether each of the input parameter of the SP is NULL or not, if it is not NULL you can include that field also in the WHERE condition of the dynamic SQL

Thanks & Regards,
MC

malavika.ramanathan SSCrazy Points: 2790 More actions · Answer 5

Hi,

Thanks for your reply. That link helped. But i am not getting desired o/p.

Following the method mentioned in the link, i tried writing this:

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)

AS

BEGIN

DECLARE @sql nvarchar(4000)

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

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 = @TotalExperience'

END

But this works only when @CandID is NOT NULL.

I gave @CandID = '' and supplied values to @CandName and @EmailID.

It is returning all rows. But there is only one such row.

Please help. its very urgent.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

I don't see where you are wexecuting the dynamic sql. Either way, '' is not the same as null.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

malavika.ramanathan SSCrazy Points: 2790 More actions · Answer 7

Oh sorry. You are right. I am not executing that query. How to do that? Should i print that parameters agian?

Now how to get the result set?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

Review the article again - it shows you.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 9

Hi Ramanathan,

Also please note that you cant give the parameter within the single quotes. It should be

If @parametername is not null

set @sql = @sql + 'and columnname = ' + @parametername

then finally execute it as exec sp_executesql @sql

Thanks & Regards,
MC

juanv-1150139 SSC Rookie Points: 26 More actions · Answer 10

This is really awesome reading! Thanks so much for posting this link. I have learned many cool things and realized how much better my T-Sql could be...really appreciate your advice.

Juan C. Vega

malavika.ramanathan SSCrazy Points: 2790 More actions · Answer 11

Hi Mithun,

Thanks for the reply. Yes that was the mistake. I finally resolved this.

Thanks for all the experts who helped me 🙂