The SQL Zone
The database server is the big iron in a server environment, and keeping them running smoothly is a full-time job. If you’re looking to improve your skills as a DBA or a database developer, this is the place to start.
The articles gathered in our SQL Server Zone cover everything from basic SQL syntax to advanced tuning techniques for stored procedures.
Alongside white papers and sample code, you can read articles for all levels of proficiency, and get answers for specific questions from the authors themselves. Your ongoing SQL Server learning begins here.
Red Gate Whitepapers
Featured Article
 |
by psined
Introduction to Boomerang Framework
|
Articles
 |
by dale.newman
Google your SQL.
|
|
by Randall Smith II
A simple solution trying out the Azure platform for development and deployment.
|
|
by Shemeer NS
CAST(), CONVERT(), PARSE(), TRY_PARSE(), TRY_CONVERT(), FORMAT(), SQL Server Functions, SQL...
|
|
by mf81
This will be a lightweight site that helps the user track their weekly comics subscriptions.
|
|
by Shemeer NS
SQL Server 2000, 2005, 2008, 2008 R2, 2012 Character String Data Types and Functions with Sample...
|
 |
by Shivprasad koirala
Implementing Audit trail using trigger
|
|
by Samir NIGAM
This article describes how to apply hover effects on GridView rows using CSS.
|
|
by Omar Al Zabir
RSS Feed aggregator and blogging Smart Client which uses Enterprise Library, Updater Application...
|
|
by Florence FZ Li
This article shows how to add a JavaScript tree menu to a DataGrid head text.
|
Discussions
|
|
 |
|
If we are selecting a set of data, and one of the fields is erroring out, with an arithematic over flow error, how to move this row aside and continue processing the remaining rows so that the entire stored procedure does not fail?
More like move this row causing the issue into a table and continue processing remaining rows?
Is there like on error resume next?
I tried a try catch block.
With in my try I had a select statement with a good value and one with a bad value, I wanted the select to print the good and not the bad one.
======================
declare @dummy int
begin try
select
--right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
select
--right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
end try
begin catch
set @dummy = 1
end catch
print @dummy
modified 2 Aug '12 - 11:06.
|
|
|
|
|
 try a bigint, in place of the int.. Care to explain why you are rounding the 50000, and what the casting is all about?
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
To give a bit more history, we are creating this view to feed into another application so it has to be set length.
Suppose this view returns 100 rows, and I am having an arthimetic overflow in on row 56 only. I want to leave 56 and continue from 57. WhenI tried the try catch block, while it catches the error, but on once it hits 56, then the whole thing stops.
|
|
|
|
|
vanikanc wrote: To give a bit more history
Did you try what I suggested? Y/N?
vanikanc wrote: I want to leave 56 and continue from 57.
Filter the row out before casting it into infinity, make the result-variable bigger, or try padding it with zeroes after the cast.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I tried with bigint - still errored out.
|
|
|
|
|
vanikanc wrote: I tried with bigint - still errored out.
Was worth a shot.
right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
Where is the @dummy used here? 500 000 * 10 000 = 5 000 000 000. That's bigger than an int, and probably wider than eight characters. Some suggestions;
- Lose the round function
- Don't multiply by 10k, pad it with 4 zero's and save the string-representation.
- If the dummy needs be multiplied by 500k, then multiply it by 5 and pad zeroes again
- Don't limit the varchar to eight characters - use varchar(50)
What kind of number are you trying to display? Can you give us an example of a number "before" (the original dummy) and the one "after" (the resulting dummy)?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thank you for all your suggestions!
It is just some inherited code, and really don't know the in and out of it. It has been working for years, so don't want to mess with what comes in and goes out.
There was an error entering the value, it was supposed to be 50 and the user keyed in 500000. So, the business wants us to put aside such errors and continue processing the data. I guess we have to code for human errors!!
Thanks for all your time and suggestions!
|
|
|
|
|
vanikanc wrote: Thank you for all your suggestions!
My pleasure, hope I wasn't too rude.
vanikanc wrote: It is just some inherited code,
Ah, that's always a good one to include on the first post. If we see code, we assume you wrote it, and partially understand it.
vanikanc wrote: There was an error entering the value, it was supposed to be 50 and the user keyed in 500000. So, the business wants us to put aside such errors and continue processing the data.
On Error Resume Next indeed. That's not how engineers work; if it fails, it fails for a reason. It get's corrected or excluded beforehand, not ignored. It's how managers work; if it fails, and nothing is burning, it's not a problem. Just ship the damn product already, we'll fix the bugs later.
Perhaps this would be a good time to add a validator to the entry-field of that user, and sanitize his/her input before it gets into the system. Once the value has been entered, it should be treated as "correct".
You heard about Knight Capital? Seems they had an "On Error Resume Next" idea to, and the algo kept buying stocks in packages of 100 at a time, 20 to 25 times a second - for over an half hour! (Total >440 million* losses - let's just be glad they weren't a hospital and relying on that software)
Ignoring errors is the worst offence in IT; the system could have skipped customer 59 for all we know. The best approach is preached by (forgive me) PHP, and it's called "do or die". Either the app does what it should do, or there's an unexpected exception - and since we cannot guarantee that the we're still working with valid data (unexpected situation, who knows what variables are loaded and not?) we have only one realistic option; let the app die. Terminate.
That's always better than continuing and writing records with an outdated identity-value after an exception, and not nowing that you're corrupting a database that was still correct when the app died.
*) I checked this time whether I should use million or billion.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
|
|
 General  News  Suggestion  Question  Bug  Answer  Joke  Rant  Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.
Red Gate Resources
Free eBooks
Product Announcements
|
|
Submit an article or tip
Post your Blog
