[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.

CREATE TABLE [dbo].[Account] {
[AccountID]     INT,
[AccountName]   VARCHAR(50)
}

INSERT INTO [dbo].[Customer] { [Name], [Gender] }
VALUES { 'Mickey Mouse', 'M' }

CREATE TABLE [dbo].[Account] (
    [AccountID]     INT,
    [AccountName]   VARCHAR(50)
)

INSERT INTO [dbo].[Customer] ( [Name], [Gender] )
VALUES ( 'Mickey Mouse', 'M' )

Server: Msg 443, Level 16, State 1, Procedure Function Name, Line 7
Invalid use of 'getdate' within a function.

To illustrate, here’s a user-defined function that computes for the age given the date of birth:

CREATE FUNCTION [dbo].[ufn_GetAge] ( @pDateOfBirth    DATETIME )
RETURNS INT
AS
BEGIN

DECLARE @vAge         INT

IF @pDateOfBirth >= GETDATE()
RETURN 0

SET @vAge = DATEDIFF(YY, @pDateOfBirth, GETDATE())

IF MONTH(@pDateOfBirth) > MONTH(GETDATE()) OR
(MONTH(@pDateOfBirth) = MONTH(GETDATE()) AND
DAY(@pDateOfBirth)   > DAY(GETDATE()))
SET @vAge = @vAge - 1

RETURN @vAge
END
GO

Since the user-defined function used the GETDATE() function in its body, the function will not be created and the following error will be encountered:

Server: Msg 443, Level 16, State 1, Procedure ufn_GetAge, Line 8
Invalid use of 'getdate' within a function.
Server: Msg 443, Level 16, State 1, Procedure ufn_GetAge, Line 11
Invalid use of 'getdate' within a function.
Server: Msg 443, Level 16, State 1, Procedure ufn_GetAge, Line 13
Invalid use of 'getdate' within a function.

Solution/Workaround:

To avoid this error, do not use the GETDATE() function inside the body of your user-defined function.  If you really need to use the GETDATE() function, pass it as a parameter in your user-defined function.  Here’s an updated version of the user-defined function above that computes for the age given the birthdate, with the reference date (GETDATE()) passed as a parameter:

CREATE FUNCTION [dbo].[ufn_GetAge] ( @pDateOfBirth    DATETIME, 
@pAsOfDate       DATETIME )
RETURNS INT
AS
BEGIN

DECLARE @vAge         INT

IF @pDateOfBirth >= @pAsOfDate
RETURN 0

SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate)

IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR
(MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND
DAY(@pDateOfBirth)   > DAY(@pAsOfDate))
SET @vAge = @vAge - 1

RETURN @vAge
END
GO

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint Constraint Name.
The conflict occurred in database Database Name, table Table Name, column Column Name.
The statement has been terminated.

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

CREATE TABLE [dbo].[State] (
    [StateCode]    CHAR(2) NOT NULL PRIMARY KEY,
    [StateName]    VARCHAR(50)
)

CREATE TABLE [dbo].[County] (
    [CountyCode]   CHAR(5) NOT NULL PRIMARY KEY,
    [CountyName]   VARCHAR(50),
    [StateCode]    CHAR(2) REFERENCES [dbo].[State] ( [StateCode] )
)

Your [dbo].[State] table contains the different states of the United States but does not yet include Puerto Rico.  Since Puerto Rico is not yet included in your [dbo].[State] table, doing an insert into the [dbo].[County] table to add a county of Puerto Rico will generate the error:

INSERT INTO [dbo].[County] ( [CountyCode], [CountyName], [StateCode] )
VALUES ( '72011', 'Añasco Municipio', 'PR' )

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK__County__StateCod__43D61337'.
The conflict occurred in database 'SQLServerHelper', table 'State', column 'StateCode'.
The statement has been terminated.

Solution/Workaround:

To avoid this error from happening, make sure that the value you are inserting into a column that references another table exists in that table.  If the value does not exist in the primary table, insert to that table first before doing the insert on the second table.

To avoid the error in the example above, Puerto Rico needs to be inserted to the [dbo].[State] table first before the county can be inserted to the [dbo].[County] table:

INSERT INTO [dbo].[State] ( [StateCode], [StateName] )
VALUES ( 'PR', 'Puerto Rico' )

INSERT INTO [dbo].[County] ( [CountyCode], [CountyName], [StateCode] )
VALUES ( '72011', 'Añasco Municipio', 'PR' )

Server: Msg 455, Level 16, State 2, Procedure Function Name, Line 65535
The last statement included within a function must be a return statement.

To illustrate, 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

Even if one of the RETURN statements will be executed given the condition, but since the last statement is not a RETURN statement by itself and not within a condition, the following error will be raised:

Server: Msg 455, Level 16, State 2, Procedure ufn_Least, Line 65535
The last statement included within a function must be a return statement.

Solution/Workaround:

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.

Server: Msg 195, Level 15, State 10, Line 1
Function Name is not a recognized function name.

To illustrate, let’s say you have the following scalar-valued user-defined function:

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

    RETURN @pInt2
END
GO

Invoking this scalar-valued user-defined function using just the single-part name of ufn_Greatest will generate the error:

SELECT ufn_Greatest ( [FirstTestScore], [SecondTestScore] ) AS [HigherScore]
FROM [dbo].[StudentScores]

Server: Msg 195, Level 15, State 10, Line 1
'ufn_Greatest' is not a recognized function name.

Solution/Workaround:

When invoking scalar-valued user-defined functions, always use the two-part name of the function, which is basically calling the function with the owner name as can be seen from the following example:

SELECT [dbo].[ufn_Greatest] ( [FirstTestScore], [SecondTestScore] ) AS [HigherScore]
FROM [dbo].[StudentScores]