CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresssms-17
Raven Supilanas
Raven Supilanas

Reputation: 81

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

Upvotes: 3

Views: 684

Answers (3)

Dale K
Dale K

Reputation: 27202

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);

Upvotes: 4

Rahul Neekhra
Rahul Neekhra

Reputation: 810

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.

Upvotes: 0

Alan Burstein
Alan Burstein

Reputation: 7918

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;

Upvotes: 1

Related Questions