A RETURN statement with a return value cannot be used in this context. - Create Function

If this error occurs in the Create Function that uses a table then the variable after return statement should be removed

The syntax should be like

Create Function
Returns Table
(
Table Definition here
..
..
)
as
BEGIN
..
..
RETURN -- note no value is returned here
END

How to get the list of Functions in Database

How to Filter Functions from Sys.Objects

The following query will list of the Functions that are part of the database:

SELECT * FROM

sys.objects

where type in ('FN', 'IF', 'TF')

Cannot alter column 'X' because it is 'timestamp'.

How to Change DataType of TimeStamp Column.

Datatype of Timestamp column cannot be deleted. Hence it is advised to drop the column and re-create it

For example,

ALTER TABLE [dbo].[PerformanceMaster] DROP COLUMN PerformanceDate
ALTER TABLE [dbo].[PerformanceMaster] ADD PerformanceDate datetime2

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

TimeStamp Column in SQL Server

TimeStamp is used to store unique binary numbers within a database. This column is autogenerated with a storage size of 8 bytes.

SQL Server - TimeStamp Column

IF you want to store the Date and Time (e.g., updated time etc) use datetime2 datatype instead of timestamp.

SQL Server DateTime Column to Record Date and Time

The error "Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column." occurs when you try to insert the value using Insert statement. Leaving the column in the Insert will automatically update the value

Insert into PerformanceMaster (PerformanceID, EventID, ArtistID, PerformanceDate     )
values (@PerformanceID, @EventID, @ArtistID, @UpdateDate )


Should throw and error. Use the following instead:


Insert into PerformanceMaster (PerformanceID, EventID, ArtistID, )
values (@PerformanceID, @EventID, @ArtistID)

Information_Schema.Routines doesn't return all procedure names

How to get a list of Procedures containing Specific Text / How to get a list of Procedures using Particular Table



Here is one method we generally use for returning Stored Procedures containing a particular text

SELECT ROUTINE_NAME



FROM INFORMATION_SCHEMA.ROUTINES


WHERE ROUTINE_DEFINITION LIKE '%used_by_id%'
AND ROUTINE_TYPE = 'PROCEDURE'


order by ROUTINE_NAME

 
However, we found it has some problems when the text appears at the fag end of big procedures. Instead try the following also and match the result
 
SELECT OBJECT_NAME(id)




FROM syscomments



WHERE [text] LIKE '%used_by_id%'



AND OBJECTPROPERTY(id, 'IsProcedure') = 1



GROUP BY OBJECT_NAME(id)







SELECT Name



FROM sys.procedures



WHERE OBJECT_DEFINITION(object_id) LIKE '%used_by_id%'



order by name







SELECT OBJECT_NAME(object_id)



FROM sys.sql_modules



WHERE Definition LIKE '%used_by_id%'



AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1



order by OBJECT_NAME(object_id)

How to Specify Foriegn Key Relationships in SQL Server 2008

Foriegn Key in SQL Server 2008 - Use the Relationships option

Specify the relationship between the columns

Add new Relationship name and save when prompted

Unable to change Identity Specification in SQL Server 2008

Unable to change Identity Specification in SQL Server 2008