I am passing multiple string values from SSRS 2005 to an SP... But I am unable to do so...
Double post, see discussion at
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c954f178-1ba6-4845-905a-252804eaa707Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich tglich
Blog
Xing
March 19th, 2011 2:58pm
Hi,
The parameter you are passing are multiselect or single select?
if it is a multiselect then you have to use charindex function so that data should come like that in in clause ('a','b','c')
CHARINDEX( ',' +Assigned_Group
+ ',', ',' + (@String) + ',' ) > 0 )
other wise it will through error.
Please mar as answer if this post helps.
Thanks,
Shobhit
Free Windows Admin Tool Kit Click here and download it now
March 19th, 2011 5:21pm
Hi All,
I am passing multiple string values from report parameter in ssrs 2005 to SP which has variable but i am unable to do so the SP is like:
-- =============================================
---- Author: Prince Kher
---- Create date: 07-03-2011
---- Description: APM_Asset Tickets Log
----pr_APM_Tickets 'ODA-CSR-Operations,ODA-PMR-Operations','2011-02-04','2011-02-28'
--ODA-PMR-Request Fulfilment,
--ODA-BPS-Request Fulfilment,
--ODA-Flex PMR-Request Fulfilment','2011-02-04','2011-02-28'
-- =============================================
Alter
PROCEDURE pr_APM_Tickets
(@Assigned_Group Varchar
(MAX),
@StartDate
Datetime,
@EndDate
Datetime
)
AS
BEGIN
SET
NOCOUNT ON;
Declare @String
Varchar(400)
set @string=
(select
replace( @Assigned_Group,',',''','''))
--set @string= ''' + @string + '''
--select @string
SELECT
Incident_Number,
Entry_ID,
SLM_Status,
SLA_Resolution,
SLA_Response,
Assignee,
Reported_Date,
Create_Date,
--NUM,
Hours,
Responded_Date,
Assigned_Group,
Last_Modified_Date,
Status_ID,
Priority_ID
FROM(
select
--top 1
Incident_Number,
Entry_ID,
SLM_Status,
SLA_Resolution,
SLA_Response,
Assignee,
Reported_Date,
Create_Date,
RANK()
OVER
(PARTITION
BY Incident_Number ORDER
BY Create_Date) AS NUM,
DATEDIFF(hh,Reported_Date,Create_Date)
AS Hours,
Responded_Date,
ITSM.Assigned_Group,
Last_Modified_Date,
Status_ID,
Priority_ID
from itsm_incidentmgmt ITSM
with (nolock)
Inner
Join
ITSM_IncidentMgmt_AuditLog
with (nolock)
on original_request_id=Entry_ID
and [log] like
'%Pending%'
where
Assigned_Group in
( @String)and Reported_Date
between @StartDate and @EndDate
and (SLA_Resolution=1
or SLA_Response=1)
--and Min(create_date)
)AS A
WHERE Num
= 1
END
Please help me make this work.
Regards
Prince Kher
Prince Kher
March 19th, 2011 5:37pm
Hi,
try by using below sample query
DECLARE @Assigned_Group NVARCHAR(MAX)
SET @Assigned_Group = Char(44) + @Assigned_Group + Char(44)
SELECT * FROM itsm_incidentmgmt
WHERE @Assigned_Group Like '%,' + Assigned_Group + ',%'
Rajesh Jonnalagadda http://www.ggktech.com
Free Windows Admin Tool Kit Click here and download it now
March 19th, 2011 6:00pm
Hi,
you could also using the following example
--replace this part
Declare @String Varchar(400)
set @string= (select replace( @Assigned_Group,',',''','''))
--with that part
declare @String as table(Assigned_Group varchar(400))
declare @xml as xml
SET @xml = cast(('<X>'+replace(@Assigned_Group,',' ,'</X><X>')+'</X>') as xml)
insert into @String select C.value('.', 'varchar(50)') as Assigned_Group FROM @xml.nodes('X') as X(C)
--AND
--replace this part
where Assigned_Group in ( @String)and Reported_Date between @StartDate and @EndDate and
--with that part
where Assigned_Group in (select Assigned_Group from @String) and Reported_Date between @StartDate and @EndDate and
March 19th, 2011 6:16pm
HI
I dnt got you what you are trying to say can you explain in detail..
Please Advice..
RegardsPrince Kher
Free Windows Admin Tool Kit Click here and download it now
March 20th, 2011 6:00pm
Hi Sir,
I am unable to get you exactly wat are you trying to do here..?
Why are we assigning variable value like that ..
1)WHERE @Assigned_Group
Like '%,' + Assigned_Group +
',%'
2)SET @Assigned_Group =
Char(44) + @Assigned_Group + Char(44)
Will it work in2005
It worked but wanna know how it is executing thanks for all the help.
Regards
Prince Kher
Prince Kher
March 20th, 2011 6:05pm