ORDER BY items must appear in the select list if SELECT DISTINCT is specified

 Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

This message occurs when you are doing a SELECT DISTINCT combined with the ORDER BY clause and one of the columns in the ORDER BY is not specified as one of the columns in the SELECT DISTINCT.

SELECT DISTINCT orderid
FROM [dbo].[Orders]
ORDER BY [OrderDate] DESC

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Instead of using the DISTINCT clause, you can use the GROUP BY clause to get the unique records from a table.  

SELECT orderid
FROM [dbo].[Orders]
GROUP BY orderid
ORDER BY MAX([OrderDate]) DESC

Cannot assign a default value to a local variable

Msg 139, Level 15, State 1, Line 1
Cannot assign a default value to a local variable. 

Prior to SQL Server 2008, assigning a default value (or initial value) to a local variable is not allowed; otherwise this error message will be encountered.

This error message can easily be generated using the following DECLARE statement entered in either SQL Server 2000 or SQL Server 2005.

DECLARE @CurrentDate DATETIME = GETDATE()

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.

SQL Server now allows the assigning of a value to a variable in the DECLARE statement.

Cannot use a BREAK statement outside the scope of a WHILE statement

 Msg 135, Level 16, State 1, Line 1

Cannot use a BREAK statement outside the scope of a WHILE statement.

The BREAK statement exits the innermost loop in a WHILE or IF… ELSE statement. Any statements appearing after the END keyword, marking the end of the loop, are executed. BREAK is frequently, but not always, started by an IF test condition.

The error message occurs when using the BREAK statement outside a WHILE statement. Here are a few examples of how this error is encountered:

-- BREAK used inside an IF condition but outside a WHILE statement
IF NOT EXISTS (SELECT custid FROM [dbo].[Customers]
               WHERE custname = 'Cust_1')
    BREAK

-- Misplaced BREAK Condition
DECLARE @Counter  INT
DECLARE @UserID   INT

SET @Counter = 0
SET @UserID = 1
WHILE EXISTS (SELECT * FROM [dbo].[UserTransaction]
              WHERE [UserID] = @UserID) 
    DELETE TOP (10) FROM [dbo].[UserTransaction]
    WHERE [UserID] = @UserID

    SET @Counter = Counter + 1

    IF @Counter > 10
        BREAK

Msg 135, Level 15, State 1, Line 38
Cannot use a BREAK statement outside the scope of a WHILE statement.

• The BREAK statement can only be used inside the scope of a WHILE statement. In cases when a set of Transact-SQL statements need to be skipped if a particular condition is not met, instead of using a BREAK statement, the GOTO statement can be used.
• In the case of exiting a stored procedure if a particular condition is met, instead of using the BREAK statement, the RETURN statement should be used. The RETURN statement exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch or statement block.
• When executing a statement block or statement group within a WHILE loop, the groups of statements need to be enclosed within a BEGIN END blocks. Otherwise only the first Transact-SQL statement within that statement block will be part of the WHILE loop. The other Transact-SQL statement will be executed after the condition in the WHILE loop is not met anymore or a BREAK statement has been encountered within the WHILE loop.

DECLARE @Counter  INT
DECLARE @UserID   INT

SET @Counter = 0
SET @UserID = 1
WHILE EXISTS (SELECT * FROM [dbo].[UserTransaction]
              WHERE [UserID] = @UserID)
BEGIN
    DELETE TOP (10) FROM [dbo].[UserTransaction]
    WHERE [UserID] = @UserID

    SET @Counter = Counter + 1

    IF @Counter > 10
        BREAK
END

Cannot update a timestamp column

 Msg 272, Level 16, State 1, Line 1

Cannot update a timestamp column.

The timestamp data type is an 8-byte data type that exposes automatically generated, unique binary numbers within a database. It is generally used as a mechanism for version-stamping table rows.

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter, the database timestamp, tracks a relative time within a database and not an actual time that can be associated with a clock.
Since the timestamp column is automatically generated by the database and is automatically incremented by the database during an update on the table, this error will be encountered if the timestamp column is manually updated through an UPDATE command.

A table can only have one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column.

CREATE TABLE [dbo].[Team] (
    [TeamID]     INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [TeamName]   VARCHAR(50),
    TIMESTAMP )

Insert one record into the table.

