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
'N').
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 ( 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.
source to share