Passing table name as a parameter in SSRS dataset definition

Question

OK so I'm trying to add a dataset that can dynamically create a table name and accept several other parameters to retrieve the desired output. The following is my dataset query -

declare @cmd nvarchar(max)

select @cmd = 'select at.LocationID, at.LocationName as Location, er.company_name as Company, er.BranchShortCode as Branch, er.Department_Name as Department , 
at.UniqueEmpID, er.EmpName , er.Designation_Name ,convert(date,at.AttendanceDate) as AttendanceDate, at.StatusCode, 
substring(convert(varchar(20),convert(time,at.InTime)),1,8) as InTime, substring(convert(varchar(20),convert(time,at.OutTime)),1,8) as OutTime, 
at.ShiftFName as ShiftName, at.BeginTime as ShiftStartTime, at.EndTime  as ShiftEndTime
from ' + @TableName + ' as at inner join EmployeeAllDetail_Rpt er on at.UniqueEmpID = er.UniqueEmpID
where LocationID in (@LocationID) and er.company_id in (@Company_ID) 
and er.branch_id in (@Branch_ID) and er.dept_id in (@dept_ID) 
and convert(date,at.AttendanceDate) between convert(date,@FromDate) and convert(date,@ToDate)
and DATEDIFF(day,convert(date,@FromDate,103),convert(date,@ToDate,103)) <= 7
order by at.LocationID, er.company_name, er.BranchShortCode, er.Department_Name, at.AttendanceDate'

exec (@cmd)

Now @TableName is a variable internal parameter based on @FromDate, wherein it extracts month and year to form the table name like say "Attendance_072020".

@LocationID/@CompanyID/@Branch_ID/@Dept_ID & @FromDate/@ToDate are several other parameters.

When I try to refresh fields using above query I get a "@LocationID" must be declared error.

Would greatly appreciate if anyone can help or guide me in the right direction. If this can't be achieved using SSRS I would be really thankful if anyone can briefly show how I could design a stored procedure to accomplish the same.

UPDATE: This is my code block now which I'm using in my report -

DECLARE @TableNamesql NVARCHAR(MAX),
        @SQL NVARCHAR(MAX),
        @Params NVARCHAR(MAX),
        @TableName NVARCHAR(128),
        @FromDate date,
        @ToDate date,
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10),
        @LocationID int,
        @Company_ID int,
        @Branch_ID int,
        @dept_ID int;

SET @Params = N'@LocationID int, @Company_ID int, @Branch_ID int, @dept_ID int, @FromDate date, @ToDate date';
--PRINT @Params


SET @TableNamesql = N'select @TableName = Tablename from (' + @CRLF +
                    N'select case when datepart(month,@FromDate) < 10 then ' + '''hrms_Attendance_0''' + ' + cast(datepart(month,@FromDate) as varchar) + ' + @CRLF +
                    N'cast(datepart(year,@FromDate) as varchar) ' + @CRLF +
                    N'else ' + '''hrms_Attendance_''' + ' + cast(datepart(month,@FromDate) as varchar) + cast(datepart(year,@FromDate) as varchar) end as Tablename)t';
--PRINT @TableNamesql

EXEC sys.sp_executesql @TableNamesql,N'@FromDate DATE, @TableName nvarchar(128) OUTPUT',@FromDate = @FromDate, @TableName = @TableName OUTPUT
--PRINT @TableName

SET @SQL = N'SELECT at.LocationID,' + @CRLF +
           N'       at.LocationName AS Location,' + @CRLF +
           N'       er.company_name AS Company,' + @CRLF +
           N'       er.BranchShortCode AS Branch,' + @CRLF +
           N'       er.Department_Name AS Department,' + @CRLF +
           N'       at.UniqueEmpID,' + @CRLF +
           N'       er.EmpName,' + @CRLF +
           N'       er.Designation_Name,' + @CRLF +
           N'       CONVERT(date, at.AttendanceDate) AS AttendanceDate,' + @CRLF +
           N'       at.StatusCode,' + @CRLF +
           N'       SUBSTRING(CONVERT(varchar(20), CONVERT(time, at.InTime)), 1, 8) AS InTime,' + @CRLF +
           N'       SUBSTRING(CONVERT(varchar(20), CONVERT(time, at.OutTime)), 1, 8) AS OutTime,' + @CRLF +
           N'       at.ShiftFName AS ShiftName,' + @CRLF +
           N'       at.BeginTime AS ShiftStartTime,' + @CRLF +
           N'       at.EndTime AS ShiftEndTime' + @CRLF +
           N'FROM dbo.' + QUOTENAME(@TableName) + N' at' + @CRLF + 
           N'  INNER JOIN EmployeeAllDetail_Rpt er ON at.UniqueEmpID = er.UniqueEmpID' + @CRLF +
           N'WHERE LocationID IN (@LocationID)' + @CRLF +
           N'  AND er.company_id IN (@Company_ID)' + @CRLF +
           N'  AND er.branch_id IN (@Branch_ID)' + @CRLF +
           N'  AND er.dept_id IN (@dept_ID)' + @CRLF +
           N'  AND at.AttendanceDate >= @FromDate' + @CRLF +
           N'  AND at.AttendanceDate < DATEADD(DAY, 1, @ToDate)' + @CRLF + 
           N'  AND DATEDIFF(DAY, @FromDate, @ToDate) <= 7' + @CRLF +
           N'ORDER BY at.LocationID,' + @CRLF +
           N'         er.company_name,' + @CRLF +
           N'         er.BranchShortCode,' + @CRLF +
           N'         er.Department_Name,' + @CRLF +
           N'         er.UniqueEmpID,' + @CRLF +
           N'         at.AttendanceDate;';
