September 9, 2010 at 2:32 am
Hi, I have created a report which has a query which takes param @ Database (single value) and do select from the selected database.
Second parameter in query is the @ Objects that may be multiple values.
The query in RS2008R2 looks like this:
DECLARE @ SQLQuery AS nvarchar (500)
SET @ SQLQuery =
'SELECT name, id, xtype, info FROM' @ Database '. SYS.SYSOBJECTS
WHERE [name] IN (''' @ Objects ''')'
EXECUTE sp_executesql @ SQLQuery
It works well if @ Objects parameter has just a value.
Problem is:
Since @ Objects may be multiple values, problem occurs if it takes multiple values.
In Profiler query with multiple values look like this :
exec sp_executesql N'DECLARE @sqlquery AS nvarchar(500)
SET @sqlquery =
''SELECT name,id,xtype, info FROM '' @Database ''.SYS.SYSOBJECTS
WHERE [name] IN ('''''' N''sysallocunits'',N''sysdbfrag'' '''''')''
EXECUTE sp_executesql @sqlquery ',N'@Database nvarchar(6)',@Database=N'master'
And get this error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ','.
I have created and attached a similar report, that you may recreate problem locally.
In reality, the case goes against different database, which is equal to a market and
select from a table that has the same construction in each database but obviously different data.
What I need help with is how can I rewrite the dynamically question in RS so it works for multiple selected values.
Proposal to why we have the same table in multiple databases and does not take a table in a database or similar is not interesting in this case.
Best regards
/Semko
September 15, 2010 at 2:20 am
Hi, I have solved the problem.
Under the datasets properties tab "parameters" you can add expression.
In this case it looks like this:
=JOIN(Parameters!Objects.Value, "', '")
Attach the solution.
Best regards
/Semko
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply