Need a UDF - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Need a UDF

Author

Topic

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-11-14 : 13:24:03

I need a UDF that concatenates multiple items to one variable.
Return value needs to be the last three status 'PXS'
The status column will be one alpha (not a specific alpha in no specific order).

Table = ChgDte
The records are:
key Seqno Status
1 1 A
1 2 B
1 3 C
1 4 S
1 5 S
1 6 X
1 7 P

My UDF looks like this:The @doc var being passed is 1

SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOALTER FUNCTION dw_f_getLast3Status (@doc varchar(3))returns varchar asBEGIN DECLARE @status varchar(3) SET @status = '' SELECT @status = rtrim(@status)+status FROM ChgDate WHERE Document = @doc order by seqno desc return @statusendGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

It returns:
Status
1
2
3
4
5
6
7

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 13:37:40

[code]CREATE FUNCTION dbo.dw_f_getLast3Status
(
@Doc VARCHAR(3)
)
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @Concat VARCHAR(3)

SET @Concat = ''

SELECT @Concat = @Concat + d.[Status]
FROM (
SELECT TOP 3 [Status]
FROM ChgDate
WHERE [Document] = @Doc
ORDER BY SeqNo DESC
) AS d

RETURN NULLIF(@Concat, '')
END[/code]

E 12°55'05.25"
N 56°04'39.16"

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-11-14 : 13:48:29

The above returns seven nulls.
I do not know how to get it to return the last three status in one variable.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 13:51:18

Huh? What do this test code return?

DECLARE	@Sample TABLE ([Key] TINYINT, SeqNo TINYINT, [Status] CHAR(1))

INSERT	@Sample
SELECT	1, 1, 'A' UNION ALL
SELECT	1, 2, 'B' UNION ALL
SELECT	1, 3, 'C' UNION ALL
SELECT	1, 4, 'S' UNION ALL
SELECT	1, 5, 'S' UNION ALL
SELECT	1, 6, 'X' UNION ALL
SELECT	1, 7, 'P'

SELECT * FROM @Sample

DECLARE	@Concat VARCHAR(3)

SET @Concat = ''

SELECT	@Concat = @Concat + d.[Status]
FROM	(
		SELECT TOP 3	[Status]
		FROM		@Sample
		WHERE		[Key] = 1
		ORDER BY	SeqNo DESC
	) AS d

SELECT	NULLIF(@Concat, '')

SET @Concat = ''

SELECT	@Concat = d.[Status] + @Concat
FROM	(
		SELECT TOP 3	[Status]
		FROM		@Sample
		WHERE		[Key] = 1
		ORDER BY	SeqNo DESC
	) AS d

SELECT	NULLIF(@Concat, '')

E 12°55'05.25"
N 56°04'39.16"

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-11-14 : 14:03:14

Your's returns the last three, but when I run it with my data I get 7 nulls.
My data looks like this.
Key seqno status
M618750468 1 S
M618750468 2 R
M618750468 3 A
M618750468 4 P

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 14:08:21

[code]CREATE FUNCTION dbo.dw_f_getLast3Status
(
@Doc VARCHAR(30)
)[/code]

E 12°55'05.25"
N 56°04'39.16"

dowens
Yak Posting Veteran

82 Posts

Posted - 2007-11-14 : 14:14:24

YES! It worked.
I have posted this in many forums.

You are the BEST!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources