An SP to Search a record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

An SP to Search a record

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-21 : 06:12:03

Hi All,
I have an SP which searches for a record in the table based on the search conditions given from the front-end.

The user may select all or any one or more than one options.
So i have used a OR condition in my where clause of the query.
But this gives all the records in the table matching all the fields.

Ex: My parameters are ID,Name,Phone,Role.

now i have given

SELECT * FROM TAB
WHERE id = @id
OR name like '%@name%'
OR phone like '%@phone%'
OR role = @role

If i give id = 45, and dont give any other field it should return 1 record as id is PK. but it is giving more than 100 rows.

What is the error here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 06:18:03

keep default values of parameters as null and use like


SELECT * FROM TAB 
WHERE (id = @id  or @id is null)
AND (name like '%@name%' or @name is null)
AND (phone like '%@phone%' or @phone is null)
AND (role = @role or @role is null)

EDIT: fixed error changing OR to AND
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-04-21 : 06:22:42

Should be:


WHERE (id = @id  or @id is null)
AND (name like '%@name%' or @name is null)
AND (phone like '%@phone%' or @phone is null)
AND (role = @role or @role is null)

I think?

Kristen
Test

22859 Posts

Posted - 2010-04-21 : 06:24:39

PS and if parameters are passed as blanks then pre-adjust them:


SELECT @name = NullIf(RTrim(@name), ''),
       @phone = NullIf(RTrim(@phone), ''),
       ....

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-21 : 06:26:28

hi visakh,
Thanks for the reply. I tried this but still i am getting all the records from the table.
your suggestions are welcome.

There is only one record in the table with id = 45

How to make parameters to have NULL values by default?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 06:29:21

quote:
Originally posted by mrm23

hi visakh,
Thanks for the reply. I tried this but still i am getting all the records from the table.
your suggestions are welcome.

There is only one record in the table with id = 45

How to make parameters to have NULL values by default?

that should be AND instead of OR as Kristen suggested. Missed that. just copied from your suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-21 : 06:29:38

Hi Kirsten,

I think using AND will not work as the input is unknown.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 06:30:43

quote:
Originally posted by mrm23

Hi Kirsten,

I think using AND will not work as the input is unknown.

it will work. try it first and see

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-21 : 06:33:25

I didnt get any record when did it with AND and that Nullif () func.

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-21 : 06:35:03

If i decalre the variables seperately and then run the query it works fine (in this case i have retained the earlier format with OR).
but with proc this is not happening.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 06:37:10

quote:
Originally posted by mrm23

I didnt get any record when did it with AND and that Nullif () func.

did you keep default values of parameters as null?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-04-21 : 06:39:06

Please try with..

DECLARE @tempTbl TABLE (id INT, FirstName VARCHAR(20), lname VARCHAR(20))

INSERT @tempTbl
SELECT 1, 'test1','T1' UNION ALL
SELECT 2, 'test2','t2' UNION ALL
SELECT 3, 'TEst3','t3' UNION ALL
SELECT 4, 'Test4','t'

SELECT * FROM @tempTbl
WHERE id = 1 -- HERE FOR IS IF IT IS NULL THEN YOU CAN USE LIKE ISNULL(@ID,ID)
and FirstName like '%%' -- AND FOR ISNULL(@NAME,'')
and lname like '%%'

Kristen
Test

22859 Posts

Posted - 2010-04-21 : 06:41:40

quote:
Originally posted by mrm23

If i decalre the variables seperately and then run the query it works fine (in this case i have retained the earlier format with OR).
but with proc this is not happening.

Suggest put a PRINT statement , or somesuch, in the sproc so you can see EXACTLY what parameters are being passed, and what they look like. Maybe something is sending "0" rather than EMPTY_STRING or NULL ??

You can use NullIf to set to NULL if the value is "0" (or whatever "any value" you need

)

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-21 : 06:41:52

How to make parameters to have NULL values by default?

Kristen
Test

22859 Posts

Posted - 2010-04-21 : 06:44:37

"and lname like '%%'"

