UDF Syntax wrong

[murphyhg]

I am not familiar with the syntax to make this UDF work. I have verified that the select statements work just fine. What am I doing wrong? Thanks for your help.

CREATE FUNCTION [dbo].[getContentURL]
(
@Tier int, -- this is the ID of the location
@Tier_ID int -- this is what level the article is attached

)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here

BEGIN
IF @Tier_ID = 3
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
IF @Tier_ID = 2
BEGIN
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
select A.T1ID, (select '/global/index.cfm/L1-'+cast(A.T1ID as varchar(10))+'/') AS URL
from dbo.Tier1 A
where A.T1ID = @Tier
END
)
GO

 

[markros]

You didn't specify the structure of the table in your UDF definition.

 

[gmmastros]

There are a couple problems here.

When you want to create a table-valued UDF, you need to declare what the output table looks like. Ex:

CREATE FUNCTION [dbo].[getContentURL]
(    
    @Tier int, -- this is the ID of the location
    @Tier_ID int -- this is what level the article is attached
    
)
RETURNS [!]@Output[/!] TABLE[!](URL VarChar(1000))[/!]
AS

Notice how I gave it a name (@Output). In the UDF, you need to insert your data in to this output table. For Example:

		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

I also noticed that your last condition @Tier_ID = 1, you returned 2 columns where you only return one column in the other 2 conditions. You need to be consistent.

Lastly, the return goes at the end. Like this:

CREATE FUNCTION [dbo].[getContentURL]
(    
    @Tier int, -- this is the ID of the location
    @Tier_ID int -- 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 (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
    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

GO

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[murphyhg]

Thank you so much for the quick reply. It works quite nicely. I will keep that in mind about declaring the output table.

 

[markros]

Yes, I wanted to post this link I found before I was interrupted

http://www.sqlteam.com/article/intro-to-user-defined-functions-updated