April 29, 2011 at 1:41 am
Hi All,
i am facing problem in my report, bellow sp working fine in SSMS but inmy report side
i am using below query to pass the parameter values in the report.
select distinct case when IsActive=1 then 'Active' Else 'NotActive' end as ActiveStatus
from OfficeUserRole_Dim
Below Error Is comeing:convertion failed when converting varchar to bit.
ALTER PROCEDURE [dbo].[SP_GrantMaker]
(
@ActiveStatus Varchar(Max),
)
AS
-- exec [SP_GrantMaker]
BEGIN
select
OfficeUserRoleId as RoleID,
Officename as EntityName,
OfficeRole as EntityType,
OUR.OfficeId as EntityID,
case when OUR.IsActive=1 then 'Active' Else 'NotActive' end as Activestatus
from OfficeUserRole_Dim OUR
inner join costcenter_Dim CCD on CCD.CostCenterName=OUR.OfficeName
where OUR.IsActive IN (SELECT DISTINCT ITEMS FROM dbo.Split(@ActiveStatus, ','))
order by Officename, OfficeRole
END
Plese slow my problem
thanks in advance
April 29, 2011 at 5:34 am
I think the problem is the where condition. Usually you need to cast the split function back to int (since it starts as [n]varchar).
Try to rerun the query without the where just to see if this is the problem.
HTH.
April 29, 2011 at 5:09 pm
You may also need to JOIN() your parameter values prior to passing them to your stored procedure
May 3, 2011 at 4:31 am
Thanks, it is working now.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply