How to Deal with SQL Server Stored Procedures with Optional Parameters
Environment
Product | Progress® Telerik® Reporting |
Description
If you don't provide a value for an optional SQL parameter, it actually receives NULL, which seems to override the default value. Even if you delete the parameter from the SqlDataSource, the Null value would be displayed for the optional parameter in the SQL Server Profiler.
Suggested Workarounds
Alter the Stored Procedure
You may include a conditional statement in the Stored Procedure that checks for the Null value for the optional parameters and sets it to the default value when Null.
This works in the following cases:
- when the parameter is set to Null
- when you pass an empty value for the parameter
- when you delete the parameter from the SqlDataSource
Here is the sample code for the Stored Procedure:
ALTER PROCEDURE [dbo].[GetForGrade]
@grade int = 10
AS
BEGIN
SET NOCOUNT ON;
IF @grade IS NULL
BEGIN
SET @grade = 10
END
SELECT [SchoolYear]
,[School]
,[LastName]
,[FirstName]
,[Grade]
FROM [dbo].[TestTable1]
Where [Grade]=@grade
END
Alter the Value Passed to the Stored Procedure Optional Parameter
If you cannot alter the Stored Procedure, you need to assure that the value passed to the SqlDataSource parameter is not Null. For example, you may use the following Expression when setting its value. It passes the hard-coded default SqlDataSource parameter value when the Report Parameter 'grade' is Null:
= Parameters.grade.Value ?? 9