Case expressions may only be nested to level %d

Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level %d.

I never experienced this in SQL Server 2017 onwards, but this was being experienced in previous version on Linked Server only. So below example is the demonstration of that assuming linked server.

Declare @id as int
set @id=13
SELECT 
    (CASE @Id
        WHEN 1 THEN 1
        WHEN 2 THEN 2
        WHEN 3 THEN 3
        WHEN 4 THEN 4
        WHEN 5 THEN 5
        WHEN 6 THEN 6
        WHEN 7 THEN 7
        WHEN 8 THEN 8
        WHEN 9 THEN 9
        WHEN 10 THEN 10
        WHEN 11 THEN 11
	WHEN 12 THEN 12
        WHEN 13 THEN 13
        WHEN 14 THEN 14
    END) AS Test
	from [SANTANA\MSSQLSERVER17].Rohit.dbo.Emp

You can resolve this by using below technique.

Using multiple coalesce cases.

Using Open query.

Points to remember :

• The above query will work absolutely fine if you run from the local instances even more than 10 conditions.this error message only happens when we are applying a case from data that comes from a Linked Server.
• If the query does not use Linked Server, you do not need to use this technique. You can use as many conditions as you want in the same case.
• If your query uses Linked Server that points to its own instance, you do not need to use this technique either. It works like a normal query without linked server.
• The COALESCE function accepts multiple conditions, so it is not limited to just 2 cases, they can be multiple (although the complexity of the code is increasing.)
• You can only use up to 9 conditions in each CASE in scenarios where the query is done on remote data.
• Using ELSE NULL at the end of each case is optional.
• Subquery and CTE do not resolve this issue.
• OPENQUERY and OPENROWSET do not have this limitation of 10 CASE conditions either.

The select list for the INSERT statement contains more items than the insert list

 Msg 121, Level 15, State 1, Line 1

The select list for the INSERT statement contains more items than the insert list. 
The number of SELECT values must match the number of INSERT columns.

SQL Server error 121 occurs when you don’t specify enough columns in your INSERT list when using a SELECT list for the values to insert.
It happens when you use a SELECT list in your INSERT statement, but the SELECT list doesn’t return as many columns as you’re specifying with the INSERT.

This is easy to fix. Simply make sure the number of columns match between your INSERT and SELECT list.

Here is the example of the code that cause the error.

INSERT INTO OrdersLatest (
    OrderId,OrderDate
    )
SELECT 
    OrderId,OrderDate,OrderDesc
	FROM OrdersMarch;

Output:

 Msg 121, Level 15, State 1, Line 1
The select list for the INSERT statement contains more items than the insert list. 
The number of SELECT values must match the number of INSERT columns.

You can fix this by correcting it.

INSERT INTO OrdersLatest (
    OrderId,OrderDate
    )
SELECT 
    OrderId,OrderDate
	FROM OrdersMarch;

You can use less columns this would depend on whether or not we have any NOT NULL constraints on the destination table.

The select list for the INSERT statement contains fewer items than the insert list

 Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. 
The number of SELECT values must match the number of INSERT columns.

SQL Server error 120 occurs when you don’t specify enough columns in your INSERT list when using a SELECT list for the values to insert.
It happens when you use a SELECT list in your INSERT statement, but the SELECT list doesn’t return as many columns as you’re specifying with the INSERT.

This is easy to fix. Simply make sure the number of columns match between your INSERT and SELECT list.

Here is the example of the code that cause the error.

INSERT INTO OrdersLatest (
    OrderId,OrderDate,OrderDesc
    )
SELECT 
    OrderId,OrderDate
	FROM OrdersMarch;

Output:

 Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. 
The number of SELECT values must match the number of INSERT columns.

You can fix this by correcting it.

INSERT INTO OrdersLatest (
    OrderId,OrderDate,OrderDesc
    )
SELECT 
    OrderId,OrderDate,OrderDesc
	FROM OrdersMarch;

You can use less columns this would depend on whether or not we have any NOT NULL constraints on the destination table.

SQL Error 119 - Must pass parameter number 2 and subsequent parameters

 Msg 119, Level 15, State 1, Line 1

Must pass parameter number 2 and subsequent parameters as '@name = value'.  After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Reason : If we want to use variable names while passing parameters to stored procedure then we must have to use variable in all the parameters.

