Developing Dynamic SQL with Parameters - and Testing in SQL Query Analyzer.
In previous posts we looked at how Dynamic SQL is different from Aliased Views, but provides great deal of flexibility in coding. This flexibility comes at a slight disadvantage when it comes to building / testing. – Aliased Views can be written and tested within the SRS dataset query window. – Dynamic SQL hits some challenges when you try to test from within SRS – it can be done, but you spend a lot of time pasting in the values of variables each time you want to test/run the query.
Building and testing Dynamic SQL is easiest for me if I’m working inside the familiar interface of SQL Query Analyzer. – But In order to work in Query analyzer you need to declare and set the value of the variables that will ultimately be passed from CRM into the report. – Once you set up these inside your query window, you can build/test your Dynamic SQL to your heart’s content.
Here’s a simple example I worked on with another consultant to demonstrate how to test/pass variables into dynamic SQL.
The challenge was when he attempted to pass a GUID into the report, the query failed to run. The reason was at runtime, SQL was attempting to concatenate a GUID with a string and it was failing. It can be a little tricky - you essentially have to get the value of the parameter converted to a string 'inside' the SQL text that you're building.
To demonstrate what was happening, I opened a query analyzer window and added the following code/comments. – the first 2 groups of comments and code are only used in the query analyzer window to declare and set the values of the parameters that would normally be declared in the report parameters and set by CRM at run-time.
/*
These are for setting up the values in query analyzer for testing.
In SRS, they would be declared in the SRS report parameters list,
not in the dataset query.
*/
Declare @ContactID UniqueIdentifier
Declare @CRM_FilteredContact nvarchar(max)
/*
These are the parameters that would be passed to your query from the parent – I’m just setting them here so the SQL statement below has something to include when it attempts to execute.
*/
Set @ContactID = (select top 1 contactID from filteredContact)
Set @CRM_FilteredContact = 'Select X.* from FilteredContact as X'
/*
This is the query that would be in the report dataset
*/
DECLARE @SQL1 nvarchar(max)
Set @SQL1 =
'
SELECT * FROM (' + @CRM_FilteredContact + ') Contacts
where Contacts.contactid like ''' + CAST(@ContactId as nvarchar(40)) + ''' '
EXEC(@SQL1)
Note the ''' surrounding the parameter is 3 single quotes, it gets tricky sometimes because you have to use the 2 single quotes to insert one single quote inside the string and not mistakenly ending the string at that point. Once you’ve included the single quote into the string, the next (3rd) single quote ends the string so you can include the string value of the variable, then the next set of 3 quotes restarts the string, adds another single quote and finally yet another single quote finally ends the whole string. Whew.
-- Scott Sewell
Comments