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
Among the many requests I have tried:
select 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' end) Invoice_ContactLName + ', ' + Invoice_ContactFName as ContactName, from dbo.Invoice left outer join dbo.fnInvoiceCurrentStatus() on Invoice_ID = CUST_InvoiceID where CUST_StatusID = 7 AND Invoice_ID = dbo.Client.Client_ID AND dbo.client.emailaddr is NULL order by Inv_Created
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
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 (
) plus some other data? time? (
) it may be related to
, but this is a shot in the dark without any details.
source to share