i need to add case statement in dynamic sql for stored procedure

Copper Contributor
  • I need to add case statement for ClaimnNumber[

    OUTER APPLY (

                SELECT

                    ISNULL(CONCAT(ISNULL(IWI.ClaimNumber, ''''))),

                    (

                        CASE

                            WHEN IsSupplimental = 1 THEN CONCAT('' (Supp: '', Supplimental, '')'')

                            ELSE ''''

                        END

                    )]

  • alter PROCEDURE[dbo].[spInvoiceSearchByCriteriaBasit]
  •  
  • (
  •  
  •                @Days INT=0,   
  •                @TransactionTime DATETIME='',
  •                @LastRetryTime DATETIME=''
  •  
  • )
  •  
  • AS
  •  
  • BEGIN
  •  
  •                SET NOCOUNT ON
  •  
  •                DECLARE @mSQL NVARCHAR(MAX)=''
  •                DECLARE @FromDate DATETIME,@EndTime DATETIME
  •                IF(@Days>0)
  •                BEGIN
  •                SET @FromDate=DATEADD(DAY,-@Days,GETDATE())
  •                SET @EndTime=GETDATE()
  •                END
  •                ELSE
  •                BEGIN
  •                               IF(@LastRetryTime='')
  •                               BEGIN  
  •                                   SET @FromDate=@TransactionTime    
  •                                              SET @EndTime=GETDATE()
  •                               END
  •                               ELSE
  •                               BEGIN
  •                                              SET @FromDate=@TransactionTime
  •                                              SET @EndTime=@LastRetryTime
  •                               END
  •                END
  •  
  • SET @mSQL = @mSQL +
  •                'SELECT A.InvoiceNumber InvoiceNumber,A.InvoiceTypeId,A.TimeCardId AS TimeCardID, A.ContactID,LTRIM(RTRIM(IsNULL(B.FirstName,''''))) + '' '' + LTRIM(RTRIM(IsNULL(B.Lastname,''''))) AdjusterName,B.EmployeeNumber,B.NPN,
  •                                                                                           A.ServiceFromDate AS WeekStartDate,A.CreatedDate AS InvoiceDate,
  •                                                                                           Status=(SELECT ISNULL(InvoiceProcessStatusTypeName,'''') FROM Invoice WHERE InvoiceProcessStatusTypeId=(SELECT TOP 1 InvoiceProcessStatusTypeId 
  •                                                                                           FROM InvoiceProcessLog WHERE InvoiceId=a.InvoiceId
  •                                                                                           ORDER BY InvoiceProcessLogId DESC))
  •                                                                                           ,ISNULL(A.ClaimId,'''') ClaimNumber
  •                                                                                           ,CASE WHEN A.InvoiceSourceTypeID=2 THEN C.ClaimNumber
  •                                                                                                                         WHEN A.InvoiceSourceTypeID=5 THEN F.ClaimNumber
  •                                                                                                          ELSE ''N/A'' END ClientClaimNumber
  •                                                                                             ,(CASE WHEN A.InvoiceSourceTypeID=2 THEN
  •                                                                                           (SELECT TOP 1 LTRIM(RTRIM(BranchNumber)) + '' - '' + LTRIM(RTRIM(BranchName))  FROM dbo.test WITH(NOLOCK) WHERE BranchID =  C.BranchID)
  •                                                                                           WHEN A.InvoiceSourceTypeID=3 THEN
  •                                                                                           (SELECT TOP 1 LTRIM(RTRIM(BranchNumber)) + '' - '' + LTRIM(RTRIM(BranchName))  FROM dbo.test WITH(NOLOCK) WHERE BranchID =  D.BranchID)
  •                                                                                           WHEN A.InvoiceSourceTypeID=4 THEN
  •                                                                                           (SELECT TOP 1 LTRIM(RTRIM(BranchNumber)) + '' - '' + LTRIM(RTRIM(BranchName))  FROM dbo.Branch WITH(NOLOCK) WHERE BranchID =  E.BranchID)
  •                                                                                           WHEN A.InvoiceSourceTypeID=5 THEN
  •                                                                                           (SELECT TOP 1 LTRIM(RTRIM(BranchNumber)) + '' - '' + LTRIM(RTRIM(BranchName))  FROM dbo.Test1 WITH(NOLOCK) WHERE testID =  F.testID)
  •                                                                                           WHEN A.InvoiceSourceTypeID=6 THEN
  •                                                                                           (SELECT TOP 1 LTRIM(RTRIM(BranchNumber)) + '' - '' + LTRIM(RTRIM(BranchName))  FROM dbo.test1 WITH(NOLOCK) WHERE BranchID =  G.BranchID)
  •                                                                                           ELSE
  •                                                                                           (SELECT TOP 1 LTRIM(RTRIM(BranchNumber)) + '' - '' + LTRIM(RTRIM(BranchName)) FROM dbo.Branch WITH(NOLOCK) WHERE
  •                                                                                           BranchID = (SELECT TOP 1 D.BranchID FROM [CATTimesheet_Dev].dbo.[timecard] D WITH(NOLOCK) WHERE  D.TimeCardID=A.TimeCardID ) )
  •                                                                                           END) BranchName                                       
  •                                                                                           ,A.InvoiceSourceTypeID
  •                                                                                           ,ContractBillingTypeID=ISNULL((CASE WHEN A.InvoiceSourceTypeID=3 THEN (SELECT TOP 1 ContractBillingTypeID FROM dbo.ContractInvoicePerHour  WITH(NOLOCK) WHERE ContractInvoicePerHourID=A.TimeCardID )  WHEN A.InvoiceSourceTypeID=4 THEN (SELECT TOP 1 ContractBillingTypeID FROM dbo.ContractInvoicePerDay  WITH(NOLOCK) WHERE ContractInvoicePerDayID=A.TimeCardID ) WHEN A.InvoiceSourceTypeID=5 THEN (3) WHEN A.InvoiceSourceTypeID=6 THEN (3) ELSE 0 END),0)
  •                                                                     FROM dbo.Invoice A WITH(NOLOCK)
  •                                                                                INNER JOIN dbo.Contact B WITH(NOLOCK) ON A.ContactID = B.ContactID
  •                                                                                 
  •                                                                                           OUTER APPLY     (SELECT TOP 1 ISNULL(CONCAT(ISNULL(IWI.ClaimNumber, ''''))),
  •                                                                                           ((CASE WHEN IsSupplimental = 1 THEN CONCAT('''' (Supp: '''', Supplimental, '''')'''') ELSE ''''
  •                                                                                           END)))  ClaimNumber,C.BranchID FROM dbo.Invoiceworkitem C WITH(NOLOCK)
  •                                                                                 WHERE C.InvoiceWorkItemID=A.TimeCardID) C
  •                                                                                           OUTER APPLY     (SELECT TOP 1 D.BranchID FROM dbo.ContractInvoicePerHour D WITH(NOLOCK) WHERE D.ContractInvoicePerHourID=A.TimeCardID) D
  •                                                                                           OUTER APPLY     (SELECT TOP 1 E.BranchID FROM dbo.ContractInvoicePerDay E WITH(NOLOCK) WHERE E.ContractInvoicePerDayID=A.TimeCardID) E
  •                                                                                        '
  •                                                                            SET @mSQL = @mSQL  + CHAR(13)+ ' WHERE ( CONVERT(VARCHAR(10),A.CreatedDate,112) BETWEEN '''+CONVERT(VARCHAR(10),@FromDate,112)+''' AND '''+CONVERT(VARCHAR(10),@EndTime,112)+''' ) '
  •                                                                            SET @mSQL = @mSQL  + CHAR(13)+ ' ORDER BY A.CreatedDate DESC'
  •  
  •                                                                            PRINT @mSQL
  •  
  •                                                                            EXECUTE sp_executesql @mSQL
  •  
  •  
  • END
1 Reply

 


@BasitNisar wrote:
  • I need to add case statement for ClaimnNumber[

@BasitNisar , your post is badly formatted and so unreadable, contains sometimes only code fragements; no one can work on.

Fix formating and provide much more informations.

Please post table design as DDL, some sample data as DML statement and the expected result.