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

Question

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 no data. In my program on the web server side it causes a NullReferenceException

Answer 1

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)

Answer 2

Another way which I like is to use a dummy value and OUTER APPLY like so.

-- sample data
DECLARE @table TABLE (someId INT IDENTITY, Col1 VARCHAR(100));
INSERT @table(Col1) VALUES ('record 1'),('record 2');

DECLARE @id INT = 11;

SELECT f.Col1
FROM   (VALUES(NULL)) AS dummy(x)
OUTER APPLY 
(
  SELECT t.Col1
  FROM   @table AS t
  WHERE t.someId = @id
) AS f;

Answer 3

Check If(DataTable.Rows.Count >0) check at your web programming level to avoid NullReferenceException. Based on the condition you can make the decision what to do with the further logic at web program.

It is always wise idea to handle such exception from programming level. Think of scenario where somebody else make changes in SQL query without knowing the effect of web usages of the code.