Geek Answers Handbook

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

I am trying to create 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 to specify the procedure in which the ctznPhoneand columns are indicated ctznEml?

I have tried dbo.tablename.columnamevarious attempts with ()and [], but it does not work.

In the end, this procedure will be called by C # datareader. What I'm trying to do is create a procedure that, when called, will return the selected cells from the row, depending on the information in the specific cells:

  • If ctznPhonenot null, I want to get it.
  • If it is zero, I want to get it instead ctznEml.
  • THAT , , (, @NoInfo).

?

+4
1

COALESCE:

COALESCE ( [,... n])

, 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
+6

More articles:


All Articles