Nimrod Yanai Nimrod Yanai - 7 months ago 5
SQL Question

How to check if cell is null by column name in SQL?

I am trying to build a stored procedure in SQL.

I have the following syntax:

CREATE PROCEDURE [dbo].[getAllCitizens]
@NoInfo as nvarchar(50) = "No contact information inserted."
AS
BEGIN
SET NOCOUNT ON;
IF ctznPhone != null
BEGIN
SELECT ctznTz, ctznLname, ctznFname, ctznPhone
FROM tblCitizens
Where ctznLivestat=1
END
ELSE
BEGIN
IF ctznEml != null
BEGIN
SELECT ctznTz, ctznLname, ctznFname, ctznEml
FROM tblCitizens
Where ctznLivestat=1
END
ELSE
BEGIN
SELECT ctznTz, ctznLname, ctznFname, @NoInfo
FROM tblCitizens
Where ctznLivestat=1
END
END
END


How do I tell the procedure in which table the columns
ctznPhone
and
ctznEml
are?

I have tried
dbo.tablename.columname
and various attempts with () and [] but it doesn't seem to work.

Eventually, this procedure will be called upon by a C# datareader.
What I am trying to do is build a procedure that, when called upon, will return the selected cells from a row, depending on the information in specific cells:
If ctznPhone is not null, then I want to get it. If it's null, I want to get ctznEml instead. If THAT's null as well, I want to get back a message that no contact information is available (a string which I have placed in
@NoInfo
).

Am I going about this the right way?

Answer

You could use COALESCE:

COALESCE ( expression [ ,...n ] )

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

CREATE PROCEDURE [dbo].[getAllCitizens]
    @NoInfo as nvarchar(50) = "No contact information inserted."
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ctznTz, ctznLname, ctznFname,
         COALESCE(ctznPhone, ctznEml, @NoInfo) AS contactInfo
  FROM tblCitizens
  WHERE ctznLivestat=1;

END