CODEWITHSUNDEEP
sqlsql-serverreporting-services
user1552172
user1552172

Reputation: 676

Report Builder paramter dropdown to choose column name

I have a parameter with the available values as column names and a I am using that parameter in the sql query

 WHERE ((@ColumnName) BETWEEN (@StartDate) AND (@EndDate)) 
AND (schema.table.column_name LIKE (@Number))

When i try to run the report i get a cannot convert date/time from characters

enter image description here

If i remove the @ColumnName parameter the report runs fine but i want the ability to choose a column to search the date against.

Upvotes: 0

Views: 176

Answers (1)

M.Ali
M.Ali

Reputation: 69524

If you want pass Column name dynamically you will need to use a stored procedure to do this using dynamic sql something like this....

CREATE PROCEDURE Get_Data
  @ColumnName1  SYSNAME,
  @ColumnName2  SYSNAME,
  @StartDate    DATETIME,
  @EndDate      DATETIME,
  @Number       VARCHAR(100)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT * FROM TableName'
         + N'WHERE ' + QUOTENAME(@ColumnName1) + N' BETWEEN @StartDate AND @EndDate '
         + N' AND ' + QUOTENAME(@ColumnName2) + N' LIKE ''@Number'' '

 EXECUTE sp_executesql @SQL 
                      ,N'@StartDate DATETIME, @EndDate DATETIME, @Number VARCHAR(100)'
                      ,@StartDate
                      ,@EndDate
                      ,@Number

END

Upvotes: 1

Related Questions