sql-servert-sqlstored-proceduresssms-17

Select statement return if there is a value and if there is no value

Here's my code of the SQL Server stored procedure:

SELECT NOTES as DeletionNote 
FROM STCRCHF_LOG 
WHERE STHTR_ =  @transferNo

IF ( @@ROWCOUNT = 0)

If there is data found, I just want to return the string of NOTES. Else if it doesn't have data, I just want to return an empty string or null.

Screenshot (executed stored procedure):

If there is data found. At my program on the web server side it gets the data.

If there is a data

If there is no data. In my program on the web server side it causes a NullReferenceException

if there is no data

Solution

  • If only a single record is possible then:

    SELECT COALESCE((SELECT NOTES FROM STCRCHF_LOG 
WHERE STHTR_ =  @transferNo), '') AS DeletionNote;

    If multiple records are possible then the following will ensure at least one row is returned:

    SELECT NOTES AS DeletionNote
FROM STCRCHF_LOG
WHERE STHTR_ =  @transferNo
UNION ALL SELECT '' /* or null if preferred */
WHERE NOT EXISTS (SELECT 1 FROM STCRCHF_LOG WHERE STHTR_ =  @transferNo);