Suppose we have created a simple stored procedure uspDisplayData in SQL Server

CREATE PROCEDURE uspDisplayData(
     @Param1 AS VARCHAR(100),
     @Param2 AS INT
)
AS
SELECT @Param1, @Param2

Now if we will execute this stored procedure:

EXECUTE dbo.uspDisplayData @Param1 = 'Sql Server',2

We will get error message like:

Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Solution:

Correct ways to pass the parameters are:

EXECUTE dbo.uspDisplayData 'Sql Server',2

OR

EXECUTE dbo.uspDisplayData @Param1 = 'Sql Server',@Param2 = 2

This will result correct result and execute without error. 

The object name 'Object Name' contains more than the maximum number of prefixes. The maximum is 2

Msg 117, Level 15, State 2, Line 4
The object name 'Object Name' contains more than the maximum number of prefixes.  The maximum is 2.

Lets see the below example. Trying Select the records.

select * from [SANTANA\MSSQLSERVER17].Rohit.dbo.Employee.Employeeid
where EmployeeID=101

Output:

Msg 117, Level 15, State 1, Line 2
The object name 'SANTANA\MSSQLSERVER17.Rohit.dbo.Employee.Employeeid' contains more than the maximum number of prefixes. The maximum is 3.

In the above example Employeeid is an invalid selection results in the error.

This error usually comes up when you are trying to perform a SELECT..INTO across a linked server. The problem is that a 4 part name cannot be used as the “INTO” parameter. This will fail.
The trick is to create the table on the remote server, then perform an INSERT INTO.

 

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

The error you're encountering typically occurs in SQL Server when a subquery that returns multiple columns is used in a context where only a single column is allowed. 

Let's look at an example and how you can resolve this error.

Example Scenario

Consider you have the following tables:

-- Sample tables
CREATE TABLE Employees (
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    DepartmentID INT
);

CREATE TABLE Departments (
    DepartmentID INT,
    DepartmentName NVARCHAR(50)
);

And you run a query like this:

SELECT 
    EmployeeID,
    (SELECT DepartmentID, DepartmentName FROM Departments WHERE Departments.DepartmentID = Employees.DepartmentID)
FROM 
    Employees

Output:

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Explanation

In the above query, the subquery (SELECT DepartmentID, DepartmentName FROM Departments WHERE Departments.DepartmentID = Employees.DepartmentID) returns two columns (DepartmentID and DepartmentName). However, it's used in a context where only one column is expected, causing SQL Server to throw the error:

Solution

To resolve this issue, you need to ensure that the subquery returns only one column. Here are a few ways to adjust your query:

Option 1: Returning a Single Column

If you only need one of the columns from the subquery, modify the subquery to return just that column:

SELECT 
    EmployeeID,
    (SELECT DepartmentName FROM Departments WHERE Departments.DepartmentID = Employees.DepartmentID) AS DepartmentName
FROM 
    Employees

Option 2: Using a JOIN

If you need multiple columns from the subquery, use a JOIN instead of a subquery:

SELECT 
    Employees.EmployeeID,
    Departments.DepartmentID,
    Departments.DepartmentName
FROM 
    Employees
JOIN 
    Departments ON Employees.DepartmentID = Departments.DepartmentID

This way, you can select multiple columns from both tables without encountering the error.

Summary

The key to resolving this error is to ensure that subqueries used in a context expecting a single column only return one column. If you need multiple columns, consider using a JOIN to include the necessary columns in your result set.

SQL Error 113 - Missing end comment mark

 Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.

SQL Server error message 113 occurs when you omit a closing comment mark.

This can occur when you open a comment but forget to close it. It can also occur when you accidentally type an opening comment.

There are 2 ways of specifying comments in a Transact-SQL script, namely with the use of two hyphens (--) for single-line comments, and with the use of /* and */ for multi-line comments. This error message occurs when using the /* and */ for multi-line comments and the closing */ is missing.

The error can be generated below 2 ways.

/*
Select * from Employee
Go

/*
--Select * from Employee /*
Go
*/

Output:

 Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.

To avoid this error make sure there are same number of opening and closing tags

/*
Select * from Employee
Go */

/*
--Select * from Employee /*
Go
*/*/

