Error "Conversion failed when converting the..."

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

.NET Inside SQL Server (2005)

Error "Conversion failed when converting the..."

Author

Topic

tykhai
Starting Member

1 Post

Posted - 2012-04-04 : 12:53:07

Hi, i have stored procedures:

ALTER PROCEDURE [dbo].[KHP_DEB_AdvanceGetByUsers]
@StaffID int
AS

Declare @a as int
select @a=RoleID from userroles
where UserID=@StaffID and RoleID=0

SELECT
[KHP_DEB_Advance].[AdvanceID],
[KHP_DEB_Advance].[PortalID],
[KHP_DEB_Advance].[DateAdvance],
[KHP_DEB_Advance].[Amount],
[Users].[UserID] as [UserID],
[Users].[UserName] as UserName,
[KHP_DEB_Advance].[StaffID],
[KHP_DEB_Advance].[Description]
FROM [KHP_DEB_Advance]
INNER JOIN Users ON [KHP_DEB_Advance].[StaffID] = [Users].[UserID]
WHERE
StaffID LIKE CASE WHEN @a=0 THEN
'%'
ELSE
@StaffID
END

but....error
Conversion failed when converting the varchar value '%' to data type int.
Please help me!

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-04-25 : 06:09:12

Try this script,

ALTER PROCEDURE [dbo].[KHP_DEB_AdvanceGetByUsers]
@StaffID int
AS

Declare @a as int
select @a=RoleID from userroles
where UserID=@StaffID and RoleID=0

SELECT
[KHP_DEB_Advance].[AdvanceID],
[KHP_DEB_Advance].[PortalID],
[KHP_DEB_Advance].[DateAdvance],
[KHP_DEB_Advance].[Amount],
[Users].[UserID] as [UserID],
[Users].[UserName] as UserName,
[KHP_DEB_Advance].[StaffID],
[KHP_DEB_Advance].[Description]
FROM [KHP_DEB_Advance]
INNER JOIN Users ON [KHP_DEB_Advance].[StaffID] = [Users].[UserID]
WHERE
StaffID LIKE CASE WHEN @a=0 THEN
StaffID
ELSE
@StaffID
END

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-25 : 07:18:49

quote:
Originally posted by sql-programmers

Try this script,

ALTER PROCEDURE [dbo].[KHP_DEB_AdvanceGetByUsers]
@StaffID int
AS

Declare @a as int
select @a=RoleID from userroles
where UserID=@StaffID and RoleID=0

SELECT
[KHP_DEB_Advance].[AdvanceID],
[KHP_DEB_Advance].[PortalID],
[KHP_DEB_Advance].[DateAdvance],
[KHP_DEB_Advance].[Amount],
[Users].[UserID] as [UserID],
[Users].[UserName] as UserName,
[KHP_DEB_Advance].[StaffID],
[KHP_DEB_Advance].[Description]
FROM [KHP_DEB_Advance]
INNER JOIN Users ON [KHP_DEB_Advance].[StaffID] = [Users].[UserID]
WHERE
StaffID ~~LIKE~~ = CASE WHEN @a=0 THEN
StaffID
ELSE
@StaffID
END

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Slight modification.
Don't use LIKE if it is not really needed.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources