SSRS Interview Questions and Answers - How would you write TSQL Query or Stored Procedure with Single Value Parameter in SSRS Report

Parameters are used to filter the data in report. Single value parameter is parameter which can accept only one value at a time.

To create your report with single value parameter , in Where clause you will be using "=" sign and Parameter name.

Let's say we want to create RegionName Parameter, we can write query as below

WHERE Region = @RegionName

Let's say if you would like to create two single value parameters RegionName and CountryName in your report

FROM dbo.MyTableName
WHERE Region = @RegionName
    AND Country = @CountryName

You can also use the Stored Procedure for your report with Single value parameter/s. The where clause will be using "=" and then Parameter name as shown below. In below Stored Procedure we are only using @Region Parameter. You can create Stored Procedure with Multiple Single Value parameters if required by adding conditions in Where clause as we did in above query for Region and Country.

Create procedure dbo.sp_SalesTotal
@Region VARCHAR(100)
Select [SalePersonFName]
           from dbo.TotalSale
           where Region=@Region