INSERT INTO [dbo].[Team] ( [TeamName], [Timestamp] )
VALUES ( 'Miami Marlins', DEFAULT )

select * from [dbo].[Team]

Output:

TeamID	TeamName	TIMESTAMP
1	Miami Marlins	0x00000000000007D1

Let's try to update which will generate the error msg.

UPDATE [dbo].[Team]
SET [Timestamp] = @@DBTS
WHERE [TeamName] = 'Miami Marlins'

Msg 272, Level 16, State 1, Line 11

Cannot update a timestamp column.

The timestamp column is usually used to determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read.

If there’s really a need to update the timestamp column of a table just to mark that particular record as having been updated, here’s one work around that can be done without really affecting the row but having the timestamp column updated:

UPDATE [dbo].[Team]
SET [TeamName] = [TeamName]
WHERE [TeamName] = 'Miami Marlins'

select * from [dbo].[Team]

Output:

TeamID	TeamName	TIMESTAMP
1	Miami Marlins	0x00000000000007D2

Select statements included within a function cannot return data to a client

Msg 444, Level 16, State 2, Line 1
Select statements included within a function cannot return data to a client.

When you are trying to issue a SELECT statement inside a function that will return the result to the caller, which is not allowed inside a function.

Let's see the below example to understand more.

CREATE FUNCTION [dbo].[GetUserInformation] ( @UserName VARCHAR(10) )
RETURNS VARCHAR(100)
AS 
BEGIN
    DECLARE @ErrorMessage VARCHAR(100)

    SET @ErrorMessage = ''
    IF EXISTS (SELECT 'X' FROM [dbo].[Users] WHERE [UserName] = @UserName)
        SELECT * FROM [dbo].[Users]
        WHERE [UserName] = @UserName
    ELSE
        SET @ErrorMessage = 'Invalid User Name'

    RETURN @ErrorMessage
END
GO

Msg 444, Level 16, State 2, Procedure GetUserInformation, Line 9 [Batch Start Line 0]
Select statements included within a function cannot return data to a client.

If you need to return result sets to the client, you have to use a stored procedure and not a function for this purpose.

CREATE PROCEDURE [dbo].[GetUserInformation] ( @UserName VARCHAR(10) )
AS 

SELECT * FROM [dbo].[Users]
WHERE [UserName] = @UserName
GO

If you want to return an error message if the user is not found, one way to do this is to return the error message as a separate result set:

CREATE PROCEDURE [dbo].[GetUserInformation] ( @UserName VARCHAR(10) )
AS 
    IF EXISTS (SELECT 'X' FROM [dbo].[Users]
               WHERE [UserName] = @UserName)
    BEGIN
        SELECT '' AS [ErrorMessage]
        SELECT * FROM [dbo].[Users]
        WHERE [UserName] = @UserName
    END
    ELSE
        SELECT 'Invalid User Name' AS [ErrorMessage]
GO

If the user name is found in the Users table, 2 result sets are returned.  The first result set contains the error message, which is an empty string, and the second result set is the user information.  If the user name is not found in the Users table, only 1 result set is returned which contains the error message.

The TABLESAMPLE clause can only be used with local tables

Msg 494, Level 16, State 1, Line 1
The TABLESAMPLE clause can only be used with local tables.

The TABLESAMPLE clause cannot be applied to derived tables, tables from linked servers, and tables derived from table-valued functions, rowset functions, or OPENXML. TABLESAMPLE cannot also be specified in the definition of a view or an inline table-valued function.

If the TABLESAMPLE clause is applied to a table-valued function, such as the following SELECT statement, then this error message will be generated:

CREATE FUNCTION [dbo].[ufn_GetCustomersByZIPCode] ( @ZIPCode VARCHAR(5))
RETURNS TABLE
AS
RETURN (SELECT * FROM [dbo].[Customers]
        WHERE [ZIPCode] = @ZIPCode)
GO

SELECT * FROM [dbo].[ufn_GetCustomersByZIPCode] A TABLESAMPLE (100 ROWS)

Msg 494, Level 16, State 1, Line 8
The TABLESAMPLE clause can only be used with local tables.

If the purpose of using the TABLESAMPLE clause is to generate a random set of rows from a table, table-valued function or view, then instead of using the TABLESAMPLE clause, the TOP clause together with the ORDER BY NEWID() clause can be used in its place.

