is250sp (3/6/2013)
In my dataset I have this script. Report parameter @acct is set as multi-value. When I select a single value it runs fine, however when I select multi-value I get the error....Incorrect syntax near '+'EXECUTE ('USE ' + @dbname + '
select g.cpnyid,g.JrnlType,g.Module,g.PerPost,g.Acct,g.Sub,substring(g.sub,6,5) as Cost_Ctr,left(g.sub,3) as Dept,substring(g.sub,4,2) as Legal_Entity
,g.DrAmt,g.CrAmt,0 as Amt,g.TranType,g.BatNbr,g.ID,g.refnbr as VONbr_InvNbr,g.ExtRefNbr as Vendor_InvNbr,g.TranDesc
from gltran g
where left(g.perpost,4) = ' + @year + '
and right(g.perpost,2) = ' + @month + '
and left(g.acct,1) in (' + @acct + ')
and (g.dramt <> 0 or g.cramt <> 0)')
Try to do this
--> Create temtp table
CREATE TABLE #Test(MonthOfYear int, Year int, Account int,Name varchar(255))
INSERT INTO #Test
VALUES(1,2013,1,'A')
INSERT INTO #Test
VALUES(12,2012,4,'B')
INSERT INTO #Test
VALUES(1,2013,4,'C')
INSERT INTO #Test
VALUES(1,2012,1,'D')
INSERT INTO #Test
VALUES(1,2013,5,'E')
--Build dynamic sql
DECLARE @Month int, @Year int, @account varchar(255)
,@sql varchar(1000)
,@DBName varchar(100) ='TEST'
-->Search single value in @account
SELECT @Month = 1, @Year = 2013, @account ='1'
SET @sql = '
SELECT MonthOfYear, Year, Account,Name
FROM #Test
WHERE MonthOfYear ='+CONVERT(varchar(2),@Month)
+' AND Year = '+CONVERT(varchar(5),@Year)
+' AND Account IN('+@Account+')'
PRINT @sql
EXEC ('USE '+@DBName+ @sql)
--> Search mutilple values in @account
SELECT @Month = 1, @Year = 2013, @account ='1,4'
SET @sql = '
SELECT MonthOfYear, Year, Account,Name
FROM #Test
WHERE MonthOfYear ='+CONVERT(varchar(2),@Month)
+' AND Year = '+CONVERT(varchar(5),@Year)
+' AND Account IN('+@Account+')'
PRINT @sql
EXEC ('USE '+@DBName+ @sql)
DROP TABLE #Test