Updated June 28, 2023
What is SSRS ISNULL()?
- An ISNULL() function in Microsoft’s SSRS returns a particular value if provided expression defines NULL. But when the expression is NOT NULL, this ISNULL() function will output the expression itself.
- The syntax structures as ISNULL (expr, value), where the parameter ‘expr’ denotes the expression, which is required for testing whether the expression is NULL, and the second parameter ‘value’ is required for returning the value of the expression parameter provided is NULL.
- SQL Server has several built-in dates, numeric, aggregate, and string functions, aiding in making our lives easier when queries are written. These functions in SQL are fragmented into various groups, and one of them is the system function SSRS ISNULL(), which we will discuss here.
How to Use SSRS ISNULL?
The SQL server function ISNULL() allows the user to return an alternative value as output when an expression equates to NULL.
Let us discuss the syntax as follows:
ISNULL(expr, replacement)The SSRS ISNULL() works to accept two arguments:
- Expr defines the expression being any type that is tested for NULL.
- Replacement defines the value that will result if the expression has a NULL value. Here, the replacement value should also be changeable to the value of the expression with provided type.
When the expression calculates to NULL, the ISNULL() function gives the replacement value. But before the value is returned, the SSRS ISNULL() function will change the replacement type to the expression type implicitly required whenever the two arguments have varied types. Like:
Exceptionally, when the expression holds not NULL value, then this ISNULL() function provides the value of that specified expression. Like:
In an expression, if there are many data types present, the SQL server will convert data types with lower precedence to those with higher precedence. Similarly, this works with SSRS ISNULL() function too. If this ISNULL() function cannot escalate the data type, then it will provide a result with an error message. Like, we have defined a variable i.e., @Person_ID having an integer type, since INT has higher precedence than the timestamp type as per MSDN data precedence table. It will be an error if you try substituting the null value with a timestamp. So, we will change the int value to the time value as follows:
Add SSRS ISNULL
The SSRS ISNULL() function can be applied everywhere, which the SQL syntax permits for the usage of a function. Still, the key usage instance for this function can be in the SELECT query list of a SQL query as soon as the user requires to change any NULL values that are being returned as output to a bit more evocative. It is important to know the NULL value used in an SQL server, so a NULL value denotes a special marker in the table column defining that the column does not have any existing data value. Also, the value of a NULL column is distinct from those columns possessing either an empty string or blank or 0 as the value in a table column. We can add the SSRS ISNULL() function in different ways while querying in SQL server as:
- Joining database tables by ISNULL function
- Usage of SSRS ISNULL() inside stored procedures
- Usage of SSRS ISNULL() function within a view definition.
- It is used in an SQL trigger for updating column values.
- Usage of SSRS ISNULL() within a computed table column.
- You can use the ISNULL() function with aggregate functions.
- It can substitute the NULL values in the table with a custom message.
- It can substitute a value present in an existing table column value.
- Usage of ISNULL() in an argument.
There is a difference between the SQL Server ISNULL() and only the ISNULL() function. The SQL server uses the ISNULL() function to substitute NULL values with a particular value, allowing for the recognition or identification of NULL values in a table.
SSRS ISNULL Examples
Let us illustrate with a few examples as elaborated below:
1. Using SSRS ISNULL() function through the numeric data type
Here, in this example, we will apply the ISNULL() function for returning the second argument of the function since the first argument defines to be NULL,
SELECT ISNULL(NULL, 50) result;The output is:
2. Using SSRS ISNULL() function through the character string
In this instance, we will apply the SSRS ISNULL() function for returning the string ‘Hi World’ since it is the initial argument of the function and does not have a NULL value,
SELECT ISNULL('Hi World', 'Hello') result;The output is:
3. Using SSRS ISNULL() function for substituting NULL values through meaningful values
In this instance, firstly, we will create a fresh table in the database providing names as separations which store athlete’s separations via ages by the following SQL code:
CREATE TABLE Separations
(Sep_id INT, Primary Key IDENTITY, MinAge INT Default 0, MaxAge INT);After creating the table, now we will enter a few data rows and columns into the separations table as follows:
INSERT INTO Separations (MinAge, MaxAge) VALUES (4, NULL), (10, NULL), (NULL, 20);In the third process, we will query the data record from this separations table as follows:
SELECT Sep_id, MinAge, MaxAge FROM Separations;The query will result in the output:
If the division process does not need any minimum age value, then the MinAge column will hold NULL. Likewise, if a division process does not need a maximum age value, the MaxAge column will also hold NULL.
At last, let us implement the ISNULL() function for converting NULL values available in the column MinAge to 0 and the same present in column MaxAge to 99 using the query as follows:
SELECT Sep_id, ISNULL(MinAge, 0) MinAge, ISNULL(MaxAge, 99) MaxAge FROM Separations;Conclusion
The server applies the SSRS ISNULL function to replace the NULL values with distinct values in expressions or the records in the database table. The Microsoft SQL Server uses built-in system functions to operate system processes and output information about settings or objects in the SQL server. Thus ISNULL() also helps to check and replace the value if it is NULL in a function call.
Recommended Articles
We hope that this EDUCBA information on “SSRS ISNULL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