In the case of the inline table-valued function shown earlier, the following SELECT statement can be used in its place which will generate 100 random rows from the rows returned by the inline table-valued function:

SELECT TOP 100 *
FROM [dbo].[ufn_GetCustomersByZIPCode] ( '12345' ) A
ORDER BY NEWID()

Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses

Msg 497, Level 15, State 1, Line 1
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.

The TABLESAMPLE clause, limits the number of rows returned from a table in the FROM clause to a sample number or PERCENT of rows. The syntax for the TABLESAMPLE clause is as follows:

One limitation of the TABLESAMPLE clause is that both the sample_number and repeat_seed parameters cannot be local variables; otherwise this error message is generated.

To generate the error let's see the below example.

Example:

CREATE TABLE [dbo].[CallDetailRecord] ( 
    [CallDetailRecordID]        INT NOT NULL IDENTITY(1, 1),
    [SourceNumber]              VARCHAR(30),
    [DestinationNumber]         VARCHAR(30),
    [CallTime]                  DATETIME,
    [CallDuration]              INT
)

-- Using a local variable for the sample number of rows
DECLARE @RowCount INT
SET @RowCount = 100
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( @RowCount ROWS)

Msg 497, Level 15, State 1, Line 12
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.

-- Using a local variable for sample percentage
DECLARE @PercentSample	DECIMAL(5, 2)
SET @PercentSample = 75.0
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( @PercentSample PERCENT)

Msg 497, Level 15, State 1, Line 17
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.

-- Using a local variable for the repeat seed
DECLARE @RepeatSeed INT
SET @RepeatSeed = 124
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 50 PERCENT ) REPEATABLE ( @RepeatSeed )

Msg 497, Level 15, State 1, Line 22
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.

Solution :

One way to avoid this error is not to make use of local variables when specifying the sample number of rows, the sample percentage or the repeat seed. Using the examples above, the following SELECT statement will generate the desired output:

SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 100 ROWS )
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 75.0 PERCENT )
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 50 PERCENT ) REPEATABLE ( 124 )

To make it dynamic it can be done in Dynamic SQL statements.

-- Using a local variable for the sample number of rows
DECLARE @RowCount INT
DECLARE @SQLStmt VARCHAR(100)
SET @RowCount = 100
SET @SQLStmt = 'SELECT * FROM [dbo].[CallDetailRecord] 
                TABLESAMPLE ( ' + CAST(@RowCount AS VARCHAR(10)) + ' ROWS )'
EXECUTE ( @SQLStmt )

Similarly other example can be done like above.

The scale must be less than or equal to the precision

Msg 192, Level 15, State 1, Line 1
The scale must be less than or equal to the precision.

To avoid the error below points can be followed.

• The scale must be within the range of 0 and the value of the precision. This can easily be done by increasing the value of the precision to include the digits both before and after the decimal point
• In the case of the incorrect scale in the definition of a DECIMAL or NUMERIC data type in column of a table, simply increase the size of the precision to include the digits both before and after the decimal point.

Try to find out the error:

DECIMAL and NUMERIC are numeric data types that have fixed precision and scale. When maximum precision is used, which is 38, valid values are from -10^38 through 10^38 – 1. NUMERIC data type is functionally equivalent to DECIMAL data type. The syntax for declaring a local variable or a column as DECIMAL or NUMERIC data type is as follows:

DECIMAL ( p [, s] )
NUMERIC ( p [, s] )

Precision (p) is the maximum number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

The optional scale (s) is the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through the value of the precision (p). Scale can only be specified if precision is specified. The default scale is 0.

Given the definition of the precision and scale of a DECIMAL or NUMERIC data type, this error message will be encountered if the specified scale is greater than the precision when defining a local variable.

DECLARE @Pi    DECIMAL(1, 6) -- 3.141592

Msg 192, Level 15, State 1, Line 1

The scale must be less than or equal to the precision.

DECLARE @Latitude    DECIMAL(2, 6)
DECLARE @Longitue    DECIMAL(3, 6)

Msg 192, Level 15, State 1, Line 2

The scale must be less than or equal to the precision.

Msg 192, Level 15, State 1, Line 3

The scale must be less than or equal to the precision.

A different error message will be encountered when the scale is greater than the precision when defining a DECIMAL or NUMERIC column in a table:

