Subqueries are not allowed in this context. Only scalar expressions are allowed

Question

My stored procedure is working fine in SQL Server 2008 but when i try to run the same procedure in SQL server 2005 it throws this error stating

Subqueries are not allowed in this context. Only scalar expressions are allowed.

following is my sp

USE dbEmployeeManagementSystem
GO
CREATE PROCEDURE [dbo].spInsertTaskAssignmentsample
(
@Username nvarchar(50),
@ProjectName nvarchar(50),
@ClientName nvarchar(50),
@Status nvarchar(50),
@StartDate nvarchar(50),
@EndDate nvarchar(50),
@ReportingManager nvarchar(50),
@Comments nvarchar(100)
 )
AS
BEGIN   
INSERT INTO tblTaskAssignment
(EID,ProjectName, ClientName, Status, StartDate, EndDate,ReportingManager,Comments) 
Values
((SELECT top 1 EID FROM tblLogin WHERE Username=@Username), @ProjectName, @ClientName, @Status, @StartDate, @EndDate,@ReportingManager,@Comments)
END

Please help me, is there any solution for this or sql 2005 doest support such kind of queries?

Thanks in advance.

Answer 1

You can use SELECT instead of a VALUES clause:

INSERT INTO tblTaskAssignment
(EID,ProjectName, ClientName, Status, StartDate, EndDate,ReportingManager,Comments) 
SELECT (SELECT TOP 1 EID FROM tblLogin WHERE Username=@Username), @ProjectName, @ClientName, @Status, @StartDate, @EndDate,@ReportingManager,@Comments

Answer 2

SQL Server 2005 doesn't support this. It was introduced in 2008.

You can assign the result of the subquery to a variable instead and use that in the VALUES clause.