SQL Server - Access TEMP table created dynamically

Sql server provides an option to create the query dynamically and execute it as below.

DECLARE @SqlStmt VARCHAR(2000)

SET @SqlStmt='SELECT * FROM HumanResources.Employee'

EXEC (@SqlStmt)

Even temporary table also can be created dynamically as follows.

DECLARE @SqlStmt VARCHAR(2000)

SET @SqlStmt='CREATE TABLE #Class(ID INT, Name VARCHAR(200))'

EXEC (@SqlStmt)

But when you insert or Select statements on #Class table immediately next to above query, Sql Server throws an error “Invalid object name '#Class'.”

To Avoid that you have to write insert/select statements also inside dynamic query string.
As #Class temporary table created dynamically that ends it's scope after EXEC statement. So you should write any further queries inside dynamic query string itself.

SSRS - Showing Multi valued Parameter selected values as CSV in Report

SSRS provides flexibility to add a parameter as single valued or multi valued parameter for a report. We may need to pass this parameter selected values to a query or need to show it on the report.

Here the requirement is to show the multi valued parameter values as comma separated values (CSV) in report. This let end your to see the selected values in the report.

SSRS provided built in function JOIN, use this to combine the selected values.

Example:

In report we have a Parameter “Country” which is multi valued. We can write an expression like below for a textbox value to combine the values with “Spaces”.

=JOIN(Parameters!Country.Value)

 Select the values from country parameter and click on view report button, then you can see the combined values in report

If you want to show the values as comma separated, then you should pass the delimited as second parameter to JOIN function as below.

=JOIN(Parameters!Country.Value,", ")

Like this you can pass &, |, ;  characters to join function to show the values as separated by given delimited on the report.