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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics