SQL string from 3 cases

Larry Brister
Larry Brister used Ask the Experts™
on
I have a function that is working and builds a string that is returned

What I need is to build a string that reads

'Error: Invalid '  And whichever variable or variables are = 'N'

Example...If all are invalid or = 'N'

@Output = 'Invalid or missing policyNumber,  IssuerName, CADate'

If ONLY CADate is invalid
@Output = 'Invalid or missing CADate'

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 =	@policyNumber + ' , ' + @IssuerName + ' , ' + @CADate
	RETURN @Output
END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Imran Javed ZiaConsultant Software Engineer - .NET Architect

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

SET             @Output =       IsNull(@policyNumber, '') + ' , ' + isNull(@IssuerName, '') + ' , ' + IsNull(@CADate, '')
Larry Bristersr. Developer

Author

Commented:
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
Ephraim WangoyaSoftware Engineer

Commented:


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
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

GlobaLevelProgrammer

Commented:
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
Ephraim WangoyaSoftware Engineer

Commented:

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

Open in new window

Ephraim WangoyaSoftware Engineer

Commented:

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

Open in new window

Ephraim WangoyaSoftware Engineer

Commented:

Sorry, the logic in the last post does not look correct, use #35755508
Larry Bristersr. Developer

Author

Commented:
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
Software Engineer
Commented:
Oops, yes, I returned the actual value instead of the title
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 = ''
      if @policyNumber is null 
        Set @Output = ', Policy Number'
      if @IssuerName is null
        Set @output = @output + ', Issure Number'
      if @CADate is null 
        Set @output = @output + ', CA Date'
        
      SET @Output = STUFF(@Output, 1, 1, '')
      RETURN @Output
END

Open in new window

Larry Bristersr. Developer

Author

Commented:
Gteat!  Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial