Return Multiple Rows in Table-Valued Function 1

[larrydavid]

Hello,

I have a table-valued function in which I'm trying to return multiple rows per item code:

ALTER FUNCTION fn_listTestTiers
(
@dealItemCode varchar(12)
)
RETURNS @TestTiers TABLE
(
-- Columns returned by the function:
tierId int NOT NULL,
tierItem varchar(12) NOT NULL,
tierQty int NOT NULL,
tierAmt money NOT NULL
)
AS
-- Returns the tier qtys and amts for the specified item:
BEGIN
DECLARE
@tierId int,
@tierItem varchar(12),
@tierQty int,
@tierAmt money
-- Get item/deal/tier info:
SELECT
@tierId = qdaTierId,
@tierItem = @dealItemCode,
@tierQty = tierQty,
@tierAmt = tierAmt
FROM
TestTiers
WHILE (@dealItemCode IS NOT NULL)
BEGIN
INSERT @TestTiers
SELECT @tierId, @tierItem, @tierQty, @tierAmt
END
RETURN
END

The output I am expecting is three rows, here is the test query:

SELECT tierId, tierItem, tierQty, tierAmt
FROM dbo.fn_listTestTiers(012313213213)
GO

Here is the expected output:
17 031311600012 6 5.5500
18 031311600012 12 8.4500
19 031311600012 24 11.4500

The table has three rows of information I am trying to return in a select statement for a stored procedure, so I have dynamic columns for each tier in one row returned instead of hard-coding the columns.

The problem I'm having is if I test the function without the WHILE loop it returns one row, no good. If I test it with the WHILE loop it seems to be an endless loop and I have to break execution.

Any help would be greatly appreciated.

Thanks,
Larry

 

[gmmastros]

The problem is... your while condition will always be true because you are not doing anything inside the loop to change the condition.

WHILE (@dealItemCode IS NOT NULL) 
  BEGIN
    INSERT @TestTiers
    SELECT @tierId, @tierItem, @tierQty, @tierAmt	

    -- Put code in here to change the loop condition
    -- so that "@dealItemCode IS NOT NULL" will eventually
    -- be false
  END

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

 

[larrydavid]

Thank you George. This is what I did and it is working, same concept I guess:

ALTER FUNCTION dbo.fn_listTestTiers
(
	@dealItemCode varchar(12)
)
RETURNS 
@tmpTable TABLE 
(   
	tierId					int,
	tierItem				varchar(12),
	tierQty					int,
	tierAmt					money
)
AS
BEGIN

DECLARE 
	@tierId int, 
	@tierItem varchar(12), 
	@tierQty int, 
	@tierAmt money

DECLARE myCursor CURSOR FOR

	SELECT 
		qdaTierId,
		dealItemCode,
		tierQty,
		tierAmt
	FROM	
		TestTiers
	WHERE
		--dealItemCode = '031311600012'
		dealItemCode = @dealItemCode 
	Order By
		tierQty ASC 
		
OPEN myCursor

FETCH NEXT FROM myCursor INTO @tierId, @tierItem, @tierQty, @tierAmt 

WHILE (@@FETCH_STATUS = 0) 
BEGIN
	INSERT @tmpTable
	SELECT @tierId, @tierItem, @tierQty, @tierAmt	

    FETCH NEXT FROM myCursor INTO @tierId, @tierItem, @tierQty, @tierAmt 

END /*WHILE*/

CLOSE myCursor
DEALLOCATE myCursor

RETURN
END
[\CODE]

 

[gmmastros]

I'm a little confused about something. It appears as though you want the function to return all the data from a table that satisfies the condition "dealItemCode = @dealItemCode".

If this is true, then you should be able to do something like...

ALTER FUNCTION dbo.fn_listTestTiers
(
@dealItemCode varchar(12)
)
RETURNS TABLE 
AS
BEGIN
RETURN  (
    SELECT  qdaTierId,
            dealItemCode,
            tierQty,
            tierAmt
    FROM    TestTiers
    WHERE   dealItemCode = @dealItemCode 
    Order By tierQty ASC 
    )
END

If I am not mistaken, this function will return exactly the same data that your version returned. The difference is that this version will execute many times faster.

First of all, 99% of the time, when you use a cursor your performance will decrease (get slower). In all but a small number of cases, you should avoid cursors if an alternative exists.

Next, you should realize that not all functions are created equally. There are several categories of user defined functions:

1. Scalar Values Functions. Scalar functions return a single piece of data.
2. Multi-statement table valued functions. Your example of this query fits in to this category. Multi-statements is exactly what it sounds like, multiple TSQL commands combined to create a function.
3. inline table valued functions. My example of this query fits in to this category. with inline table valued functions, there is only 1 tsql statement within it. It can still return multiple columns and multiple rows, but it does so with a single query.

When you run code in SQL server, several things have to happen. The code is checked for syntax errors. It's then evaluated for an execution plan (the plan that SQL will use to execute the query). Finally, the code is executed. When you have a multi-statement function, SQL Server executes the code for each time it is called from an outer query. With an inline table function, sql server can combine the execution plans in to one, resulting in less calls and therefore better performance.

Bottom line, avoid cursors when you can and use inline table functions instead of multi-statement table valued functions when you can.

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