Note that this won't match any row where [lname] is Null. Ditto for the other columns, and likewise for using ISNULL(@ID, ID) as a method of matching a value, or NULL - it doesn't select rows where the actual value of the column is Null (unless you fiddle with the ANSI_NULL settings, which I don't recommend.

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-04-21 : 06:47:33

quote:
Originally posted by Kristen

"and lname like '%%'"

Note that this won't match any row where [lname] is Null. Ditto for the other columns, and likewise for using ISNULL(@ID, ID) as a method of matching a value, or NULL - it doesn't select rows where the actual value of the column is Null (unless you fiddle with the ANSI_NULL settings, which I don't recommend.

Yes, kristen you are right

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-21 : 06:55:52

is this correct?

WHERE hrb.uniqueno = @CandID
AND (hrb.FirstName like 'NullIf(RTrim(@CandName), '')' OR @CandName is null)
AND (hrb.LastName like 'NullIf(RTrim(@CandName), '')' OR @CandName is null)
AND (hrb.EmailId in (Nullif(RTrim(@EmailID),'')) OR @EmailID is null)
AND (hrb.Mobile = Nullif(RTrim(@EmailID),'') OR @MobileNo is null)
AND (hrb.ResumeSourceID = @CentreID OR @CentreID is null)
AND (hrb.TotalExp = @TotalExperience OR @TotalExperience is null)

Kristen
Test

22859 Posts

Posted - 2010-04-21 : 07:50:02

You need to move the NullIf stuff out of the WHERE clause.

@CandName is used several times (and you have only used NullIf() on some of them - it would need to be on all of them - but the potential repeated calls may be slower, and may prevent SQL making the best choice for a query plan.

There are a couple of extraneous single quotes in there too

You can't use IN with a variable (well you can, but it won't do what you most probably expect)
AND (hrb.EmailId in (Nullif(RTrim(@EmailID),'')) OR @EmailID is null)

You have Mobile field referencing incorrect parameter:
AND (hrb.Mobile = Nullif(RTrim(~~@EmailID~~@MobileNo),'') OR @MobileNo is null)

You are not processing @CentreID and @TotalExperience to force NULL - so either the application will pass NULL for "any value", or you need to do that for those fields explicitly.

so probably some like this:


SELECT @CandName = NullIf(RTrim(@CandName), ''),
       @EmailID = NullIf(RTrim(@EmailID), ''),
       @MobileNo = NullIf(RTrim(@MobileNo), ''),
	-- Assumed that these are numeric fields and 0 value means "any value"
       @CandID = NullIf(@CandID, 0),
       @CentreID = NullIf(@CentreID, 0),
       @TotalExperience = NullIf(@TotalExperience, 0)

SELECT ...
FROM ...
WHERE	    (hrb.uniqueno = @CandID OR @CandID is null)
	AND (hrb.FirstName like @CandName OR @CandName is null)
	AND (hrb.LastName like @CandName OR @CandName is null)
	AND (hrb.EmailId = @EmailID OR @EmailID is null) -- Assuming equals is OK for this test
	AND (hrb.Mobile = @MobileNo OR @MobileNo is null)
	AND (hrb.ResumeSourceID = @CentreID OR @CentreID is null)
	AND (hrb.TotalExp = @TotalExperience OR @TotalExperience is null)

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-21 : 08:35:28

Thanks a lot Kirsten! This works Fantastically!

I learnt a very good point here. Thanks to SQL Team :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 23:40:03

quote:
Originally posted by mrm23

How to make parameters to have NULL values by default?

like below
@parameter <datatype> = NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-23 : 09:00:46

Hi All,
I thank you for your time in answering the queries in this regard.
Now i am facing a new problem: what if the @CandID is null?
And the previous employer column of the table may have more than one record for each record.
in that case i need to show only one.

i used max() in my query but it didnt work.
kindly help.

this is my query:
SELECT @CandName = NullIf(RTrim(@CandName), ''),
@EmailID = NullIf(RTrim(@EmailID), ''),
@MobileNo = NullIf(RTrim(@MobileNo), ''),-- Assumed that these are numeric fields and 0 value means "any value"
@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.IndtypeID,hrb.FunctionID,hrb.Working,hrb.WorkingFrom,hrb.PresentEmployer
,hrb.PresentCTC,hrb.ExpectedCTC,hre.Employer,hrb.resumesourceID
FROM hc_resume_bank hrb
JOIN hc_resume_employer hre
ON hrb.rid = hre.resumeid
WHERE (hrb.uniqueno = @CandID OR @CandID is null)
AND (hrb.FirstName like @CandName OR @CandName is null)
AND (hrb.LastName like @CandName OR @CandName is null)
AND (hrb.EmailId = @EmailID OR @EmailID is null) -- Assuming equals is OK for this test
AND (hrb.Mobile = @MobileNo OR @MobileNo is null)
AND (hrb.ResumeSourceID = @CentreID OR @CentreID is null)
AND (hrb.TotalExp = @TotalExperience OR @TotalExperience is null)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources