CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
user1568844
user1568844

Reputation: 1

SQL Server Incorrect Syntax

I keep getting the error listed below:

Msg 156, Level 15, State 1, Procedure spGetManagerName, Line 22 Incorrect syntax near the keyword 'SELECT'.

Situation: A user selects (from a dropdown) a team which converts to a teamID and is sent to the data access layer (DAL). The teamID is passed into the stored procedure. Based on the teamID... the team & the manager table are quizzed to divulge the correct managers name.

The problem: I keep getting a syntax error message. I know/think its something small but just can't see it.

Anybody see what's going on with this... 

USE [Travel_Test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================

-- Description: Getting Manager Name via Team dropdown selection
-- =============================================
CREATE PROCEDURE [dbo].[spGetManagerName]
@inputTeamID int,
@ManName nvarchar(50) OUTPUT    
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;
SELECT 
    @ManName = ManagerFName + ' ' + ManagerLName AS ManagerName
FROM 
    Managers m
WHERE
    m.ManagerID = SELECT
                        Team.ManagerID
                   FROM
                        Team
                   WHERE
                        Team.TeamID = @inputTeamID;
                   RETURN

GO

Upvotes: 0

Views: 49

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Select Top 1 @ManName = m.ManagerFName + ' ' + m.ManagerLName 
  FROM Managers m
  JOIN Team 
    ON m.ManagerID = Team.ManagerID
   AND Team.TeamID = @inputTeamID;

Upvotes: 0

Robert
Robert

Reputation: 25753

Try to add parenthesis to the subquery:

CREATE PROCEDURE [dbo].[spGetManagerName]
@inputTeamID int,
@ManName nvarchar(50) OUTPUT    
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;
SELECT 
    @ManName = ManagerFName + ' ' + ManagerLName 
FROM 
    Managers m
WHERE
    m.ManagerID = (SELECT
                        Team.ManagerID
                   FROM
                        Team
                   WHERE
                        Team.TeamID = @inputTeamID
                   );
RETURN
GO

Upvotes: 1

Related Questions