SQL string from 3 cases

Hi,
It seems you are getting null value in some of your field
so use

SET             @Output =       IsNull(@policyNumber, '') + ' , ' + isNull(@IssuerName, '') + ' , ' + IsNull(@CADate, '')

IJZ:
That is correct...but as this is a legal document...the person needs to get the message back on exactly what is missing

So if everything is fine...they get back the Output in my stored procedure

If something all somethings are null...they get back the list so they can go fix what needs to be changed

ALTER FUNCTION [dbo].[uf_ucc_structured_WithCA] (@DealID int)

RETURNS VARCHAR(8000)
AS
BEGIN
      Declare @policyNumber varchar(40)
      Declare @IssuerName varchar(100)
      Declare @CADate varchar(30)
      Select      @policyNumber = x.AnnnuitantPolicyNumber,
                  @IssuerName   = x.IssuerName,
                  @CADate              = x.EventDate         
                  From      ( Select IsNull(pd.AnnuitantPolicyNumber,'N') AnnnuitantPolicyNumber
                                    ,IsNull(i.IssuerName, 'N') IssuerName
                                    ,case when pe.[EventData] is null then 'N' else dbo.FormatDateTime(pe.[EventData], 'STANDARD') end eventDate
                                     from crmprod_01.dbo.P_Deals pd
                                                left join proc_ss.dbo.p_issuers i on pd.IssuerID = i.IssuerID
                                                left join crmprod_01.dbo.p_Events_Table pe on (pd.DealID = pe.dealID  and pe.EventTypeID = 84)
                                                where pd.DealID =@dealID) x
      DECLARE @Output VARCHAR(8000)
      SET      @Output = STUFF(ISNULL(@policyNumber, '') + ISNULL(', ' + @IssuerName, '') + ISNULL(', ' + @CADate, ''), 1, 1, '')
      if @Output is not null
        SET @Output = 'Invalid or missing ' + @Output
        else
          SET @Output = ''
      RETURN @Output
END

as mentioned above I got a NULL value for this...put a step in before the RETURN...
If @Output = NULL
@Output = 'The Select returns a Null value'

RETURN @Output

if you just need the list then

ALTER FUNCTION [dbo].[uf_ucc_structured_WithCA] (@DealID int) 

RETURNS VARCHAR(8000)
AS
BEGIN
      Declare @policyNumber varchar(40)
      Declare @IssuerName varchar(100)
      Declare @CADate varchar(30)
      Select      @policyNumber = x.AnnnuitantPolicyNumber,
                  @IssuerName   = x.IssuerName,
                  @CADate              = x.EventDate         
                  From      ( Select IsNull(pd.AnnuitantPolicyNumber,'N') AnnnuitantPolicyNumber
                                    ,IsNull(i.IssuerName, 'N') IssuerName
                                    ,case when pe.[EventData] is null then 'N' else dbo.FormatDateTime(pe.[EventData], 'STANDARD') end eventDate
                                     from crmprod_01.dbo.P_Deals pd 
                                                left join proc_ss.dbo.p_issuers i on pd.IssuerID = i.IssuerID
                                                left join crmprod_01.dbo.p_Events_Table pe on (pd.DealID = pe.dealID  and pe.EventTypeID = 84)
                                                where pd.DealID =@dealID) x
      DECLARE @Output VARCHAR(8000)
      SET      @Output = STUFF(ISNULL(@policyNumber, '') + ISNULL(', ' + @IssuerName, '') + ISNULL(', ' + @CADate, ''), 1, 1, '')
      RETURN @Output
END

Select allOpen in new window

To compare null value use IS

If @Output IS NULL
@Output = 'The Select returns a Null value'

But use LEN

ALTER FUNCTION [dbo].[uf_ucc_structured_WithCA] (@DealID int) 

RETURNS VARCHAR(8000)
AS
BEGIN
      Declare @policyNumber varchar(40)
      Declare @IssuerName varchar(100)
      Declare @CADate varchar(30)
      Select      @policyNumber = x.AnnnuitantPolicyNumber,
                  @IssuerName   = x.IssuerName,
                  @CADate              = x.EventDate         
                  From      ( Select IsNull(pd.AnnuitantPolicyNumber,'N') AnnnuitantPolicyNumber
                                    ,IsNull(i.IssuerName, 'N') IssuerName
                                    ,case when pe.[EventData] is null then 'N' else dbo.FormatDateTime(pe.[EventData], 'STANDARD') end eventDate
                                     from crmprod_01.dbo.P_Deals pd 
                                                left join proc_ss.dbo.p_issuers i on pd.IssuerID = i.IssuerID
                                                left join crmprod_01.dbo.p_Events_Table pe on (pd.DealID = pe.dealID  and pe.EventTypeID = 84)
                                                where pd.DealID =@dealID) x
      DECLARE @Output VARCHAR(8000)
      SET      @Output = STUFF(ISNULL(@policyNumber, '') + ISNULL(', ' + @IssuerName, '') + ISNULL(', ' + @CADate, ''), 1, 1, '')
      if LEN(@Output) > 0 
        SET @Output = 'Invalid or missing ' + @Output
        else
          SET @Output = 'The Select returns a Null value'
      RETURN @Output
END

Select allOpen in new window

Sorry, the logic in the last post does not look correct, use #35755508

ewangoya:

Close...but somethings a little backwards...

This is what is now returned when 1 and 3 are null
Invalid or missing , Metropolitan Life Ins Co, N

What I need is Invalid or Policy Number,CADateI

Gteat!  Thanks