Filtering stored procedure records with a nested case statement

I need to modify my saved proc result set from this post , I need to filter my result set to only display records where emailaddr is NULL (which means that only records write that are Invoice_DeliveryType


Among the many requests I have tried:

    Invoice_ID, 'Unknown' as Invoice_Status, 
    case when Invoice_Printed is null then '' else 'Y' end as Invoice_Printed, 
    case when Invoice_DeliveryDate is null then '' else 'Y' end as Invoice_Delivered, 
    (case when Invoice_DeliveryType <> 'USPS' then ''
          when exists (Select 1
                   from dbo.Client c
                   Where c.Client_ID = SUBSTRING(i.Invoice_ID, 1, 6) and
                         c.emailaddr is not null
          then 'Y'
          else 'N'
    Invoice_ContactLName + ', ' + Invoice_ContactFName as ContactName, 
left outer join 
    dbo.fnInvoiceCurrentStatus() on Invoice_ID = CUST_InvoiceID 
    CUST_StatusID = 7 
    AND Invoice_ID = dbo.Client.Client_ID
    AND dbo.client.emailaddr is NULL
order by 


but i get the error

Converting nvarchar value '20111028995999' overflowed with int column

How can I get the stored procedure to return records with only help DeliveryType = 'N'



source to share

2 answers

Attempting to select stored proc results into temp table then select * from #TempTable



We could really do with schema definition to solve this problem.

It looks like there is an implicit conversion happening in one of your case arguments, but without a schema it's fuzzy to keep track of which one.

You cannot safely mix data types in CASE expressions unless you are sure that any implicit conversions will work fine. You must make explicit conversions.

Judging from the error message, it looks like something that could be a date represented as a string ( 20111028

) plus some other data? time? ( 995999

) it may be related to Invoice_DeliveryDate

, but this is a shot in the dark without any details.



All Articles