Server: Msg 141, Level 15, State 1, Line 2
A SELECT statement that assigns a value to a variable 
must not be combined with data-retrieval operations.

To illustrate, using the [dbo].[Customers] table in the Northwind database, the following SELECT statement will generate the error:

DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)

SELECT @CompanyName = [CompanyName],
@ContactName = [ContactName],
[ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

The error is caused by the [ContactTitle] because it is not assigned to a local variable.

Solution/Workaround:

To avoid this error, make sure that all columns are assigned to a local variable.

DECLARE @CompanyName  NVARCHAR(40)
DECLARE @ContactName  NVARCHAR(30)
DECLARE @ContactTitle NVARCHAR(30)

SELECT @CompanyName  = [CompanyName],
@ContactName  = [ContactName],
@ContactTitle = [ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

Alternatively, if you will not be using the column, simply remove it from the SELECT statement.

DECLARE @CompanyName  NVARCHAR(40)
DECLARE @ContactName  NVARCHAR(30)

SELECT @CompanyName  = [CompanyName],
@ContactName  = [ContactName]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

If you really need to do both, meaning to assign the value to local variables and to return the columns as a result set, you have to do it in 2 steps instead of combining them into one SELECT statement:

DECLARE @CompanyName NVARCHAR(40)
DECLARE @ContactName NVARCHAR(30)

SELECT @CompanyName = [CompanyName],
@ContactName = [ContactName],
[ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

SELECT [ContactTitle]
FROM [dbo].[Customers]
WHERE [CustomerID] = 'ALFKI'

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

To illustrate, using the [dbo].[Orders] table in the Northwind database, suppose you want to get a list of unique Customer IDs that had orders ordered by the date of the order with the newest orders first, the following SELECT statement will generate the error above:

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

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

Solution/Workaround:

To avoid encountering this error, instead of using the DISTINCT clause, you can use the GROUP BY clause to get the unique records from a table.  The query above can be re-written as follows:

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

This query will return the unique Customer IDs ordered by the newest orders in descending order.

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

To illustrate, let’s say you have the following table:

CREATE TABLE [dbo].[Students] (
[StudentID]        INT,
[FirstName]        VARCHAR(10),
[LastName]         VARCHAR(10)
)

INSERT INTO [dbo].[Students] ( [StudentID], [FirstName], [LastName] )
VALUES ( 12345, 'Rumpelstiltskin', '' )

Since the [FirstName] column will only accept 10 characters, the INSERT statement will fail because the length of the value being inserted is more than 10 characters.

The error can also be encountered when decreasing the length of a VARCHAR or CHAR column in a table that already contains data and the new length of the column is not long enough to accommodate the longest value in the column.

CREATE TABLE [dbo].[Students] (
[StudentID]        INT,
[FirstName]        VARCHAR(20),
[LastName]         VARCHAR(20)
)

INSERT INTO [dbo].[Students] ( [StudentID], [FirstName], [LastName] )
VALUES ( 12345, 'Rumpelstiltskin', '' )

ALTER TABLE [dbo].[Students] ALTER COLUMN [FirstName] VARCHAR(10)

Solution/Workaround:

To prevent this error from happening when inserting data to your table or when updating existing data in your table, always make sure that the string value you are trying to insert into your table can fit to the column you’ve specified.  If you really need to insert the data to the table, your only option is to increase the length of the VARCHAR/CHAR column using the ALTER TABLE command:

ALTER TABLE [dbo].[Students] ALTER COLUMN [FirstName] VARCHAR(50)

If the error is caused by the decreasing of the length of the column, first check the maximum length that the column have and use that as the new length of the column.  To determine the maximum length of a particular column, you can do the following:

SELECT MAX(LEN([FirstName])) FROM [dbo].[Students]

This will return the length of the longest [FirstName].  Then use this value as the new length of your column if you need to decrease its length.

Server: Msg 8144, Level 16, State 2, Procedure Stored Procedure or Function Name, Line 0
Procedure or function Stored Procedure or Function Name has too many arguments specified.

To illustrate, let’s say you have the following function definition:

CREATE FUNCTION [dbo].[ufn_Concat]
( @pString1 VARCHAR(10), @pString2 VARCHAR(10) )
RETURNS VARCHAR(20)
AS
BEGIN
RETURN ISNULL(@pString1 + ' ', '') + ISNULL(@pString2, '')
END

This function expects only 2 arguments, namely @pString1 and @pString2.  To use this function, you do the following:

SELECT [dbo].[ufn_Concat] ( [FirstName], [LastName] ) AS [FullName]
FROM [dbo].[Customers]

The error will be encountered you pass more than 2 arguments or parameters to the function, as follows:

SELECT [dbo].[ufn_Concat] ( [FirstName], [MiddleName], [LastName] ) AS [FullName]
FROM [dbo].[Customers]

Server: Msg 8144, Level 16, State 2, Line 1
Procedure or function dbo.ufn_Concat has too many arguments specified.

Solution/Workaround:

To avoid this error from happening, always make sure that you pass the same number of arguments that a stored procedure or function is expecting.  To know the parameters expected by a stored procedure, you can use the sp_help system stored procedure and pass the name of the stored procedure as the parameter.

Server: Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the procedure or function 
Stored Procedure or Function Name.

To illustrate, let’s say you have the following function definition:

CREATE FUNCTION [dbo].[ufn_Concat]
    ( @pString1 VARCHAR(10), @pString2 VARCHAR(10), @pString3 VARCHAR(10) )
    RETURNS VARCHAR(30)
    AS
    BEGIN
    RETURN ISNULL(@pString1 + ' ', '') + 
    ISNULL(@pString2 + ' ', '') +
    ISNULL(@pString3, '')
    END
    

This function expects 3 arguments, namely @pString1, @pString2 and @pString3.  To use this function, you do the following:

SELECT [dbo].[ufn_Concat] ( [FirstName], [MiddleName], [LastName] ) AS [FullName]
    FROM [dbo].[Customers]
    

The error will be encountered you don’t pass 3 arguments or parameters to the function, as follows:

SELECT [dbo].[ufn_Concat] ( [FirstName], [LastName] ) AS [FullName]
    FROM [dbo].[Customers]
    

Server: Msg 313, Level 16, State 2, Line 1
    An insufficient number of arguments were supplied for the procedure or function dbo.ufn_Concat.
    

Solution/Workaround:

To avoid this error from happening, always make sure that you pass the same number of arguments that a stored procedure or function is expecting.  To know the parameters expected by a stored procedure, you can use the sp_help system stored procedure and pass the name of the stored procedure as the parameter.