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.
| 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. |
|
|
|
|
|
|
Error "Conversion failed when converting the..."