Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

LEFT(@String, CHARINDEX(' ', @String) - 1)

To further illustrate, assume that we want to get the first name from a full name wherein the first name is separated from the last name by a space.  Here's how the query will look like:

DECLARE @FullName VARCHAR(50)
SET @FullName = 'John Doe'

SELECT LEFT(@FullName, CHARINDEX(' ', @FullName) - 1) AS [FirstName]

The result of this query will be John.  Using the same query but with the full name just containing a last name will yield an error:

DECLARE @FullName VARCHAR(50)
SET @FullName = 'McGyver'

SELECT LEFT(@FullName, CHARINDEX(' ', @FullName) - 1) AS [FirstName]

Solution/Workaround:

To avoid this error, always make sure that you pass a non-negative value to the length parameter of the SUBSTRING, LEFT and RIGHT functions.  If used in conjunction with the CHARINDEX function, you can use the NULLIF function to check if the character separator is found and return a NULL value if not found.  Here's how the query above that produces the error will look like:

DECLARE @FullName VARCHAR(50)
SET @FullName = 'McGyver'

SELECT LEFT(@FullName, NULLIF(CHARINDEX(' ', @FullName) - 1, -1)) AS [FirstName]

Since there's no space in the full name, a NULL value is returned as the first name.

Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Solution/Workaround:

There are three ways to avoid the "Division by zero encountered" error in your SELECT statement and these are as follows:

• CASE statement
• NULLIF/ISNULL functions
• SET ARITHABORT OFF and SET ANSI_WARNINGS OFF

Using the CASE statement, your query will look like the following:

SELECT CASE WHEN [Denominator] = 0 THEN 0 ELSE [Numerator] / [Denominator] END AS [Percentage]
FROM [Table1]

If the denominator or divisor is 0, the result becomes 0. Otherwise, the division operation is performed.

Using the NULLIF and ISNULL functions, your query will look like the following:

SELECT ISNULL([Numerator] / NULLIF([Denominator], 0), 0) AS [Percentage]
FROM [Table1]

What this does is change the denominator into NULL if it is zero.  Then in the division, any number divided by NULL results into NULL.  So if the denominator is 0, then the result of the division will be NULL.  Then to return a value of 0 instead of a NULL value, the ISNULL function is used.

Lastly, using the SET ARITHABORT and SET ANSI_WARNINGS, your query will look like the following:

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

SELECT [Numerator] / [Denominator]

With both ARITHABORT and ANSI_WARNINGS set to OFF, SQL Server will return a NULL value in a calculation involving a divide-by-zero error.  To return a 0 value instead of a NULL value, you can put the division operation inside an ISNULL function:

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

SELECT ISNULL([Numerator] / [Denominator], 0)

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value to a column of data type int.

DECLARE @SQL VARCHAR(2000)
DECLARE @ID INT

SET @ID = 124
SET @SQL = 'SELECT * FROM [dbo].[Customers] WHERE [ID] = ' + @ID

The reason why the error is encountered in this scenario is because an integer data type has a higher precedence over a varchar data type.  Since the integer data type has a higher precedence, the varchar data type is implicitly converted by SQL Server to an integer data type, and not the other way around as you would have assumed.

Solution/Workaround:

For the case of a varchar column that contains integer values but with a few non-integer values, you can use the ISNUMERIC function to determine if the column can be converted to an integer value or not.  To determine the rows where the column cannot be converted to an integer, you can do the following query:

SELECT * FROM [dbo].[Table1] WHERE ISNUMERIC([VarcharIntColumn]) = 0

For the case of the dynamic SQL wherein a varchar variable is concatenated with an integer variable, you have to explicitly convert the integer variable to a varchar data type using either the CAST or CONVERT function.

DECLARE @SQL VARCHAR(2000)
DECLARE @ID INT

SET @ID = 124
SET @SQL = 'SELECT * FROM [dbo].[Customers] WHERE [ID] = ' + CAST(@ID AS VARCHAR(10))

Server: Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

To illustrate, here are examples on when the error can occur:

-- Sample #1: Using INSERT INTO ... VALUES
CREATE TABLE [dbo].[Customers] ( [ID] INT, [Name] VARCHAR(100))

INSERT INTO [dbo].[Customers]
VALUES (1, 'John', 'Doe')

-- Sample #2: Using INSERT INTO ... SELECT FROM
CREATE TABLE [dbo].[Client] ( [ID] INT, [Name] VARCHAR(100))

INSERT INTO [dbo].[Client]
SELECT [ID], [Name], [Address]
FROM [dbo].[NewClient]

Solution/Workaround:

To avoid this problem, make sure that the values specified in the VALUES clause or in the SELECT subquery match the number of columns in the INSERT clause.  In addition to this, you must specify the columns in the INSERT INTO clause.  Although the column list in the INSERT INTO statement is optional, it is recommended that it is always specified so that even if there are any modifications made on the table, either new columns are added or inserted in the middle of the table or columns are deleted, the INSERT statement will not generate this error.  (Of course, a different error message will be generated if a column is deleted from the table that is being referenced by the INSERT statement).

Given the samples above, here's how to avoid the error:

-- Sample #1: Using INSERT INTO ... VALUES
CREATE TABLE [dbo].[Customers] ( [ID] INT, [Name] VARCHAR(100))

INSERT INTO [dbo].[Customers] ( [ID], [Name] )
VALUES (1, 'John Doe')

-- Sample #2: Using INSERT INTO ... SELECT FROM
CREATE TABLE [dbo].[Client] ( [ID] INT, [Name] VARCHAR(100))

INSERT INTO [dbo].[Client] ( [ID], [Name] )
SELECT [ID], [Name]
FROM [dbo].[NewClient]

Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table <Table Name> 
when IDENTITY_INSERT is set to OFF.

To illustrate, here's an example on how it can be encountered:

CREATE TABLE [dbo].[IdentityInsert] (
[ID] INT NOT NULL IDENTITY(1,1),
[Name] VARCHAR(50))
GO

INSERT INTO [dbo].[IdentityInsert] ( [ID], [Name] )
VALUES (1, ‘Old MacDonald’)
GO

Solution/Workaround:

There are two ways to avoid this error.  First is not to include the identity column in the INSERT statement and let SQL Server assign the next identity value to the record.

INSERT INTO [dbo].[IdentityInsert] ( [Name] )
VALUES (‘Old MacDonald’)
GO

The second method is with the setting of the IDENTITY_INSERT property to ON for the table.

SET IDENTITY_INSERT [dbo].[IdentityInsert] ON

INSERT INTO [dbo].[IdentityInsert] ( [ID], [Name] )
VALUES ( 2, 'Yankee Doodle')

SET IDENTITY_INSERT [dbo].[IdentityInsert] OFF

Setting the IDENTITY_INSERT to ON allows explicit values to be inserted into the identity column of a table.  At any given time, only one table in a session can have the IDENTITY_INSERT property set to ON.  If a table already has this property set to ON, and a SET IDENTITY_INSErt on statement is issued for another table, SQL Server returns an error message that states Set IDENTITY_INSERT is already ON and reports the table it is set ON for.  Execute permissions for the SET IDENTITY_INSERT default to the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles, and the object owner.