Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL: How do I enable optional parameters on a recursive table valued funtion?

Posted on 2009-02-23
3
Medium Priority
?
399 Views
Last Modified: 2013-12-07
I have a recursive table valued function in SQL Server 2005 that currently works great.  Now I want to make the parameter optional.  If @ID is null I want to assign it a value from a select statement.  I have tried to insert the following if statement into the body of the function many different ways and I get always get multiple errors:

IF (@ID IS NULL) SET @ID = (SELECT TOP 1 ItemCode FROM ConfigDropDownLists WHERE ParentID IS NULL AND isMapAggPnt = 'true')

What is the proper way to do this?
ALTER FUNCTION [dbo].[GetAggPointAndChildCoordinates]
(
	@ID VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
	WITH Results AS
	(
	-- Anchor member definition
		SELECT cdl.ParentID AS ParentID, cdl.ItemCode AS ID, cdl.ListName AS [Type], cdl.ItemName AS [Name], cdl.Polygon, 1 AS [Level]
		FROM ConfigDropdownLists AS cdl
		WHERE cdl.ParentID = @ID AND cdl.IsMapAggPnt = 'True'
		UNION ALL
	-- Recursive member definition
		SELECT cdl.ParentID AS ParentID, cdl.ItemCode AS ID, cdl.ListName AS [Type], cdl.ItemName AS [Name], cdl.Polygon, [Level] + 1
		FROM ConfigDropdownLists AS cdl
		INNER JOIN Results AS r
			ON cdl.ParentID = r.ID
	)
 
	-- Statement that executes the CTE
	SELECT ParentID AS [ParentID], ItemCode AS ID, ListName AS [Type], ItemName AS [Name], 0 AS [Level],isnull(Polygon, dbo.GetAggregationPointPerimeterCSV(ItemCode)) AS Coordinates FROM ConfigDropdownLists WHERE ItemCode = @ID
	UNION ALL 
	SELECT ParentID, ID, [Type], [Name], [Level],isnull(Polygon, dbo.GetAggregationPointPerimeterCSV(ID)) AS Coordinates
	FROM Results
)

Open in new window

0
Comment
Question by:pureauto
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 23714628
Your function declaration is a Inline-table-valued function which cannot incorporate multiple sql statements.

You need a multi-statement-table-valued function, the template for which is below -

(If calling with a default parameter, you have to explicitly call the function with the keyword 'default' as show in example below the code)
CREATE FUNCTION [dbo].[GetAggPointAndChildCoordinates]
(
        @ID VARCHAR(50) = NULL
)
RETURNS @TableVar TABLE  (ParentId int, [Id] int, Type varchar(20), [Name] varchar(20), Level varchar(10), CoOrdinates varchar(10))
AS
BEGIN
	IF (@ID IS NULL) SET @ID = (SELECT TOP 1 ItemCode FROM ConfigDropDownLists WHERE ParentID IS NULL AND isMapAggPnt = 'true')
 
        WITH Results AS
        (
        -- Anchor member definition
                SELECT cdl.ParentID AS ParentID, cdl.ItemCode AS ID, cdl.ListName AS [Type], cdl.ItemName AS [Name], cdl.Polygon, 1 AS [Level]
                FROM ConfigDropdownLists AS cdl
                WHERE cdl.ParentID = @ID AND cdl.IsMapAggPnt = 'True'
                UNION ALL
        -- Recursive member definition
                SELECT cdl.ParentID AS ParentID, cdl.ItemCode AS ID, cdl.ListName AS [Type], cdl.ItemName AS [Name], cdl.Polygon, [Level] + 1
                FROM ConfigDropdownLists AS cdl
                INNER JOIN Results AS r
                        ON cdl.ParentID = r.ID
        )
 
        -- Statement that executes the CTE
	INSERT @TableVar
        SELECT ParentID AS [ParentID], ItemCode AS ID, ListName AS [Type], ItemName AS [Name], 0 AS [Level],isnull(Polygon, dbo.GetAggregationPointPerimeterCSV(ItemCode)) AS Coordinates FROM ConfigDropdownLists WHERE ItemCode = @ID
        UNION ALL 
        SELECT ParentID, ID, [Type], [Name], [Level],isnull(Polygon, dbo.GetAggregationPointPerimeterCSV(ID)) AS Coordinates
        FROM Results
 
	RETURN
END
GO
 
/* call the function as follows 
SELECT * FROM [dbo].[GetAggPointAndChildCoordinates] (default)
*/

Open in new window

0
 

Author Comment

by:pureauto
ID: 23714833
Thanks reb, that worked well.  I had experimented with the multistatement table valued functions, but apparently my syntax was off.
0
 

Author Closing Comment

by:pureauto
ID: 31550249
thanks again.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question