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.