CODEWITHSUNDEEP
sqlreporting-services
deepti
deepti

Reputation: 729

Very simple but still complicated just want to Add parameter to SSRS with Dynamic data

I am creating a SSRs report, I just took 2 parameters one is salary & and other >, <, or = operators, but that is dynamic what user selects in drop down that comparison option should come.

My code is as follows in SSRs report

DECLARE  @PARAM VARCHAR(10)
DECLARE @sqlcommand varchar(1000)

-- DECLARE @salary varchar(1000)
-- SET @salary='40000'

SET @PARAM='<'

SET @sqlcommand = 'SELECT A.EmpID, A.FirstName, A.LastName, C.Address, C.City, C.PostalCode, C.Country, C.Region, B.Salary FROM ((Employee A INNER JOIN Salary B ON A.EmpID = B.EmpID) INNER JOIN  Address C ON A.EmpID = C.EmpID) WHERE  B.salary ' + @PARAM + ''+ CONVERT(INT, CONVERT(VARCHAR(12), @Salary))

PRINT @sqlcommand

EXEC (@sqlcommand)

So for me one salary is parameter and other comparison operator when I run I get this error:

Microsoft Report Builder

An error occurred while executing the query.

Conversion failed when converting the varchar value 'Select A.EmpID, A.FirstName, A.LastName, C.Address, C.City, C.PostalCode, C.Country, C.Region, B.Salary FROM ((Employee A INNER JOIN Salary B ON A.EmpID = B.EmpID) INNER JOIN Address C ON A.EmpID = C.EmpID) where B.salary <' to data type int.

Can you please help? I know it's simple but I am struggling to concatenate since days and no luck.

Upvotes: 0

Views: 53

Answers (1)

Simon
Simon

Reputation: 537

You just need to CONVERT your salary to VARCHAR not INT, give this a go;

    Use MytestDB

DECLARE  @PARAM VARCHAR(10)

DECLARE @sqlcommand varchar(1000)

declare @salary varchar(1000)

set @salary='40000'

SET @PARAM='<'

SET @sqlcommand='Select A.EmpID, A.FirstName, A.LastName, C.Address, C.City, C.PostalCode, C.Country, C.Region, B.Salary FROM ((Employee A INNER JOIN Salary B ON A.EmpID = B.EmpID) INNER JOIN  Address C ON A.EmpID = C.EmpID) WHERE  B.salary ' + @PARAM + CONVERT(VARCHAR(12), @Salary)

SELECT @sqlcommand

Upvotes: 1

Related Questions