Wildcards Bit more info... - 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 2000 Forums

SQL Server Development (2000)

Wildcards Bit more info...

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-03 : 10:52:12

I have a search form with several text fields whcih the user can fil inor leave blank. I have the following stored procedure which works if an entry is made in the text fields, but won;t work if the text field is "" Can someone tell me what is wrong please.

CREATE PROCEDURE spRMU_GetRequestsFiltered2

@strRMUReqList int,
@strReqNo int,
@strDepartment nvarchar(100),
@strDivision nvarchar(100),
@strSection nvarchar(100),
@strSurname nvarchar(100),
@strFirstname nvarchar(100)

AS

Select *
from vweProcessRequestsALL
WHERE
Dept like '%'+@strDepartment+'%'and
Division like '%'+@strDivision +'%' and
Sect like '%'+@strSection+'%' and
Firstnames like '%'+@strFirstname+'%' and
Surname like '%'+@strSurname+'%'
ORDER BY MovementId DESC
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-03 : 10:54:57

can you provide some sample data??

Go with the flow & have fun! Else fight the flow

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-03 : 11:07:15

I am entering Development in @strDepartment and Planning in @strDivision and leaving the other fields blank. There are hundreds of records matching this but I am only getting back the two that have no surname or firstnames or section. If I fill in all the fields I get nothing back ???????

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-03-03 : 11:09:34

Run this and see what you get:
Select '%' + null + '%'

then try this query:


Select *
from vweProcessRequestsALL
WHERE
Dept like '%'+isnull(@@strDepartment,'')+'%'and
Division like '%'+isnull(@@strDivision,'') +'%' and
Sect like '%'+isnull(@@strSection,'')+'%' and
Firstnames like '%'+isnull(@@strFirstname,'')+'%' and
Surname like '%'+isnull(@strSurname,'')+'%' 
ORDER BY MovementId DESC

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-03 : 11:12:58

or even better put the default values of your input paramteters to ''
that way you don't need isnull check.

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-03-03 : 11:17:17

true

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-03 : 11:19:47

I'm not sure how you mean to run Select '%' +null+'%'

I tried changing the sp and it still didn't work...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-03 : 11:23:24

your sproc should look like this:
if that's not working then we'll need table definitions some sample data 20 rows max,
and desired results based on sample data.

CREATE PROCEDURE spRMU_GetRequestsFiltered2

@strRMUReqList int,
@strReqNo int,
@strDepartment nvarchar(100) = '',
@strDivision nvarchar(100) = '',
@strSection nvarchar(100) = '',
@strSurname nvarchar(100) = '',
@strFirstname nvarchar(100) = ''

AS

Select *
from vweProcessRequestsALL
WHERE
Dept like '%'+@strDepartment+'%'and
Division like '%'+@strDivision +'%' and
Sect like '%'+@strSection+'%' and
Firstnames like '%'+@strFirstname+'%' and
Surname like '%'+@strSurname+'%'
ORDER BY MovementId DESC
GO

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-03-03 : 11:28:20

quote:
Originally posted by Pinto

I'm not sure how you mean to run Select '%' +null+'%'

I tried changing the sp and it still didn't work...

That was simply to demonstrate what happens when you try to add a null. if you don't specify the param value, then it comes in as a null. that would make your query read like:

Select....
Where...
and firstName like null -- this is bad.

see spirit's post for correction.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-03 : 11:41:06

This seems to work....I had null values in some of the fields

Select *
from tblFileRequests
WHERE
Dept like '%'+@strDepartment+'%' or @strDepartment = null and
Division like '%'+@strDivision +'%' or @strDivision = null and
Sect like '%'+@strSection+'%' or @strSection = null and
Firstnames like '%'+@strFirstname+'%' or @strFirstname = null and
Surname like '%'+@strSurname+'%' or @strSurname = null
ORDER BY MovementId DESC
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-03 : 11:52:25

didn't the sproc with my correction's work?? have you tried it?

Go with the flow & have fun! Else fight the flow

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-04 : 04:37:50

Yes, I have tried it but I get the same results as I did with mine. It is only returning the records with no surname or firstname.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-04 : 10:46:34

I think I now know the reason but do not have a solution ! If one of the search textboxes on my page is left blank the value in it is a zero length string. My sql then evaluates to Surname Like '&&' so it is only returning records WITH NO surname present. Any ideas on how to get round this.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-04 : 11:06:45

Surname like '%'+@strSurname+'%'
will result in Surname like '%%'
if @strSurname is '' that will return all records that don't have null in Surname.

Go with the flow & have fun! Else fight the flow

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-07 : 05:33:29

If the surname text field is left blank I want records with a surname and without a surname. ie the field is ignored and not used in the search. I only want the fields that have data entered to be searched on, the others that are blank to be ignored.

Would it be easier to build my SQL in the code instead of using a SP ?

Thanks for your help spirit1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources