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

Question

My stored procedure works fine in SQL Server 2008, but when I try to run the same procedure in SQL Server 2005, it throws this error by indicating

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

following: 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 if there is any solution for this or sql 2005 doest to support such queries?

Thanks in advance.

Answer 1

You can use SELECT instead of the 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 does not support this. It was introduced in 2008.

Instead, you can assign the result of the subquery to a variable and use this in the VALUES clause.

Answer 3

Alternative 1:

You can put the EID in a variable as follows:

  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 DECLARE @EID INT; SET @EID = (SELECT top 1 EID FROM tblLogin WHERE Username=@Username ); INSERT INTO tblTaskAssignment (EID,ProjectName, ClientName, Status, StartDate, EndDate,ReportingManager,Comments) Values (@EID, @ProjectName, @ClientName, @Status, @StartDate, @EndDate,@ReportingManager,@Comments) END 

If the EID does not have an integer type, you need to specify this type in the statement of the operator

Alternative 2: Use the SELECT clause

  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) SELECT TOP 1 EID, @ProjectName, @ClientName, @Status, @StartDate, @EndDate,@ReportingManager,@Comments FROM tblLogin WHERE Username=@Username END 

Answer 4

You can use SELECT instead of the VALUES clause:

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

(Although note that according to the discussion between Martin Smith and ta.speot.is, this assumes that at tblLogin will be at least one line in @Username )