October 27, 2017 at 8:31 am
Dear all,
I have created in my dataset a parameter named @pDB
and I have build the query below in the dataset:
SELECT SPECIFIC_SCHEMA + ' - ' + ROUTINE_NAME
FROM @pDB.information_schema.routines
WHERE routine_type = 'PROCEDURE' AND ROUTINE_NAME IN
(SELECT DISTINCT (replace(replace(spname, '[', ''), ']', ''))
FROM @pDB.[Logging].[SPExecutions])
ORDER BY SPECIFIC_SCHEMA + ' - ' + ROUTINE_NAME
When I try to save the dataset it always prompts me to put value for this parameter. Which I do and then it says bad sintax nera "."
This is what I am putting:
Core_PRO
Can someone help?
Thank you
October 27, 2017 at 8:52 am
I don't think you can dynamically use the parameter like that.
You'll have to build your sql statement dynamically like this:
DECLARE @pDB AS VARCHAR(10)
DECLARE @sql AS VARCHAR(500)
SET @pDB = 'Core_PRO'
SELECT @sql =
'SELECT SPECIFIC_SCHEMA + '' - '' + ROUTINE_NAME
FROM ' + @pDB + '.information_schema.routines
WHERE routine_type = ''PROCEDURE'' AND ROUTINE_NAME IN
(SELECT DISTINCT (replace(replace(spname, ''['', ''''), '']'', ''''))
FROM ' + @pDB + '.[Logging].[SPExecutions])
ORDER BY SPECIFIC_SCHEMA + '' - '' + ROUTINE_NAME'
EXEC (@sql)
I would also encapsulate the logic in a stored procedure and pass the parameter to it.
October 27, 2017 at 8:59 am
yap. thanks
October 27, 2017 at 9:02 am
That's because what you're doing there is incorrect syntax. Your statement there is to return the data from the database @pDB, as in, the database is called "@pDB". You can't use a variable or parameter to replace an object's name.
One way is to use dynamic SQL. For example:DECLARE @pDB nvarchar(50) = 'Sandbox'; --In SSRS you won't need to declare this
DECLARE @sql nvarchar(4000);
SET @sql = N'SELECT name from sys.tables;';
EXEC sp_executesql @sql;
The problem with this, however, is that you could easily inject something into it. I'd suggest looking into SQL injection, however, you could avoid so by checking the DB is valid: For example:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 27, 2017 at 9:04 am
Paulo de Jesus - Friday, October 27, 2017 8:52 AMI don't think you can dynamically use the parameter like that.
You'll have to build your sql statement dynamically like this:
DECLARE @pDB AS VARCHAR(10)
DECLARE @sql AS VARCHAR(500)
SET @pDB = 'Core_PRO'
SELECT @sql =
'SELECT SPECIFIC_SCHEMA + '' - '' + ROUTINE_NAME
FROM ' + @pDB + '.information_schema.routines
WHERE routine_type = ''PROCEDURE'' AND ROUTINE_NAME IN
(SELECT DISTINCT (replace(replace(spname, ''['', ''''), '']'', ''''))
FROM ' + @pDB + '.[Logging].[SPExecutions])
ORDER BY SPECIFIC_SCHEMA + '' - '' + ROUTINE_NAME'EXEC (@sql)
I would also encapsulate the logic in a stored procedure and pass the parameter to it.
Just to reiterate, be careful with this solution. Although Paulo uses a varchar(10), I imagine some of your database have a name larger than 10 characters, and, more importantly, I expect your parameter will be larger than a varchar(10). Meaning that someone could easily inject with a larger parameter size.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply