| Author |
 Topic  |
|
|
tykhai
Starting Member
Vietnam
1 Posts |
 Posted - 04/04/2012 : 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
USA
189 Posts |
Posted - 04/25/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 04/25/2012 : 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. |
|
|
| |
Topic |
|
|
|
Error "Conversion failed when converting the..."
