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)