CODEWITHSUNDEEP
sql-serverif-statementsyntax-error
Yan Kleber
Yan Kleber

Reputation: 405

T-SQL IF problemo

I have the following stored procedure:

ALTER PROCEDURE [dbo].[getipmp]
@year varchar(4)
AS
BEGIN

select * from (

IF @year = ''
    select distinct carlinenm
    from [interchange].[dbo].[carline] 
    WHERE CarlineYear >= 1990
ELSE
    select distinct carlinenm
    from [interchange].[dbo].[carline] 
    WHERE CarlineYear = @year

UNION ALL

SELECT distinct cast(carlineyear as varchar(4)),'Y|' + rtrim(cast(carlineyear as varchar(4)))
from [interchange].[dbo].[carline]
WHERE carlineyear >= 1990

) U

END

SQL-Express is saying:

Msg 156, Level 15, State 1, Procedure getipmp, Line 15 [Batch Start Line 7]
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Procedure getipmp, Line 43 [Batch Start Line 7]
Incorrect syntax near ')'.

It refers to the "IF" at beginning and to the last ")" before the U.

Any idea what is wrong there?

Thanks!

Upvotes: 0

Views: 30

Answers (1)

Isparia
Isparia

Reputation: 712

Shouldnt it be:

ALTER PROCEDURE [dbo].[getipmp]
(
    @year varchar(4)
)
AS
BEGIN

    select  distinct carlinenm
    ,       null
    from [interchange].[dbo].[carline] 
    WHERE (CarlineYear >= 1990 AND @year = '') OR CarlineYear = @year

    UNION ALL

    SELECT distinct 
        cast(carlineyear as varchar(4))
    ,   'Y|' + rtrim(cast(carlineyear as varchar(4)))
    from [interchange].[dbo].[carline]
    WHERE carlineyear >= 1990
END

Upvotes: 1

Related Questions