LIKE '%' on Int - 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 2008 Forums

Transact-SQL (2008)

LIKE '%' on Int

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-01-03 : 18:54:05

I have the following stored proc:

@pID int,
@pJobID int,
@pDepartmentID int

SELECT
E.ID,
E.CompanyID,
WT.TitleDescription,
WG.GroupDescription,
E.Superordinate,
E.FirstName,
E.LastName,
JT.JobTitleDescription,
WD.DepartmentDescription,
E.EmailAddress,
E.WorkPhone
FROM
HRIS_Employee E,
HRIS_WorkingJobTitle JT,
HRIS_WorkingDepartment WD,
HRIS_WorkingTitles WT,
HRIS_WorkingGroups WG
WHERE
E.CompanyID = @pID AND
E.WorkingJobTitleID LIKE
CASE
WHEN @pJobID > 0 THEN
@pJobID
ELSE
'%'
END AND
E.DepartmentID LIKE
CASE
WHEN @pDepartmentID > 0 THEN
@pDepartmentID
ELSE
'%'
END AND
E.WorkingJobTitleID = JT.ID AND
E.DepartmentID = WD.ID AND
E.WorkingTitleID = WT.ID AND
E.WorkingGroupID = WG.ID

I am getting the following error:
"Conversion failed when converting the varchar value '%' to data type int."

If I run a simple query like this, it works fine.

select * from HRIS_Employee E
where E.ID Like '%'

In this case I am using LIKE '%' against the E.ID field which is an int. So why does it fail in the stored proc and not here??

Any suggestions or workarounds??

Thanks!!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-03 : 20:25:47

CASE
WHEN @pDepartmentID > 0 THEN
@pDepartmentID
ELSE
'%'
END

@pDepartmentID is an int so the case statement has to procuse an int. As '%' cannot be converted to an int it produces an error.
Doing a like statement with an int doesn't make sense anyway.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-01-04 : 01:14:24

I need a way to select 1 row or all rows based on the 2 incoming parameters.

I thought of using 4 different if statements, but figured the CASE statement would reduce a lot of duplicate code. For instance:

IF(A > 0 AND B > 0)
select * from table where col1=A and col2=B
IF(A < 0 AND B > 0)
select * from table where col2=B
IF(A > 0 AND B < 0)
select * from table where col1=A
IF(A < 0 AND B < 0)
select * from table

There must be a more elegant solution than this???

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-04 : 02:26:27

[code]...
AND E.WorkingJobTitleID = CASE
WHEN @pJobID > 0 THEN @pJobID
ELSE E.WorkingJobTitleID
END[/code]

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 03:30:47

select *
from table
where (A>0 and B>0 and col1=A and col2=B)
or (A < 0 AND B > 0 and col2=B)
or (A > 0 AND B < 0 and col1=A)
or (A < 0 AND B < 0)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-01-04 : 09:57:06

Thanks for your help Lumbago and Nigel!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources