SQL Repository
  • Home
  • Articles
    • MS SQL DBA
    • SSIS
    • SSRS
    • T-SQL
  • Code Snippets
    • MS SQL DBA
    • SSIS
    • SSRS
    • T-SQL
  • Interview Questions
    • MS SQL DBA
    • SSIS
    • SSRS
    • T-SQL
  • How To
    • MS SQL DBA
    • SSIS
    • SSRS
    • T-SQL
  • Contact





T-SQL Divide by Zero Error

On 31 May, 2015
T-SQL
By : Dave Jenkins
No Comments
Views : 2295

Someone in my day job this week asked me how to deal with Msg 8134, Level 16, State 1, Line 3 Divide by zero error encountered error that they were receiving. It’s second nature for me to add the below code whenever I do divison. Whether I think there are going to be Zero’s in my data or not, I feel it’s better to be safe than sorry! It also reminded me that there are new people that might need this tip, so here goes:

T-SQL Divid by Zero Error
Transact-SQL
1
2
3
4
5
-- Msg 8134, Level 16, State 1, Line 3 Divide by zero error encountered.
SELECT 100 / 0 as DivideByZero
 
-- Easy fix NULLIF(YourDivisor,0) as DivideByZero
SELECT 100 / NULLIF(0,0)

So how does it work? Nice and simple, it takes your divisor/dividend column and replaces it with NULL if its a Zero. Anything that is divided by NULL will return a NULL and you wont get the error message.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Google+ (Opens in new window)

Related



Tags :   NULLIFT-SQL

Previous Post Next Post 

About The Author

Dave Jenkins

Hello! I'm Dave Jenkins and I have been working with MS SQL Server for two years. I'm an MCP (70-461) working towards an MCSA. I love working with MS SQL Server and the BI Stack.


Number of Posts : 26
All Posts by : Dave Jenkins

Related Posts

  • Detecting Duplicates

  • UNION and UNION ALL

  • 70-461 Querying Microsoft SQL Server 2012

  • Removing duplicates from a table

Leave a Comment

Click here to cancel reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">





  • Popular
  • Recent
  • Database stuck in “Restoring” state

    17687 views
  • Find the modified date of SQL Server Agents Jobs

    8476 views
  • Script to Check TempDB Speed

    4664 views
  • PING all the Linked Servers and get a status report

    4339 views
  • Log shipping Alerts failing to send emails

    4200 views
  • Moving the tempdb database

    27 Jan, 2016
  • Script to Check TempDB Speed

    14 Jan, 2016
  • SQL Server buffer pool

    05 Nov, 2015
  • Log shipping Alerts failing to send emails

    04 Nov, 2015
  • View queries waiting for memory grant

    21 Oct, 2015

Useful links

  • Books Online for SQL Server 2012
  • Developer Reference for SQL Server 2014
  • Download SQL Server
  • Installation for SQL Server 2012
  • Microsoft Virtual Academy
  • SQL Server Online Training
  • Transact-SQL Reference
  • Tutorials for SQL Server 2012

Tags

.CSV 70-461 AdventureWorks 2012 ALL ANY CAST Chinook Database Code Snippet CONVERT CTE dataset datasource Dates DATETIME divide by zero Duplicates Exam EXCEPT expressions FORMAT IF Import Indexes INTERSECT Jobs NULLIF REBUILD Recursive CTE REORGANIZE ROW_NUMBER() Schedules Sequence SOME sp_stop_job SQL Server 2012 SQL Server Agent SSIS SSRS T-SQL Tally Table T_SQL UAC Permissions Error UNION UNION ALL

Recent Comments

  • Rudnei Silva on Log shipping Alerts failing to send emails
  • johnson Welch on Database stuck in “Restoring” state
  • Neil on Database stuck in “Restoring” state
  • Mark Gribler on MS SQL Database Administrator Interview Questions – Part 4

Google Analytics Stats

Latest Tweets:

  • 5 years ago Attended @SQLSatMcr yesterday - it was amazing! Roll on @sqlsatcambs! Won some Beats Headphones courtesy of @SQLDBApros - thanks guys! :)
  • 6 years ago Looking forward to attending @SQLSatMcr - its too far off though!!!
  • 6 years ago Simple Post: WhoIsActive SPROC: http://t.co/LZvQUaeapK
  • 6 years ago POST: Index REBUILD or Index REORGANIZE: http://t.co/h3L0N37vw4
  • 6 years ago How to Ping all Linked Servers: http://t.co/Q2QxusrKjO
  • 6 years ago For beginners - T-SQL Divide by Zero Error: http://t.co/BBhgoH5hK9

© Copyright 2015 SQL Repository. All Rights Reserved by SQL Repository