You can write code like this
declare sSQL varchar(500)
declare sWhere varchar(200)
set sSQL = "Select * from mytable"
if lenght(@.Parameter1) > 0
begin
sWhere = " Where mycolume = '" + @.Parameter2 + "'"
end
sSQL = sSQL + sWhere
EXEC (sSQL)
|||Thanks for the reply, but I need to be clearer with the question that I am asking.
I have written code to construct the piece of SQL that I need to append - it retrieves conditions from the database which define some additional filtering for security.
What I need to know is if it is possible at runtime to trap the SQL statement stored in the <CommandText> element of the RDL for the report that is running - append my additional segment of SQL just before it gets processed. Bear in mind that a Report designed may have several SQL statements from individual parts of the overall report - a data set tp provide values for parameters, a dataset for each section on a report, chart and matrix for example.
Should I be looking at doing it via a Data Processing extension, therefore trapping any report that that is run via SSRS?