'CREATE FUNCTION' must be the first statement in a query batch

 Msg 111, Level 15, State 1, Line 3
'CREATE FUNCTION' must be the first statement in a query batch.

When creating the User Defined function, if the CREATE FUNCTION is not the first statement in the query, you will receive this error
There should be no other statements before the CREATION FUNCTION statement that would make it not the first statement in a query batch

Below is the SQL Script that will lead to the error.

Select * from Employee

Create FUNCTION [dbo].[Calculate_Age]
(
@DOB datetime , @calcDate datetime
)
RETURNS int
AS
BEGIN
declare @age int
IF (@calcDate < @DOB )
RETURN -1
-- If a DOB is supplied after the comparison date, then return -1
SELECT @age = YEAR(@calcDate) - YEAR(@DOB) +
CASE WHEN DATEADD(year,YEAR(@calcDate) - YEAR(@DOB)
,@DOB) > @calcDate THEN -1 ELSE 0 END
RETURN @age
END

Output:
Msg 111, Level 15, State 1, Line 3
'CREATE FUNCTION' must be the first statement in a query batch.

To avoid this error, CREATE FUNCTION statement must always be the first statement in the Query batch. To fix this, the GO command needs to be added to seperate the SELECT Command from the CREATE FUNCTION. One of the advantages of this is that the GO command lets the SQL Server know that its the end of the batch of T-SQL statements and anything after the GO is a new batch of query.

Select * from Employee
Go

Create FUNCTION [dbo].[Calculate_Age]
(
@DOB datetime , @calcDate datetime
)
RETURNS int
AS
BEGIN
declare @age int
IF (@calcDate < @DOB )
RETURN -1
-- If a DOB is supplied after the comparison date, then return -1
SELECT @age = YEAR(@calcDate) - YEAR(@DOB) +
CASE WHEN DATEADD(year,YEAR(@calcDate) - YEAR(@DOB)
,@DOB) > @calcDate THEN -1 ELSE 0 END
RETURN @age
END

SQL Error 110 - There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

 Msg 110, Level 15, State 1, Line 7
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

The error occurs while trying to do insert operation using INSERT..INTO.There are fewer columns in the INSERT statement than values specified in the VALUES clause.

Example:

Create Table Students 
(
	Id int,
	FirstName Varchar(50),
	LastName Varchar(50)
)
INSERT INTO Students (Id,FirstName)
VALUES (1,'Rohit','Kumar')

Output: 

 Msg 110, Level 15, State 1, Line 7
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

This can be resolved by the number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

SQL Error 109 - There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement

 Msg 109, Level 15, State 1, Line 7
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

You will see this error message when you are doing an INSERT operation to a table using INSERT TO.. There are more columns in the INSERT statement than values specified in the VALUES clause

Example:

Create Table Students 
(
	Id int,
	FirstName Varchar(50),
	LastName Varchar(50)
)
INSERT INTO Students (Id,FirstName,LastName)
VALUES (1,'Rohit')

Output:

Msg 109, Level 15, State 1, Line 7
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

The above error msg is also self descrptive.

The ORDER BY position number is out of range of the number of items in the select list

Msg 108, Level 15, State 1, Line 1
The ORDER BY position number is out of range of the number of items in the select list. 

This error occurs when specifying the column index or position number in an ORDER BY clause in your SELECT statement and the column index or position number is either 0 or higher than the number of columns specified in the SELECT clause.

This error usually happens when you are using the index in the ORDER BY clause instead of the column name and you have either used 0 or the position that is higher than the number of columns specified in the SELECT clause.

Use AdventureWorks2017
Go

Select FirstName,MiddleName,LastName
from Person.Person
Order By 4

OutPut:

Msg 108, Level 16, State 1, Line 3
The ORDER BY position number 4 is out of range of the number of items in the select list.

To avoid this, error make sure that the position number or the column index specified in the ORDER by clause is with-in the range of items in the SELECT clause.

The best practice is to use the column name instead of the index as shown below.

Use AdventureWorks2017
Go

Select FirstName,MiddleName,LastName
from Person.Person
Order By FirstName

One of the advantages of specifying the column name instead of the index is that even you add new columns in to the SELECT clause, you will have the desired result with-out any errors. Using index for sorting will result in undesired results when you add new columns in to your SELECT clause.