SET ANSI_WARNINGS OFF - SQL 2005
I would like some advice on the SET ANSI_WARNINGS OFF.
I had a pretty complex query that was returning the "String or binary data would be truncated" error message so I dropped in SET ANSI_WARNINGS OFF. What (if any) are my issues with using this statement? Is there a more simple way of identifying the "String or binary data would be truncated" error message? Is it possible to see the truncated records to pin point what was the problem? I know there are alot of SQL gurus on this board ... so what I'm hoping for is a shortcut to get me past this message and advice on the pro's and con's to using the SET ANSI_WARNINGS OFF.
I had a pretty complex query that was returning the "String or binary data would be truncated" error message so I dropped in SET ANSI_WARNINGS OFF. What (if any) are my issues with using this statement? Is there a more simple way of identifying the "String or binary data would be truncated" error message? Is it possible to see the truncated records to pin point what was the problem? I know there are alot of SQL gurus on this board ... so what I'm hoping for is a shortcut to get me past this message and advice on the pro's and con's to using the SET ANSI_WARNINGS OFF.
ASKER
Here is the SQL, I historically took it piece by piece until I found the issue. I guess I was hoping for a way to step past this alittle faster or easier. If you can spot anything out of whack let me know. The statements in the "WHERE" clause will look odd to you, but rest assured this is how the third party application handles them. Strange ... but it works.
/*Patient Ledger*/
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE @DOBFrom datetime, @DOBTo datetime
IF 1 = 1
BEGIN
SET @DOBFrom = getdate() - (120 * 365.25)
IF 1 = 1
BEGIN
SET @DOBTo = getdate() - ((1-1) * 365.25)
END
ELSE
BEGIN
SET @DOBTo = getdate() - (1 * 365.25)
END
END
ELSE
BEGIN
IF NULL = NULL
BEGIN
SET @DOBFrom = getdate() - (365.25*110)
SET @DOBTo = getdate()
END
ELSE
BEGIN
SET @DOBFrom = NULL
SET @DOBTo = NULL
END
END
CREATE TABLE #Visit
(
PatientVisitId int,
PatientProfileId int
)
CREATE TABLE #Ledger
(
PatientProfileId int,
Type smallint,
PatientId varchar(15) NULL,
PatientMRN VARCHAR(15) NULL,
Birthdate datetime NULL,
PatientAge INT NULL,
PatientSex VARCHAR(1)NULL,
PatientSSN VARCHAR(15)NULL,
PatientName varchar(110) NULL,
PatientAddress1 varchar(50) NULL,
PatientAddress2 varchar(50) NULL,
PatientCity varchar(30) NULL,
PatientState varchar(3) NULL,
PatientZip varchar(10) NULL,
PatientPhone1 varchar(15) NULL,
PatientPhone1Type varchar(25) NULL,
PatientPhone2 varchar(15) NULL,
PatientPhone2Type varchar(25) NULL,
PatientVisitId int NULL,
VisitDateOfService datetime NULL,
VisitDateOfEntry datetime NULL,
DoctorId int NULL,
DoctorName varchar(60) NULL,
FacilityId int NULL,
FacilityName varchar(60) NULL,
CompanyId int NULL,
CompanyName varchar(60) NULL,
TicketNumber varchar(20) NULL,
PrimaryCarrier varchar(50) NULL,
CurrentCarrier varchar(50) NULL,
PatientVisitProcsId int NULL,
TransactionDateOfServiceFrom datetime NULL,
TransactionDateOfServiceTo datetime NULL,
TransactionDateOfEntry datetime NULL,
InternalCode varchar(10) NULL,
ExternalCode varchar(10) NULL,
Modifier1 varchar(4) NULL,
Modifier2 varchar(4) NULL,
Modifier3 varchar(4) NULL,
Modifier4 varchar(4) NULL,
Description varchar(255) NULL,
Fee money NULL,
Units float NULL,
PatAmount money NULL,
InsAmount money NULL,
Action varchar(1) NULL,
Payer varchar(255) NULL,
Notes text NULL,
PatBalance money NULL,
InsBalance money NULL,
Charges money NULL,
Payments money NULL,
Adjustments money NULL
)
/* Get the subset of visits for this report */
INSERT #Visit
SELECT pv.PatientVisitId,
pv.PatientProfileId
FROM PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
WHERE --Filter on Patient
(
(NULL IS NOT NULL AND pp.PatientProfileId in (NULL)) OR
(NULL IS NULL)
)
AND --Filter on date type and range
(
('1' = '1' AND pv.Visit >= ISNULL(NULL,'1/1/1900') AND pv.Visit < dateadd(d, 1, ISNULL(NULL,'1/1/3000'))) OR
('1' = '2' AND pv.Entered >= ISNULL(NULL,'1/1/1900') AND pv.Entered < dateadd(d,1,ISNULL(NULL,'1/1/3000')))
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on company
(
(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on patient sex
(
(Null IS NOT NULL AND pp.sex in (Null)) OR
(Null IS NULL)
)
AND --Filter on DOB Date
(
pp.Birthdate >= @DOBFrom AND pp.Birthdate <= @DOBTo
)
/* Get demographics for the patient */
INSERT #Ledger
(
PatientProfileId, Type, PatientId, PatientMRN, Birthdate, PatientAge, PatientSex, PatientSSN,
PatientName, PatientAddress1, PatientAddress2, PatientCity, PatientState, PatientZip,
PatientPhone1, PatientPhone1Type, PatientPhone2, PatientPhone2Type,
PatBalance, InsBalance, Charges, Payments, Adjustments
)
SELECT DISTINCT pp.PatientProfileId,
1,
pp.PatientId,
ISNULL(pp.MedicalRecordNumber,'') AS PatientMRN,
pp.Birthdate,
datediff(YYYY, isnull(pp.birthdate, getdate()), getdate())AS PatientAge,
ISNULL(pp.Sex,'') AS PatientSex,
ISNULL(SUBSTRING(pp.SSN,1,3) + '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),'') AS PatientSSN,
RTRIM(RTRIM(RTRIM(ISNULL(pp.First, '') + ' ' + ISNULL(pp.Middle, '')) + ' ' + pp.Last) + ' ' + ISNULL(pp.Suffix, '')) AS PatientName,
pp.Address1,
pp.Address2,
pp.City,
pp.State,
pp.Zip,
ISNULL(dbo.formatphone(pp.phone1,1),'') AS Phone1,
pp.Phone1Type,
ISNULL(dbo.formatphone(pp.phone2,1),'') AS Phone2,
pp.Phone2Type,
PatBalance = (SELECT SUM(pva.PatBalance) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
InsBalance = (SELECT SUM(pva.InsBalance) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
Charges = (SELECT SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
Payments = (SELECT SUM(pva.InsPayment + pva.PatPayment) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
Adjustments = (SELECT SUM(pva.InsAdjustment + pva.PatAdjustment) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId)
FROM PatientProfile pp
INNER JOIN #Visit tv ON pp.PatientProfileId = tv.PatientProfileId
/* Get visit information for the patient */
INSERT #Ledger
(
PatientProfileId, Type, PatientVisitId, VisitDateOfService, VisitDateOfEntry,
DoctorId, DoctorName, FacilityId, FacilityName, CompanyId, CompanyName,
TicketNumber, PrimaryCarrier, CurrentCarrier
)
SELECT pv.PatientProfileId,
2,
pv.PatientVisitId,
pv.Visit,
pv.Entered,
pv.DoctorId,
d.ListName AS DoctorName,
pv.FacilityId,
f.ListName AS FacilityName,
pv.CompanyId,
c.ListName AS CompanyName,
pv.TicketNumber,
ic.ListName AS PrimaryCarrier,
ic2.ListName AS CurrentCarrier
FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN InsuranceCarriers ic2 ON pv.CurrentInsuranceCarriersId = ic2.InsuranceCarriersId
WHERE --Filter on Insurance Carrier
(
('30' IS NOT NULL AND pv.PrimaryInsuranceCarriersId IN (30)) OR
('30' IS NULL)
)
/* Get diagnosis information for the patient's visits */
INSERT #Ledger
(
PatientProfileId, Type, PatientVisitId,
InternalCode, ExternalCode,
Description
)
SELECT pv.PatientProfileId,
3,
pv.PatientVisitId,
pvd.Code,
pvd.ICD9Code,
pvd.Description
FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
/* Get procedure information for the patient's visits */
INSERT #Ledger
(
PatientProfileId, Type, PatientVisitId, PatientVisitProcsId,
TransactionDateOfServiceFrom, TransactionDateOfServiceTo, TransactionDateOfEntry,
InternalCode, ExternalCode, Modifier1, Modifier2, Modifier3, Modifier4,
Description, Fee, Units, PatAmount, InsAmount
)
SELECT pv.PatientProfileId,
4,
pv.PatientVisitId,
pvp.PatientVisitProcsId,
pvp.DateOfServiceFrom,
pvp.DateOfServiceTo,
b.Entry,
pvp.Code,
pvp.CPTCode,
m1.Code AS Modifier1,
m2.Code AS Modifier2,
m3.Code AS Modifier3,
m4.Code AS Modifier4,
pvp.Description,
pvp.TotalFee,
pvp.Units,
pvpa.OrigPatAllocation,
pvpa.OrigInsAllocation
FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
INNER JOIN Batch b ON pvp.BatchId = b.BatchId
INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsId = pvpa.PatientVisitProcsId
LEFT JOIN MedLists m1 ON pvp.Modifier1MId = m1.MedListsId
LEFT JOIN MedLists m2 ON pvp.Modifier2MId = m2.MedListsId
LEFT JOIN MedLists m3 ON pvp.Modifier3MId = m3.MedListsId
LEFT JOIN MedLists m4 ON pvp.Modifier4MId = m4.MedListsId
/* Get transaction information for the patient's visits */
INSERT #Ledger
(
PatientProfileId, Type, PatientVisitId, PatientVisitProcsId,
TransactionDateOfServiceFrom, TransactionDateOfEntry,
Description, Payer, PatAmount, InsAmount, Action, Notes
)
SELECT pv.PatientProfileId,
5,
pv.PatientVisitId,
NULL,
ISNULL(pm.CheckDate, b.Entry),
b.Entry,
CASE WHEN t.Action = 'T' THEN CASE WHEN pm.Source = 1 THEN 'Transfer from Patient' ELSE 'Transfer from Insurance' END ELSE at.Description END,
pm.PayerName,
CASE WHEN pm.Source = 1 THEN -t.Amount ELSE CASE WHEN t.Action = 'T' THEN t.Amount ELSE 0 END END,
CASE WHEN pm.Source = 2 THEN -t.Amount ELSE CASE WHEN t.Action = 'T' THEN t.Amount ELSE 0 END END,
t.Action,
CASE WHEN ISNULL(t.ShowOnStatement, 0) <> 0 THEN t.Note ELSE NULL END
FROM #Visit tv
INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitId
INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId AND t.Action <> 'N'
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId AND ISNULL(pm.InsuranceTransfer, 0) = 0
INNER JOIN Batch b ON pm.BatchId = b.BatchId
LEFT JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
SELECT tl.*
FROM #Ledger tl
ORDER BY tl.PatientProfileId, tl.PatientVisitId, tl.PatientVisitProcsId, tl.TYPE
DROP TABLE #Visit
DROP TABLE #Ledger
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks ...
Mainly that it might mask some soft errors that you really do care about. Personally, I would NEVER turn it OFF permanently. I might consider toggling it ON/OFF for the duration of a query if I fully understood the implications and accepted the risk.
>Is there a more simple way of identifying the "String or binary data would be truncated" error message?
No. You need to find the source of the problem and modify the code so that it doesn't happen. You may need to increase a column or variable size or you may need to explicitly truncate some data so that it fits.
Often, you can isolate the problem by inspecting the SQL with a little common sense and a big understanding of your data and datatypes. Or at least you can find the likely candidates. Other than that, start simplifying the SQL until the problem disappears. Then, fix what's causing it.
If you post your query, we can probably speed you along.
>Is it possible to see the truncated records to pin point what was the problem?
Hard to say without seeing your query. Certainly you could modify your query to capture the results in such a way that they are viewable.