Passing SSRS parameter into Query as parameter

Question

Recently there is one situation in SSRS , Where I am not able to figure it out correct logic for my requirement ,can someone please help me out what is the problem in my logic.

I am trying to pass the parameter value into SSRS dataset query. Please refer the query below. When I am trying to execute the query manually it runs but when I pass parameter in it, not providing me the desired output please refer the screenshot where I executed the query in SSMS.

DataSet Query

---

Select * from(
SELECT     @Defaulter_Type AS Priority_Count, Priority
FROM         Quality_Portal_Data_Fact_Graph)  temp

---

SQL Query

---

SELECT [NEW_PID_2],[Priority_1] 
  FROM [OneWindowPRD].[dbo].[Quality_Portal_Data_Fact_Graph]

---

Although, I figure it out one thing that when I am trying to pass parameter in it. It is passing the value with single cort because of which it is showing data is below mentioned form.

Can someone please suggest What will I do to remove these single cort. Please suggest any other method or logic to select the column of the query which is given by user

I already tried SUBSTRING its not working. Please refer the screenshot

SELECT QUOTENAME(SUBSTRING(@Defaulter_Type, 2, LEN(@Defaulter_Type) - 2)) AS Count_Priority FROM Quality_Portal_Data_Fact_Graph

The parameter I am trying to pass in the query is the column_name which is passed by user, it is single value column. Please refer screenshot is someone have any suggestion or other approach

Answer 1

Are you trying to do something like:

DECLARE @sql varchar(1000) =
'
Select * from(
SELECT     ' + QUOTENAME(@Defaulter_Type) + ' AS Priority_Count, Priority
FROM         Quality_Portal_Data_Fact_Graph)  temp
'

EXEC (@sql)

This uses dynamic SQL to build a query string using your parameter as a column name. It then executes that query to produce your result set.

Your query as it currently is will just select the value of @Defaulter_Type as the value of the field Priority_Count for every row.

Additionally, if your parameter is encased in quotes that you want to remove, use:

DECLARE @sql varchar(1000) =
'
Select * from(
SELECT     ' + QUOTENAME(SUBSTRING(@Defaulter_Type, 2, LEN(@Defaulter_Type) - 2)) + ' AS Priority_Count, Priority
FROM         Quality_Portal_Data_Fact_Graph)  temp
'

EXEC (@sql)