CAST throwing error when run in stored procedure but not when run as raw query [duplicate]Why does query error with empty result set in SQL Server 2012?At which point does SQL Server validate data for insertionIntermittent “Conversion failed…” errorUPDATE statement processing records that should be eliminated by WHERE clauseImplicit conversion causing error part of the timestored procedure with multiple statements does not execute all statements when run from jobSQL Server Replication Monitor Throwing “Specified cast is not valid. (ReplicationMonitor)” ErrorUpdate code works in manual stored procedure but not when run as SQL Server Agent JobCan't cast a stored procedure parameter?ORA-01722 Error when running stored procedureStored Procedure ErrorDelete Works in one Stored Procedure but not anotherStored Procedure: Error 1064MySQL Stored Procedure not throwing error if it contains a query that succeedsSQL Server stored procedure not throwing certain kind of error
I’m planning on buying a laser printer but concerned about the life cycle of toner in the machine
US citizen flying to France today and my passport expires in less than 2 months
Is it legal for company to use my work email to pretend I still work there?
How does strength of boric acid solution increase in presence of salicylic acid?
Smoothness of finite-dimensional functional calculus
Why do falling prices hurt debtors?
Why doesn't Newton's third law mean a person bounces back to where they started when they hit the ground?
Can a Warlock become Neutral Good?
Test whether all array elements are factors of a number
tikz: show 0 at the axis origin
Today is the Center
Finding angle with pure Geometry.
Why did the Germans forbid the possession of pet pigeons in Rostov-on-Don in 1941?
Python: next in for loop
What defenses are there against being summoned by the Gate spell?
LaTeX closing $ signs makes cursor jump
How do we improve the relationship with a client software team that performs poorly and is becoming less collaborative?
Can I ask the recruiters in my resume to put the reason why I am rejected?
Have astronauts in space suits ever taken selfies? If so, how?
Can an x86 CPU running in real mode be considered to be basically an 8086 CPU?
Is it important to consider tone, melody, and musical form while writing a song?
Email Account under attack (really) - anything I can do?
Font hinting is lost in Chrome-like browsers (for some languages )
can i play a electric guitar through a bass amp?
CAST throwing error when run in stored procedure but not when run as raw query [duplicate]
Why does query error with empty result set in SQL Server 2012?At which point does SQL Server validate data for insertionIntermittent “Conversion failed…” errorUPDATE statement processing records that should be eliminated by WHERE clauseImplicit conversion causing error part of the timestored procedure with multiple statements does not execute all statements when run from jobSQL Server Replication Monitor Throwing “Specified cast is not valid. (ReplicationMonitor)” ErrorUpdate code works in manual stored procedure but not when run as SQL Server Agent JobCan't cast a stored procedure parameter?ORA-01722 Error when running stored procedureStored Procedure ErrorDelete Works in one Stored Procedure but not anotherStored Procedure: Error 1064MySQL Stored Procedure not throwing error if it contains a query that succeedsSQL Server stored procedure not throwing certain kind of error
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
This question already has an answer here:
At which point does SQL Server validate data for insertion
1 answer
Implicit conversion causing error part of the time
1 answer
UPDATE statement processing records that should be eliminated by WHERE clause
1 answer
Intermittent “Conversion failed…” error
1 answer
Why does query error with empty result set in SQL Server 2012?
2 answers
Something very odd is happening here.
I have a query that looks like this.
SELECT CAST(FT.DOP AS SMALLINT) FROM TRACKING_DATA WHERE date > @mydate and identifier = 0000000000
When run as a raw query it returns data just fine.
When i place it in a stored procedure that alters the where clause it throws this error.
Msg 244, Level 16, State 2, Procedure myprocedure, Line 107 [Batch Start Line 2]
The conversion of the varchar value '58629' overflowed an INT2 column. Use a larger integer column.
So here is the oddity. I go through all the possible data for that where clause with a query like this.
SELECT DISTINCT DOP FROM TRACKING_DATA where identifier = 000000000000
and
SELECT DISTINCT CAST(DOP AS smallint) FROM TRACKING_DATA where identifier = 000000000000
And this is what i get back.
17
12
9
19
8
14
6
16
11
13
7
10
0
18
5
15
4
Nowhere does it have anything remotely too large for a SMALLINT
. So i thought, ok maybe its a non-printable ASCII character. But i can't find any.
I'm a little perplexed at the moment. It runs find as a raw query, explodes as a procedure and all possible data based on the where is valid. My only suspicion is that the query plan is doing something odd with filtering or maybe runs with different validation when run as a procedure.
sql-server sql-server-2012 stored-procedures
marked as duplicate by Paul White♦
StackExchange.ready(function()
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();
);
);
);
yesterday
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
At which point does SQL Server validate data for insertion
1 answer
Implicit conversion causing error part of the time
1 answer
UPDATE statement processing records that should be eliminated by WHERE clause
1 answer
Intermittent “Conversion failed…” error
1 answer
Why does query error with empty result set in SQL Server 2012?
2 answers
Something very odd is happening here.
I have a query that looks like this.
SELECT CAST(FT.DOP AS SMALLINT) FROM TRACKING_DATA WHERE date > @mydate and identifier = 0000000000
When run as a raw query it returns data just fine.
When i place it in a stored procedure that alters the where clause it throws this error.
Msg 244, Level 16, State 2, Procedure myprocedure, Line 107 [Batch Start Line 2]
The conversion of the varchar value '58629' overflowed an INT2 column. Use a larger integer column.
So here is the oddity. I go through all the possible data for that where clause with a query like this.
SELECT DISTINCT DOP FROM TRACKING_DATA where identifier = 000000000000
and
SELECT DISTINCT CAST(DOP AS smallint) FROM TRACKING_DATA where identifier = 000000000000
And this is what i get back.
17
12
9
19
8
14
6
16
11
13
7
10
0
18
5
15
4
Nowhere does it have anything remotely too large for a SMALLINT
. So i thought, ok maybe its a non-printable ASCII character. But i can't find any.
I'm a little perplexed at the moment. It runs find as a raw query, explodes as a procedure and all possible data based on the where is valid. My only suspicion is that the query plan is doing something odd with filtering or maybe runs with different validation when run as a procedure.
sql-server sql-server-2012 stored-procedures
marked as duplicate by Paul White♦
StackExchange.ready(function()
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();
);
);
);
yesterday
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
At which point does SQL Server validate data for insertion
1 answer
Implicit conversion causing error part of the time
1 answer
UPDATE statement processing records that should be eliminated by WHERE clause
1 answer
Intermittent “Conversion failed…” error
1 answer
Why does query error with empty result set in SQL Server 2012?
2 answers
Something very odd is happening here.
I have a query that looks like this.
SELECT CAST(FT.DOP AS SMALLINT) FROM TRACKING_DATA WHERE date > @mydate and identifier = 0000000000
When run as a raw query it returns data just fine.
When i place it in a stored procedure that alters the where clause it throws this error.
Msg 244, Level 16, State 2, Procedure myprocedure, Line 107 [Batch Start Line 2]
The conversion of the varchar value '58629' overflowed an INT2 column. Use a larger integer column.
So here is the oddity. I go through all the possible data for that where clause with a query like this.
SELECT DISTINCT DOP FROM TRACKING_DATA where identifier = 000000000000
and
SELECT DISTINCT CAST(DOP AS smallint) FROM TRACKING_DATA where identifier = 000000000000
And this is what i get back.
17
12
9
19
8
14
6
16
11
13
7
10
0
18
5
15
4
Nowhere does it have anything remotely too large for a SMALLINT
. So i thought, ok maybe its a non-printable ASCII character. But i can't find any.
I'm a little perplexed at the moment. It runs find as a raw query, explodes as a procedure and all possible data based on the where is valid. My only suspicion is that the query plan is doing something odd with filtering or maybe runs with different validation when run as a procedure.
sql-server sql-server-2012 stored-procedures
This question already has an answer here:
At which point does SQL Server validate data for insertion
1 answer
Implicit conversion causing error part of the time
1 answer
UPDATE statement processing records that should be eliminated by WHERE clause
1 answer
Intermittent “Conversion failed…” error
1 answer
Why does query error with empty result set in SQL Server 2012?
2 answers
Something very odd is happening here.
I have a query that looks like this.
SELECT CAST(FT.DOP AS SMALLINT) FROM TRACKING_DATA WHERE date > @mydate and identifier = 0000000000
When run as a raw query it returns data just fine.
When i place it in a stored procedure that alters the where clause it throws this error.
Msg 244, Level 16, State 2, Procedure myprocedure, Line 107 [Batch Start Line 2]
The conversion of the varchar value '58629' overflowed an INT2 column. Use a larger integer column.
So here is the oddity. I go through all the possible data for that where clause with a query like this.
SELECT DISTINCT DOP FROM TRACKING_DATA where identifier = 000000000000
and
SELECT DISTINCT CAST(DOP AS smallint) FROM TRACKING_DATA where identifier = 000000000000
And this is what i get back.
17
12
9
19
8
14
6
16
11
13
7
10
0
18
5
15
4
Nowhere does it have anything remotely too large for a SMALLINT
. So i thought, ok maybe its a non-printable ASCII character. But i can't find any.
I'm a little perplexed at the moment. It runs find as a raw query, explodes as a procedure and all possible data based on the where is valid. My only suspicion is that the query plan is doing something odd with filtering or maybe runs with different validation when run as a procedure.
This question already has an answer here:
At which point does SQL Server validate data for insertion
1 answer
Implicit conversion causing error part of the time
1 answer
UPDATE statement processing records that should be eliminated by WHERE clause
1 answer
Intermittent “Conversion failed…” error
1 answer
Why does query error with empty result set in SQL Server 2012?
2 answers
sql-server sql-server-2012 stored-procedures
sql-server sql-server-2012 stored-procedures
edited Apr 3 at 18:39
Chris Rice
asked Apr 3 at 15:55
Chris RiceChris Rice
27116
27116
marked as duplicate by Paul White♦
StackExchange.ready(function()
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();
);
);
);
yesterday
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Paul White♦
StackExchange.ready(function()
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();
);
);
);
yesterday
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
So it was the damn query plan it kept deciding to use.
The value that it was exploding on was present in the table (which it shouldn't be but that's another problem) but it was associated with a completely different identifier.
The query in question was running a clustered seek
on an index that covered the date
but not the identifier
this caused it to scan the ENTIRE table which is so, so, so wrong for a number of reasons.
Added a proper index for the where
clause and the procedure is happily humming away now as it filters on the identifier before going after the date. I wish i could get before / after statistics but i'm pretty sure its running faster now too.
1
You beat me to your own answer. Sometimes stored procedures use different query plans than simple ad-hoc queries, and this can result in the system processing data in a different order. Usually, indexing is a good workaround, but sometimes you need to move the last operation outside of the data retrieval for things to work, i.e.SELECT CAST(FT.DOP AS SMALLINT) FROM (SELECT FT.DOP FROM TRACKING_DATA WHERE date > @mydate and identifier = 000000) As FT
– Laughing Vergil
Apr 3 at 16:15
2
@LaughingVergil - That is not guaranteed to work. TheCAST
can still get pushed before the filter, the most reliable way is to useTRY_CONVERT
so then it doesn't matter if it runs against rows that would fail the cast but later be eliminated anyway
– Martin Smith
Apr 3 at 16:37
add a comment |
You're better off not depending on the indexing to avoid these errors, and instead writing the query to guard against this situation. Since you're on SQL Server 2012, one option would be to use TRY_CAST:
SELECT TRY_CAST(FT.DOP AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000;
This will result in NULL
being selected for values that fail to convert from varchar to smallint. But as long as you know there aren't any results like that, or your application can handle the NULL
results, you should be good.
add a comment |
You are obviously getting different query plans and your cast is being tried against values which are filtered out by the where clause, I wouldn’t bother trying to figure out why — whatever the cause, it could obviously happen for a different reason later. Relying upon filtering is unreliable.
The thing to do is to write a query that won’t fail regardless of any index, hint, or statistics.
I think there are 3 ways to do this:
Use a temp table/table variable.
declare @result table (dop varchar);
Insert into @result
SELECT FT.DOP FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000
SELECT cast(dop as SMALLINT) dop from @resultUse a case statement:
SELECT CAST(case when FT.DOP like '%[^0-9]%' then null else ft.dop end AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000Use try_cast instead and of cast:
SELECT try_CAST(FT.DOP AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000
Personally I would recommend 3 if possible.
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
So it was the damn query plan it kept deciding to use.
The value that it was exploding on was present in the table (which it shouldn't be but that's another problem) but it was associated with a completely different identifier.
The query in question was running a clustered seek
on an index that covered the date
but not the identifier
this caused it to scan the ENTIRE table which is so, so, so wrong for a number of reasons.
Added a proper index for the where
clause and the procedure is happily humming away now as it filters on the identifier before going after the date. I wish i could get before / after statistics but i'm pretty sure its running faster now too.
1
You beat me to your own answer. Sometimes stored procedures use different query plans than simple ad-hoc queries, and this can result in the system processing data in a different order. Usually, indexing is a good workaround, but sometimes you need to move the last operation outside of the data retrieval for things to work, i.e.SELECT CAST(FT.DOP AS SMALLINT) FROM (SELECT FT.DOP FROM TRACKING_DATA WHERE date > @mydate and identifier = 000000) As FT
– Laughing Vergil
Apr 3 at 16:15
2
@LaughingVergil - That is not guaranteed to work. TheCAST
can still get pushed before the filter, the most reliable way is to useTRY_CONVERT
so then it doesn't matter if it runs against rows that would fail the cast but later be eliminated anyway
– Martin Smith
Apr 3 at 16:37
add a comment |
So it was the damn query plan it kept deciding to use.
The value that it was exploding on was present in the table (which it shouldn't be but that's another problem) but it was associated with a completely different identifier.
The query in question was running a clustered seek
on an index that covered the date
but not the identifier
this caused it to scan the ENTIRE table which is so, so, so wrong for a number of reasons.
Added a proper index for the where
clause and the procedure is happily humming away now as it filters on the identifier before going after the date. I wish i could get before / after statistics but i'm pretty sure its running faster now too.
1
You beat me to your own answer. Sometimes stored procedures use different query plans than simple ad-hoc queries, and this can result in the system processing data in a different order. Usually, indexing is a good workaround, but sometimes you need to move the last operation outside of the data retrieval for things to work, i.e.SELECT CAST(FT.DOP AS SMALLINT) FROM (SELECT FT.DOP FROM TRACKING_DATA WHERE date > @mydate and identifier = 000000) As FT
– Laughing Vergil
Apr 3 at 16:15
2
@LaughingVergil - That is not guaranteed to work. TheCAST
can still get pushed before the filter, the most reliable way is to useTRY_CONVERT
so then it doesn't matter if it runs against rows that would fail the cast but later be eliminated anyway
– Martin Smith
Apr 3 at 16:37
add a comment |
So it was the damn query plan it kept deciding to use.
The value that it was exploding on was present in the table (which it shouldn't be but that's another problem) but it was associated with a completely different identifier.
The query in question was running a clustered seek
on an index that covered the date
but not the identifier
this caused it to scan the ENTIRE table which is so, so, so wrong for a number of reasons.
Added a proper index for the where
clause and the procedure is happily humming away now as it filters on the identifier before going after the date. I wish i could get before / after statistics but i'm pretty sure its running faster now too.
So it was the damn query plan it kept deciding to use.
The value that it was exploding on was present in the table (which it shouldn't be but that's another problem) but it was associated with a completely different identifier.
The query in question was running a clustered seek
on an index that covered the date
but not the identifier
this caused it to scan the ENTIRE table which is so, so, so wrong for a number of reasons.
Added a proper index for the where
clause and the procedure is happily humming away now as it filters on the identifier before going after the date. I wish i could get before / after statistics but i'm pretty sure its running faster now too.
answered Apr 3 at 16:10
Chris RiceChris Rice
27116
27116
1
You beat me to your own answer. Sometimes stored procedures use different query plans than simple ad-hoc queries, and this can result in the system processing data in a different order. Usually, indexing is a good workaround, but sometimes you need to move the last operation outside of the data retrieval for things to work, i.e.SELECT CAST(FT.DOP AS SMALLINT) FROM (SELECT FT.DOP FROM TRACKING_DATA WHERE date > @mydate and identifier = 000000) As FT
– Laughing Vergil
Apr 3 at 16:15
2
@LaughingVergil - That is not guaranteed to work. TheCAST
can still get pushed before the filter, the most reliable way is to useTRY_CONVERT
so then it doesn't matter if it runs against rows that would fail the cast but later be eliminated anyway
– Martin Smith
Apr 3 at 16:37
add a comment |
1
You beat me to your own answer. Sometimes stored procedures use different query plans than simple ad-hoc queries, and this can result in the system processing data in a different order. Usually, indexing is a good workaround, but sometimes you need to move the last operation outside of the data retrieval for things to work, i.e.SELECT CAST(FT.DOP AS SMALLINT) FROM (SELECT FT.DOP FROM TRACKING_DATA WHERE date > @mydate and identifier = 000000) As FT
– Laughing Vergil
Apr 3 at 16:15
2
@LaughingVergil - That is not guaranteed to work. TheCAST
can still get pushed before the filter, the most reliable way is to useTRY_CONVERT
so then it doesn't matter if it runs against rows that would fail the cast but later be eliminated anyway
– Martin Smith
Apr 3 at 16:37
1
1
You beat me to your own answer. Sometimes stored procedures use different query plans than simple ad-hoc queries, and this can result in the system processing data in a different order. Usually, indexing is a good workaround, but sometimes you need to move the last operation outside of the data retrieval for things to work, i.e.
SELECT CAST(FT.DOP AS SMALLINT) FROM (SELECT FT.DOP FROM TRACKING_DATA WHERE date > @mydate and identifier = 000000) As FT
– Laughing Vergil
Apr 3 at 16:15
You beat me to your own answer. Sometimes stored procedures use different query plans than simple ad-hoc queries, and this can result in the system processing data in a different order. Usually, indexing is a good workaround, but sometimes you need to move the last operation outside of the data retrieval for things to work, i.e.
SELECT CAST(FT.DOP AS SMALLINT) FROM (SELECT FT.DOP FROM TRACKING_DATA WHERE date > @mydate and identifier = 000000) As FT
– Laughing Vergil
Apr 3 at 16:15
2
2
@LaughingVergil - That is not guaranteed to work. The
CAST
can still get pushed before the filter, the most reliable way is to use TRY_CONVERT
so then it doesn't matter if it runs against rows that would fail the cast but later be eliminated anyway– Martin Smith
Apr 3 at 16:37
@LaughingVergil - That is not guaranteed to work. The
CAST
can still get pushed before the filter, the most reliable way is to use TRY_CONVERT
so then it doesn't matter if it runs against rows that would fail the cast but later be eliminated anyway– Martin Smith
Apr 3 at 16:37
add a comment |
You're better off not depending on the indexing to avoid these errors, and instead writing the query to guard against this situation. Since you're on SQL Server 2012, one option would be to use TRY_CAST:
SELECT TRY_CAST(FT.DOP AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000;
This will result in NULL
being selected for values that fail to convert from varchar to smallint. But as long as you know there aren't any results like that, or your application can handle the NULL
results, you should be good.
add a comment |
You're better off not depending on the indexing to avoid these errors, and instead writing the query to guard against this situation. Since you're on SQL Server 2012, one option would be to use TRY_CAST:
SELECT TRY_CAST(FT.DOP AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000;
This will result in NULL
being selected for values that fail to convert from varchar to smallint. But as long as you know there aren't any results like that, or your application can handle the NULL
results, you should be good.
add a comment |
You're better off not depending on the indexing to avoid these errors, and instead writing the query to guard against this situation. Since you're on SQL Server 2012, one option would be to use TRY_CAST:
SELECT TRY_CAST(FT.DOP AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000;
This will result in NULL
being selected for values that fail to convert from varchar to smallint. But as long as you know there aren't any results like that, or your application can handle the NULL
results, you should be good.
You're better off not depending on the indexing to avoid these errors, and instead writing the query to guard against this situation. Since you're on SQL Server 2012, one option would be to use TRY_CAST:
SELECT TRY_CAST(FT.DOP AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000;
This will result in NULL
being selected for values that fail to convert from varchar to smallint. But as long as you know there aren't any results like that, or your application can handle the NULL
results, you should be good.
answered Apr 3 at 16:38
Josh DarnellJosh Darnell
7,84022243
7,84022243
add a comment |
add a comment |
You are obviously getting different query plans and your cast is being tried against values which are filtered out by the where clause, I wouldn’t bother trying to figure out why — whatever the cause, it could obviously happen for a different reason later. Relying upon filtering is unreliable.
The thing to do is to write a query that won’t fail regardless of any index, hint, or statistics.
I think there are 3 ways to do this:
Use a temp table/table variable.
declare @result table (dop varchar);
Insert into @result
SELECT FT.DOP FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000
SELECT cast(dop as SMALLINT) dop from @resultUse a case statement:
SELECT CAST(case when FT.DOP like '%[^0-9]%' then null else ft.dop end AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000Use try_cast instead and of cast:
SELECT try_CAST(FT.DOP AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000
Personally I would recommend 3 if possible.
add a comment |
You are obviously getting different query plans and your cast is being tried against values which are filtered out by the where clause, I wouldn’t bother trying to figure out why — whatever the cause, it could obviously happen for a different reason later. Relying upon filtering is unreliable.
The thing to do is to write a query that won’t fail regardless of any index, hint, or statistics.
I think there are 3 ways to do this:
Use a temp table/table variable.
declare @result table (dop varchar);
Insert into @result
SELECT FT.DOP FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000
SELECT cast(dop as SMALLINT) dop from @resultUse a case statement:
SELECT CAST(case when FT.DOP like '%[^0-9]%' then null else ft.dop end AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000Use try_cast instead and of cast:
SELECT try_CAST(FT.DOP AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000
Personally I would recommend 3 if possible.
add a comment |
You are obviously getting different query plans and your cast is being tried against values which are filtered out by the where clause, I wouldn’t bother trying to figure out why — whatever the cause, it could obviously happen for a different reason later. Relying upon filtering is unreliable.
The thing to do is to write a query that won’t fail regardless of any index, hint, or statistics.
I think there are 3 ways to do this:
Use a temp table/table variable.
declare @result table (dop varchar);
Insert into @result
SELECT FT.DOP FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000
SELECT cast(dop as SMALLINT) dop from @resultUse a case statement:
SELECT CAST(case when FT.DOP like '%[^0-9]%' then null else ft.dop end AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000Use try_cast instead and of cast:
SELECT try_CAST(FT.DOP AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000
Personally I would recommend 3 if possible.
You are obviously getting different query plans and your cast is being tried against values which are filtered out by the where clause, I wouldn’t bother trying to figure out why — whatever the cause, it could obviously happen for a different reason later. Relying upon filtering is unreliable.
The thing to do is to write a query that won’t fail regardless of any index, hint, or statistics.
I think there are 3 ways to do this:
Use a temp table/table variable.
declare @result table (dop varchar);
Insert into @result
SELECT FT.DOP FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000
SELECT cast(dop as SMALLINT) dop from @resultUse a case statement:
SELECT CAST(case when FT.DOP like '%[^0-9]%' then null else ft.dop end AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000Use try_cast instead and of cast:
SELECT try_CAST(FT.DOP AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000
Personally I would recommend 3 if possible.
answered 2 days ago
jmorenojmoreno
654516
654516
add a comment |
add a comment |