What is SSRS ISNULL()?
- Basically, an ISNULL() function in Microsoft’s SSRS is used to return a particular value if provided expression defines NULL. But in the case when the expression is NOT NULL, then 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 if the expression parameter provided is NULL.
- There are several built-in dates, numeric, aggregate, and string functions in SQL Server, aiding in making our lives easier when queries are written. These functions in SQL are fragmented into various groups, and one of them is this system function SSRS ISNULL() that 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 is to be the result if the expression has a NULL value. Here, the replacement value should be changeable to the value of the expression with provided type also.
- When the expression calculates to NULL, then 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, then the SQL server will convert data types with lower precedence to the data types 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 integer type, since INT has higher precedence than the timestamp type as per MSDN data precedence table. If you try substituting the null value with a timestamp, it will be an error. So, we will change the int value to the time value as follows:
Add SSRS isnull
The SSRS ISNULL() function is able to be applied everywhere, which the SQL syntax permits for the usage of a function, but 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.
- Can be used ISNULL() with the aggregate functions.
- Can be used to substitute the NULL values in the table with a custom message.
- Can be used to substitute a value present in an existing table column values.
- Usage of ISNULL() in an argument.
There is a difference between the SQL Server ISNULL() and only the ISNULL() function. The ISNULL() is used to recognize or find out the NULL values present in a table, but the SQL server ISNULL() is used to substitute the NULL values with a particular value.
SSRS isnull Examples
Let us illustrate with a few examples as elaborated below:
- 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;code>The output is:
- 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:
- 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 the output as:
If the division process does not need any minimum age value, then the column named MinAge will hold NULL. Likewise, if a division process does not need a maximum age value, then the column named MaxAge 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
- SSRS ISNULL function is applied in the server to actually replace the NULL values with distinct values in expressions or the records in the database table.
- Since the system functions built in in the Microsoft SQL Server are applied 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
This is a guide to SSRS ISNULL. Here we discuss What is SSRS ISNULL(), How to use SSRS isnull, and examples with code implementation. You may also have a look at the following articles to learn more –
