CODEWITHSUNDEEP
sql-serverstored-procedures
user2470174
user2470174

Reputation: 71

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

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.

Upvotes: 4

Views: 12707

Answers (4)

mortb
mortb

Reputation: 9849

Alternative 1:

You can put EID into a variable like this:

    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 EID is not of integer type, you'll have to specify that type in the declare statement

Alternative 2: Use a 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

Upvotes: 2

ta.speot.is
ta.speot.is

Reputation: 27214

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

Upvotes: 10

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

You can use SELECT instead of a 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, as per the discussion between Martin Smith and ta.speot.is, this does assume that there will be at least one row in tblLogin that matches @Username)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453037

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.

Upvotes: 4

Related Questions