Problem with function - 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 2005 Forums

Transact-SQL (2005)

Problem with function

Author

Topic

ernie99
Starting Member

12 Posts

Posted - 2008-12-03 : 08:43:51

hi everyone, please give me your advice on the following SQL. It's a function that is supposed to return a concatenated string created by looping through a set of rows. The problem is that the variable @PhaseTitle is supposed to be different for each row that is looped through but it isn't. Therefore the condition IF @PhaseTitle <> @PhaseList is never true and the code contained with the if statement never executes. I have checked the data and there are definately 3 unique rows that should be returned i.e. The end result should be "apple, orange, pear" instead if the concatenation code actually executed (which it won't because @PhaseTitle <> @PhaseList always equals false) I would get "apple, apple, apple". Here's the SQL

BEGIN
DECLARE @PhaseTitle VARCHAR(255)
DECLARE @PhaseList VARCHAR(255)
DECLARE @NumPhases INT
DECLARE @RowCount INT

SET @PhaseTitle = ''
SET @PhaseList = ''
SET @RowCount = 0

SET @NumPhases = (SELECT COUNT(*) FROM
(SELECT DISTINCT rd.Title
FROM dbo.OrganisationPhase op
INNER JOIN dbo.ReferenceData rd
ON rd.ReferenceId = 'f5ade029-d03b-44c1-97d9-184ef0a01582' AND
rd.ReferenceDataId = op.PhaseId
WHERE op.OrganisationId = '34A14C41-006F-4D4B-B416-28BE129AC88E') AS TotalPhases)

print @NumPhases

WHILE @RowCount <= @NumPhases
BEGIN
-- ==== SOMETHING WRONG WITH THIS SECTION ===
SET @PhaseTitle =
ISNULL((SELECT TOP 1 TopPhases.Title FROM
(SELECT TOP (6-@RowCount) AllPhases.Title FROM
(SELECT DISTINCT rd.Title
FROM dbo.OrganisationPhase op
INNER JOIN dbo.ReferenceData rd
ON rd.ReferenceId = 'f5ade029-d03b-44c1-97d9-184ef0a01582' AND
rd.ReferenceDataId = op.PhaseId
WHERE op.OrganisationID = '34A14C41-006F-4D4B-B416-28BE129AC88E') AS AllPhases
ORDER BY AllPhases.Title ASC) AS TopPhases ORDER BY TopPhases.Title DESC),'')
-- ======================================
SET @RowCount = @RowCount + 1

IF @PhaseTitle <> @PhaseList
SET @PhaseList =
LTRIM(RTRIM(@PhaseList)) +
CASE WHEN @PhaseList <> ''
THEN ', '
ELSE ''
END + LTRIM(RTRIM(@PhaseTitle))
END

PRINT @PhaseList
END

ernie99
Starting Member

12 Posts

Posted - 2008-12-03 : 08:57:26

Fixed, change line
SELECT TOP (6-@RowCount) AllPhases.Title FROM
to
SELECT TOP (@NumPhases-@RowCount) AllPhases.Title FROM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources