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

SELECT Col1Name
    ,Col2Name
    ,Col3Name
FROM dbo.TABLE
WHERE Region = @RegionName

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

SELECT Col1Name
    ,Col2Name
    ,Col3Name
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)
AS
BEGIN 
Select [SalePersonFName]
           ,[SalePersonLName]
           ,[ProductName]
           ,[ItemsSold]
           ,[SoldPrice]
           ,[SoldDate]
           ,[City]
           ,[State]
           ,[Country]
           ,[Region] 
           from dbo.TotalSale
           where Region=@Region
           END