Posted on 2007-07-20
Last Modified: 2012-05-05
so i have had good experiences with the sql statement ISNULL(eval, ret)  statement.  now i am looking for something like ISNOTNULL(eval, ret), basically i have this code that I want to seriously condense:

      IF @patientFirstName IS NOT NULL
                  SET @delimitedColumns = 'patientFirstName|'
                  SET @delimitedValues = @oldPatientFirstName + '|'

      IF @patientMiddleName IS NOT NULL
                  SET @delimitedColumns = @delimitedColumns + 'patientMiddleName|'
                  SET @delimitedValues = @delimitedValues + @oldPatientMiddleName + '|'

      IF @patientLastName IS NOT NULL
                  SET @delimitedColumns = @delimitedColumns + 'patientLastName|'
                  SET @delimitedValues = @delimitedValues + @oldPatientLastName + '|'

      IF @patientSocialSecurityNumber IS NOT NULL
                  SET @delimitedColumns = @delimitedColumns + 'patientSocialSecurityNumber|'
                  SET @delimitedValues = @delimitedValues + @oldPatientSocialSecurityNumber + '|'

      IF @patientAddress1 IS NOT NULL
                  SET @delimitedColumns = @delimitedColumns + 'patientAddress1|'
                  SET @delimitedValues = @delimitedValues + @oldPatientAddress1 + '|'

      IF @patientAddress2 IS NOT NULL
                  SET @delimitedColumns = @delimitedColumns + 'patientAddress2|'
                  SET @delimitedValues = @delimitedValues + @oldPatientAddress2 + '|'

      IF @patientAddress3 IS NOT NULL
                  SET @delimitedColumns = @delimitedColumns + 'patientAddress3|'
                  SET @delimitedValues = @delimitedValues + @oldPatientAddress3+ '|'

      IF @patientCity IS NOT NULL
                  SET @delimitedColumns = @delimitedColumns + 'patientCity|'
                  SET @delimitedValues = @delimitedValues + @oldPatientCity + '|'

      IF @patientState IS NOT NULL
                  SET @delimitedColumns = @delimitedColumns + 'patientState|'
                  SET @delimitedValues = @delimitedValues + @oldPatientState + '|'

      IF @patientZip IS NOT NULL
                  SET @delimitedColumns = @delimitedColumns + 'patientZip|'
                  SET @delimitedValues = @delimitedValues + CAST(@oldPatientZip AS VARCHAR) + '|'

      IF @patientCountry IS NOT NULL
                  SET @delimitedColumns = @delimitedColumns + 'patientCountry'
                  SET @delimitedValues = @delimitedValues + @oldPatientCountry

any help will be greatly appreciated.
Question by:rbradberry
    LVL 15

    Expert Comment

    Unfortunately there's no existing function like isnull.

    You could cut down on the if statements by doing this:

    SELECT @delimitedColumns = 'patientFirstName|', @delimitedValues = @oldPatientFirstName + '|'
    WHERE @patientFirstName is not null

    At least it gets each IF block down to 1 select line that way.
    LVL 3

    Author Comment

    hmm, this could work.  i am leaving this open for further discussion for rebuttal before i accept your answer.
    LVL 42

    Accepted Solution

    You can write your own function:

    create function dbo.IfNotNull (@eval varchar(1000),  @ret varchar(1000))
    returns varchar(1000)
    --evaluates first argument.  If null returns empty string, otherwise
    --returns second argument.
    if @eval is null
       return ('')
    return (@notnull)

    SET @delimitedColumns=
    + dbo.IfNotNull(@oldPatientMiddleName,'patientMiddleName|')
    + dbo.IfNotNull(@oldPatientLastName,'patientLastName|')

    SET @delimtedValues=
    + dbo.IfNotNull(@oldPatientMiddleName,@oldPatientMiddleName+'|')
    + dbo.IfNotNull(@oldPatientLastName,@oldPatientLastName+'|')

    LVL 3

    Author Comment

    thanks, that worked perfectly

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now