function getting error in IF Statement

[murphyhg]

Hello, I am trying to modify this function to pass an additional parameter to it called ByPassURL. If the variable ByPassURL is empty I would like to do one thing. If it is not empty I want to insert BypassURL into the table and return it. This is the error I am getting.
Msg 156, Level 15, State 1, Procedure getContentURLSetByPass, Line 16
Incorrect syntax near the keyword 'IF'.
Here is the code.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[getContentURLSetByPass]
(
@Tier int, -- this is the ID of the location
@Tier_ID int, -- this is what level the article is attached
@BYPassURL varchar(1000) = ''

)
RETURNS @Output TABLE(URL VarChar(1000))
AS
-- Add the SELECT statement with parameter references here

BEGIN
IF @Tier_ID = 3
BEGIN
Insert into @Output(URL)
IF (@BYPassURL IS NULL)--Error on this line
BEGIN
select (select '/global/index.cfm/L1-'+cast(B.T1IDFK as varchar(10))+ '/L2-'+cast(A.T2IDFK as varchar(10))+'/L3-'+cast(A.T3ID as varchar(10))+'/') AS URL
from dbo.Tier3 A, dbo.Tier2 B
WHERE A.T3ID = @Tier
AND A.T2IDFK = B.T2ID
END
ELSE
BEGIN
select @BYPassURL AS URL
from dbo.Tier3 A, dbo.Tier2 B
WHERE A.T3ID = @Tier
AND A.T2IDFK = B.T2ID
END

END
IF @Tier_ID = 2
BEGIN
Insert into @Output(URL)
select (select '/global/index.cfm/L1-'+cast(A.T1IDFK as varchar(10))+ '/L2-'+cast(A.T2ID as varchar(10))+'/') AS URL
from dbo.Tier2 A
where A.T2ID = @Tier
END
IF @Tier_ID = 1
Insert into @Output(URL)
select (select '/global/index.cfm/L1-'+cast(A.T1ID as varchar(10))+'/') AS URL
from dbo.Tier1 A
where A.T1ID = @Tier

RETURN
END

 

[philhege]

Turn that IF statement into a CASE and place it after the SELECT statement, that is, handle the case in the value assignment.

-------++NO CARRIER++-------

 

[gmmastros]

You need to move the "Insert Into" line inside the if, like this:

CREATE FUNCTION [dbo].[getContentURLSetByPass]
(    
    @Tier int, -- this is the ID of the location
    @Tier_ID int, -- this is what level the article is attached
    @BYPassURL varchar(1000) = ''
    
)
RETURNS @Output TABLE(URL VarChar(1000))
AS
    -- Add the SELECT statement with parameter references here
    
BEGIN
    IF @Tier_ID = 3
    BEGIN
        IF (@BYPassURL IS NULL)--Error on this line
        BEGIN
        [!]Insert into @Output(URL)[/!]
        select (select '/global/index.cfm/L1-'+cast(B.T1IDFK as varchar(10))+ '/L2-'+cast(A.T2IDFK as varchar(10))+'/L3-'+cast(A.T3ID as varchar(10))+'/') AS URL
        from dbo.Tier3 A, dbo.Tier2 B
        WHERE  A.T3ID = @Tier
        AND A.T2IDFK = B.T2ID
        END
        ELSE
        BEGIN
        [!]Insert into @Output(URL)[/!]
        select @BYPassURL AS URL
        from dbo.Tier3 A, dbo.Tier2 B
        WHERE  A.T3ID = @Tier
        AND A.T2IDFK = B.T2ID
        END

    END
    IF @Tier_ID = 2
    BEGIN
        Insert into @Output(URL)
        select (select '/global/index.cfm/L1-'+cast(A.T1IDFK as varchar(10))+ '/L2-'+cast(A.T2ID as varchar(10))+'/') AS URL
        from dbo.Tier2 A
        where  A.T2ID = @Tier
    END
    IF @Tier_ID = 1
        Insert into @Output(URL)
        select (select '/global/index.cfm/L1-'+cast(A.T1ID as varchar(10))+'/') AS URL
        from dbo.Tier1 A
        where A.T1ID = @Tier

    RETURN
END

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[murphyhg]

I cannot seem to get the syntax correct for this. Getting these errors.
Msg 156, Level 15, State 1, Procedure getContentURLSetByPass, Line 18
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure getContentURLSetByPass, Line 30
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Procedure getContentURLSetByPass, Line 44
Incorrect syntax near 'END'.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[getContentURLSetByPass]
(
@Tier int, -- this is the ID of the location
@Tier_ID int,
@ByPassURL varchar(1000) -- this is what level the article is attached

)
RETURNS @Output TABLE(URL VarChar(1000))
AS
-- Add the SELECT statement with parameter references here

BEGIN
IF @Tier_ID = 3
BEGIN
Insert into @Output(URL)
SELECT CASE @BYPassURL WHEN ''
THEN
(select '/global/index.cfm/L1-'+cast(B.T1IDFK as varchar(10))+ '/L2-'+cast(A.T2IDFK as varchar(10))+'/L3-'+cast(A.T3ID as varchar(10))+'/') AS URL
from dbo.Tier3 A, dbo.Tier2 B
WHERE A.T3ID = @Tier
AND A.T2IDFK = B.T2ID
ELSE
select @BYPassURL AS URL
from dbo.Tier3 A, dbo.Tier2 B
WHERE A.T3ID = @Tier
AND A.T2IDFK = B.T2ID
END

END
IF @Tier_ID = 2
BEGIN
Insert into @Output(URL)
select (select '/global/index.cfm/L1-'+cast(A.T1IDFK as varchar(10))+ '/L2-'+cast(A.T2ID as varchar(10))+'/') AS URL
from dbo.Tier2 A
where A.T2ID = @Tier
END
IF @Tier_ID = 1
Insert into @Output(URL)
select (select '/global/index.cfm/L1-'+cast(A.T1ID as varchar(10))+'/') AS URL
from dbo.Tier1 A
where A.T1ID = @Tier

RETURN
END

 

[murphyhg]

@gmmastros, thanks for your quick reply this is working.