If you are writing T-SQL code targeting SQL Server 2008 you might have written statements that resembles this:

INSERT INTO
Employee(Name, OrganisationId)
VALUES('MyName', (SELECT TOP 1 ID FROM Organisation WHERE Name = 'MyOrganisation' ))

Now lets say that our T-SQL also has to run on SQL Server 2005. Nothing special here that should stop us from doing that is there? Embedding subqueries into insert statements like this was actually a new long awaited feature in Microsoft SQL Server 2008. Quess what happens if you run this query on a SQL Server 2005 or a database that is set to SQL Server 2005 compability or earlier?

It will result in the following error:

“Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”

The obvious workaround is as follows:

DECLARE @OrganisationID int
SET @OrganisationID = (SELECT TOP 1 ID FROM Organisation WHERE Name = 'MyOrganisation')
INSERT INTO Employee(Name, OrganisationId) Values('MyName', @OrganisationID)