• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5239
  • Last Modified:

SQL ISNOTNULL ??

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
            BEGIN
                  SET @delimitedColumns = 'patientFirstName|'
                  SET @delimitedValues = @oldPatientFirstName + '|'
            END

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

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

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

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

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

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

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

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

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

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

any help will be greatly appreciated.
-Russ
0
rbradberry
Asked:
rbradberry
  • 2
1 Solution
 
derekkrommCommented:
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.
0
 
rbradberryAuthor Commented:
hmm, this could work.  i am leaving this open for further discussion for rebuttal before i accept your answer.
0
 
dqmqCommented:
You can write your own function:

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

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

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

 
0
 
rbradberryAuthor Commented:
thanks, that worked perfectly
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now