CREATE TABLE [dbo].[Product] ( 
    [ProductID]      INT,
    [ProductName]    VARCHAR(100),
    [Width]          DECIMAL(4, 6),
    [Length]         DECIMAL(4, 6),
    [Height]         DECIMAL(4, 6)
)

Msg 183, Level 15, State 1, Line 8

The scale (6) for column 'Width' must be within the range 0 to 4.

To avoid this error, as the error message suggests, the scale must be within the range of 0 and the value of the precision. This can easily be done by increasing the value of the precision to include the digits both before and after the decimal point.
Here’s an updated version of the scripts earlier that fixes the issue:

DECLARE @Pi    DECIMAL(7, 6) -- 3.141592
DECLARE @Latitude    DECIMAL(8, 6) –- 2 Digits to the left and 6 digits to the right.
DECLARE @Longitue    DECIMAL(9, 6) –- 3 Digits to the left and 6 digits to the right.

In the case of the incorrect scale in the definition of a DECIMAL or NUMERIC data type in column of a table, simply increase the size of the precision to include the digits both before and after the decimal point.

CREATE TABLE [dbo].[Product] ( 
    [ProductID]      INT,
    [ProductName]    VARCHAR(100),
    [Width]          DECIMAL(10, 6),
    [Length]         DECIMAL(10, 6),
    [Height]         DECIMAL(10, 6)
)

The last statement included within a function must be a return statement

 Msg 455, Level 16, State 2, Line 1
The last statement included within a function must be a return statement.

As the error message suggests, the last statement in a function must be a RETURN statement.  Even if the execution path of the statements in a function will execute a RETURN statement, the error will still be encountered.

To understand better, here’s a user-defined function that returns the smaller number between two integer parameters:

CREATE FUNCTION [dbo].[ufn_Least] ( @pInt1 INT, @pInt2 INT )
RETURNS INT
AS
BEGIN
    IF @pInt1 > @pInt2
        RETURN @pInt2
    ELSE
        RETURN @pInt1
END
GO

Output:
Msg 455, Level 16, State 2, Procedure ufn_Least, Line 8 [Batch Start Line 0]
The last statement included within a function must be a return statement.

To avoid this error, make sure that the last statement in your user-defined function is the RETURN statement.  In the case of the user-defined function shown above, here’s an updated version of the function that gets rid of the error:

CREATE FUNCTION [dbo].[ufn_Least] ( @pInt1 INT, @pInt2 INT )
RETURNS INT
AS
BEGIN
IF @pInt1 > @pInt2
    RETURN @pInt2

RETURN @pInt1
END
GO

Instead of putting the last RETURN statement inside the ELSE statement, it is executed by itself and the function still produces the same result.

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.

The column prefix does not match with a table name or alias name used in the query

Msg 107, Level 16, State 3, Line 1

The column prefix does not match with a table name or alias name used in the query.

The actual Error message varies with the Alias being used. The usual message is as shown below.

The column prefix '%.*ls' does not match with a table name or alias name used in the query.

The % in the above can be replaced with the alias that was used and caused the issue.

For example, below are 2 scenarios that will lead to the SQL Error msg 107.

• When the Column Prefix doesnot correspond to the table or alias name that is used in the SQL query. The below query leads to the error. 

Use AdventureWorks2017
Go

SELECT P.*
FROM Person.Person
where FirstName='Adam'

Output:

Msg 107, Level 15, State 1, Line 7

The column prefix 'P' does not match with a table name or alias name used in the query.

In this query, the alias P cannot be identified and will result above error

• The table name is specified as a column prefix where as the alias name is supplied in the FROM clause. 

Use AdventureWorks2017
Go

SELECT Person.*
FROM Person.Person As PR
where FirstName='Adam'

Output:

Msg 107, Level 15, State 1, Line 7

The column prefix 'Person' does not match with a table name or alias name used in the query.

To avoid this error, always make sure that the table name used as a prefix of a column in a query exists in the FROM clause.

Use AdventureWorks2017
Go

SELECT Person.*
FROM Person.Person
where FirstName='Adam'

Also, to avoid the second cause of this error, once you assign an alias to a table in the FROM clause, make sure to use that alias in a column prefix and not the original table name:

Use AdventureWorks2017
Go

SELECT PR.*
FROM Person.Person As PR
where FirstName='Adam'