none
TSQL error in IF clause RRS feed

  • Question

  • Hello!

    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!

    Friday, February 14, 2020 3:03 PM

All replies

  • IF / ELSE is a flow control command, you can not use it within a SELECT statement; that's why you get the error message.

    You can re-write the query as

    select distinct carlinenm
    from [interchange].[dbo].[carline] 
    WHERE (CarlineYear = @year AND @year <> '')
          OR 
          (CarlineYear >= 1990 AND @year = '')
    
    UNION ALL
    
    SELECT distinct cast(carlineyear as varchar(4)),'Y|' + rtrim(cast(carlineyear as varchar(4)))
    from [interchange].[dbo].[carline]
    WHERE carlineyear >= 1990


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, February 14, 2020 3:31 PM
  • You can also try following:

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

    select * from #temp1

    UNION ALL

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

    Mark as answer if it helps. Thanks.


    • Edited by Soumen Barua Saturday, February 15, 2020 3:57 AM
    Saturday, February 15, 2020 3:56 AM
  • SQL is declarative language, so we hate seeing things like while loops, if – then – else statements and local variables. You cannot put flow control into select statements. 

    It is also a language that depends on a tiered architecture, so we don't do formatting in the database tier of this architecture. The results are passed up to a presentation tier that does that work for us. Since we have no DDL (did you read the part about posting on these forums?), I'm going to guess that the term "Ipmp" is well understood in your industry, and that you've stored something called a "carline_year" is a string of four digits. My guess as to what you meant to post should look something like this:

    CREATE PROCEDURE Get_Ipmp
    @in_carline_year CHAR(4)
    AS
    SELECT * -- never use * in production code!!
    FROM Carlines
    WHERE carline_year >= '1990'
    OR carline_year = @in_carline_year;

    Would you care to provide us with the bare minimum, we need to answer your problem?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, February 15, 2020 5:17 AM
  • Hi yankleberr,

    Please try following script.

    ALTER PROCEDURE [dbo].[getipmp]
    @year varchar(4)
    AS
    BEGIN
    declare @sql varchar(max)
    declare @part_sql varchar(max)
    
    IF @year = ''
    set @part_sql='select distinct carlinenm
    from [interchange].[dbo].[carline] 
    WHERE CarlineYear >= 1990'
    else set @part_sql='select distinct carlinenm
    from [interchange].[dbo].[carline] 
    WHERE CarlineYear = '+@year
    
    set @sql='select * from (
    '+@part_sql+'
    UNION ALL
    SELECT distinct cast(carlineyear as varchar(4)),''Y|'' + rtrim(cast(carlineyear as varchar(4)))
    from [interchange].[dbo].[carline]
    WHERE carlineyear >= 1990
    ) U'
    print(@sql)
    
    END

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    13 hours 34 minutes ago