What is the best solution to this problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

What is the best solution to this problem

Author

Topic

brado38501
Starting Member

4 Posts

Posted - 2009-03-18 : 10:52:47

I have a table of purchase order items. I have a separate table that can hold up to 10 lines of additional description data (max 35 characters per line) for each item in the purchase order items table. I need to design a query that will combine all the additional description lines into a single varchar text string and return that with each purchase order item.

I have been attempting to use a cursor inside a scalar user defined function but it does not return any data. I can take the same code and place it in a stored procedure and it works. Below is the code I have for my user defined finction. What am I missing here?

ALTER FUNCTION [dbo].[GetOpenDescription]
(
-- Add the parameters for the function here
@TransactionNum varchar(8),
@EntryNum varchar(3)
)
RETURNS varchar(360)
AS
BEGIN
-- Declare the return variable here
DECLARE @Desc varchar(360)
DECLARE @Line varchar(35)

-- Add the T-SQL statements to compute the return value here

select @Desc = NULL
-- create the cursor that will step through the descriptions
declare crsReader cursor fast_forward for
select DescriptionLine
from PODE
where TransactionNum = @TransactionNum and EntryNum = @EntryNum
order by SequenceNum

-- open the cursor
open crsReader

-- begin reading the data
fetch next from crsReader into @Line

-- loop while we have data
while @@fetch_status = 0
begin
if @Desc = NULL
begin
select @Desc = @Line
end
else
begin
select @Desc = @Desc + ' ' + @Line
end
-- get the next record
fetch next from crsReader into @Line
end

-- get rid of the cursor
close crsReader
deallocate crsReader

-- Return the result of the function
RETURN @Desc

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 10:54:09

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
No need for CURSOR at all...

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 10:56:39

Something similar to this

ALTER FUNCTION dbo.GetOpenDescription 
(
	@TransactionNum VARCHAR(8),
	@EntryNum VARCHAR(3)
)
RETURNS VARCHAR(360)
AS
BEGIN
	RETURN	STUFF(
			(
				SELECT		' ' + DescriptionLine
				FROM		PODE
				WHERE		TransactionNum = @TransactionNum
						AND EntryNum = @EntryNum
				ORDER BY	SequenceNum
				FOR XML		PATH('')
			), 1, 1, ''))
END

E 12°55'05.63"
N 56°04'39.26"

brado38501
Starting Member

4 Posts

Posted - 2009-03-18 : 11:21:53

Thanks for the quick reply. That did the trick. Since I am a newbie to all this, can you break down the select statement you used so I can understand how it works?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 11:38:34

Start with reading Books Online and the section about FOR XML.

E 12°55'05.63"
N 56°04'39.26"

brado38501
Starting Member

4 Posts

Posted - 2009-03-18 : 12:22:27

Ok. I understand what you did. I assume that the STUFF function is more efficient than using LTRIM to get rid of the leading space in the result?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 15:16:35