--PRINT @SQL

EXEC sys.sp_executesql @SQL, @Params, @LocationID, @Company_ID, @Branch_ID, @dept_ID, @FromDate, @ToDate;
'''

The fact you want to do this suggests have you have overall design flaw. — Larnu

Larnu Larnu · Accepted Answer · 2020-07-20T12:47:19

As I mention in the comments, the fact that you need a query like this suggests you have a design flaw. If you have multiple tables all with the same definition, then this likely means you have a denormalised design and need to really be fixing that.

As for answering the question, this is why you shouldn't be using EXEC (@SQL); you can't parametrise the statement. Variables/parameters only exist in the scope they are declared in. They will not exist in the dynamic statement, as that is a separate scope. This is why you should always be using a parametrised call to sys.sp_executesql.

You also need to safely inject the value of your dynamic object. This gives you something like the below (note there are several comments in this code you should be checking and changing as needed):

/*
Other pamaraters will have been previously declared, however, I assume the following datatypes:
@TableName sysname
@LocationID int
@Company_ID int
@Branch_ID int
@dept_ID int
@FromDate date
@ToDate date
*/

DECLARE @SQL nvarchar(MAX),
        @Params nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

--I may have missed some out here, and I have GUESSED data types
SET @Params = N'@LocationID int, @Company_ID int, @Branch_ID int, @dept_ID int, @FromDate date, @ToDate date';

SET @SQL = N'SELECT at.LocationID,' + @CRLF +
           N'       at.LocationName AS Location,' + @CRLF +
           N'       er.company_name AS Company,' + @CRLF +
           N'       er.BranchShortCode AS Branch,' + @CRLF +
           N'       er.Department_Name AS Department,' + @CRLF +
           N'       at.UniqueEmpID,' + @CRLF +
           N'       er.EmpName,' + @CRLF +
           N'       er.Designation_Name,' + @CRLF +
           N'       CONVERT(date, at.AttendanceDate) AS AttendanceDate,' + @CRLF +
           N'       at.StatusCode,' + @CRLF +
           N'       SUBSTRING(CONVERT(varchar(20), CONVERT(time, at.InTime)), 1, 8) AS InTime,' + @CRLF +
           N'       SUBSTRING(CONVERT(varchar(20), CONVERT(time, at.OutTime)), 1, 8) AS OutTime,' + @CRLF +
           N'       at.ShiftFName AS ShiftName,' + @CRLF +
           N'       at.BeginTime AS ShiftStartTime,' + @CRLF +
           N'       at.EndTime AS ShiftEndTime' + @CRLF +
           N'FROM dbo.' + QUOTENAME(@TableName) + N' at' + @CRLF + --Guessed Schema
           N'     INNER JOIN EmployeeAllDetail_Rpt er ON at.UniqueEmpID = er.UniqueEmpID' + @CRLF +
           N'WHERE LocationID IN (@LocationID)' + @CRLF +
           N'  AND er.company_id IN (@Company_ID)' + @CRLF +
           N'  AND er.branch_id IN (@Branch_ID)' + @CRLF +
           N'  AND er.dept_id IN (@dept_ID)' + @CRLF +
           --Considering I have assumed that @FromDate and @ToDate are dates, there is no need to convert them.
           --You should also be using >= and < logic here, so I assume what you actually want is this:
           N'  AND at.AttendanceDate >= @FromDate' + @CRLF +
           N'  AND at.AttendanceDate < DATEADD(DAY, 1, @ToDate)' + @CRLF + 
           --This seems an odd clause to have in the WHERE.
           --Again, considering I have assumed that @FromDate and @ToDate are dates, there is no need to convert them.
           N'  AND DATEDIFF(DAY, @FromDate, @ToDate) <= 7' + @CRLF +
           N'ORDER BY at.LocationID,' + @CRLF +
           N'         er.company_name,' + @CRLF +
           N'         er.BranchShortCode,' + @CRLF +
           N'         er.Department_Name,' + @CRLF +
           N'         at.AttendanceDate;';

--PRINT @SQL; --Your best friend.

EXEC sys.sp_executesql @SQL, @Params, @LocationID, @Company_ID, @Branch_ID, @dept_ID, @FromDate, @ToDate; --If I missed any parameters, you need to add these

You can use your "best friend" to aid in debugging.

Note, I highly recommend using whitespace and line breaks as well; as I have done above. Your dynamic statement was impossible to read in the block you posted. Just because a statement is dynamic doesn't mean you should throw formatting out the window.

I expand more on dynamic SQL and good practices in my article Dos and Don'ts of Dynamic SQL.

Passing table name as a parameter in SSRS dataset definition

1 Answers