My below code works fine in SSMS but it is giving me an error while executing using SSRS Incorrect Syntax near ',' Dataset.I am trying to select Multiple Parameters through SSRS.
Can anyone please guide?
Below is my code
Declare @Startdate datetime
Declare @Enddate datetime
Declare @Debug int=0
Declare @TimeZone int=330
Declare @OrganizationId int
Declare @OrganizationName varchar(320)
Set @Startdate ='2016-03-08 00:00:00'
Set @Enddate ='2016-03-09 00:00:00'
--Set @OrganizationName=@POrganization
Declare @Orgname varchar(100)
declare @OrganizationIds int
set @Orgname='Root Org,Maersk Line'
Declare @place TABLE(ID INT IDENTITY,Places varchar(100))
insert into @place (Places) values(@Orgname)
--select * from @place
Declare @RawData Table
(
ROWID int identity(1,1) primary key,
OrgId int,
Orgname varchar(100)
)
;WITH SplitSting AS
(
SELECT
ID,LEFT(Places,CHARINDEX(',',Places)-1) AS Part
,RIGHT(Places,LEN(Places)-CHARINDEX(',',Places)) AS Remainder
FROM @place
WHERE Places IS NOT NULL AND CHARINDEX(',',Places)>0
UNION ALL
SELECT
ID,LEFT(Remainder,CHARINDEX(',',Remainder)-1)
,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
FROM SplitSting
WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
UNION ALL
SELECT
ID,Remainder,null
FROM SplitSting
WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0
)
--select * from SplitSting
insert into @RawData(OrgName,OrgId)
SELECT S.Part,O.ID FROM SplitSting S
JOIN ORGANIZATION O
ON S.Part=O.NAME
--select * from @RawData
DECLARE @RowCount INT
Declare @String varchar(100)
SELECT @RowCount = COUNT(*) FROM @RawData
WHILE (@RowCount>0)
BEGIN
set @String=convert(varchar,@OrganizationIds)+','
If (@RowCount>0)
Begin
PRINT 'Loop Sequence : ' + convert(varchar,@RowCount) + ' '
set @OrganizationIds = (SELECT OrgId FROM @RawData WHERE ROWID = @RowCount)
PRINT 'Orgid Inside Loop:' + Convert(varchar,@OrganizationIds)
End
Set @RowCount = @RowCount-1
Set @OrganizationIds = convert(varchar,@OrganizationIds)
PRINT 'Orgid Outside Loop:'+ convert(varchar,@OrganizationIds)
set @String=@String + Convert(varchar,@OrganizationIds)
END
PRINT 'String Value Outside Loop: ' + @String
Declare @TempData Table
(
OrganizationID int
)
insert into @TempData(OrganizationID)
EXEC GetFormsData_Organization @String
