I was helping someone who had an interesting data set. Their tables were coming from a vendor and they had to report off of those tables. The problem was that the tables were all named and logically split by MMYYYY. This meant that if they wanted to query data between a date range, they would need to maintain views daily or get creative in SSRS.
Now this option can be used for many things, you just need to tweak the parameters a little. I’m showing you conceptually how to use the template to execute a dynamic SQL Script that populate a data set from a user selecting drop downs in a report. The parameters are tables that are named to match the date range of data contained within them in this example.
DECLARE @Table VARCHAR(250);
DECLARE @TableTwo VARCHAR(250);
DECLARE @ReturnStatus INT;
SET @Table = @ParameterOne;
SET @TableTwo = @ParameterTwo;
IF OBJECT_ID('TempDB..#HoldRecords') IS NOT NULL
DROP TABLE #HoldRecords;
CREATE TABLE #HoldRecords
(TestExample INT,
TestExampletwo VARCHAR(50)
);
INSERT INTO #HoldRecords
EXECUTE @ReturnStatus = [dbo].[StoredProcedureContainingDynamicSQL]
@Table,
@TableTwo;
SELECT TestExample,
TestExampletwo
FROM #HoldRecords;
Here is an explanation of the stored procedure:
DECLARE @Table varchar(250); DECLARE @TableTwo varchar(250); DECLARE @ReturnStatus Int;
At the top, I am declaring the variables we need. The @Table and @TableTwo would be populated by the date drop downs the user selects. Imagine the user picks May 2018 as the beginning date and then they pick June 2018 in the end date. You would know the @table would be dbo.052018 and the @tabletwo would be dbo.062018, so the parameter converts the dates selected from the drop down into the table names in the database.
set @Table = @ParameterOne; set @TableTwo = @ParameterTwo;
We are then setting those variables equal to the parameters. You do this in the code or procedure, it will be mapped after evaluation in the parameter menu towards the left pane.
IF OBJECT_ID('TempDB..#HoldRecords') IS NOT NULL
DROP TABLE #HoldRecords;
Create Table #HoldRecords (TestExample int, TestExampletwo varchar(50));
We need a table to hold results from, so we are going to test for a temp table and drop a temp table so it will run every time without problems. I’ve declared and created a test example one, you will need to alter it to fit your needs.
INSERT INTO #HoldRecords EXECUTE @ReturnStatus = [dbo].[StoredProcedureContainingDynamicSQL] @Table ,@TableTwo;
We are then executing a stored procedure. Here’s the purpose of the return status, it will give you additional benefit if you incorporate it with error handling.
@ReturnStatus
Is an optional integer variable that stores the return status of a module. This variable must be declared in the batch, stored procedure, or function before it is used in an EXECUTE statement. MSDN
We are executing the stored procedure and passing it the variables @Table (dbo.052018) and @TableTwo (dbo.062018). Your procedure should contain dynamic SQL that will query the tables using the parameters passed. Something to add is you may want to consider a variable for a where clause for each table that would contain the day the user selected. You can simply declare another variable like @DayOne and @DayTwo and set them to the parameters as we did with the tables. You’ll pass it through to the stored procedure the same way.
Select
TestExample
,TestExampletwo
FROM #HoldRecords;
At the end, we are returning the results from the temp table. To summarize the process, you basically have to pass parameters to dynamically generate a query that will stage records that you can later select from.
