I'm new to the forum and would like to post a problem which I'm trying to resolve.
I need to concatenate a field from a certain number of rows and created a function to return the concatenated value. Here's the function. I'm passing an ID and based on that ID, the function returns a string. However, when I tested the function it's giving me a null.
--Calling syntax:: Select dbo.getIncidentNotes(187714) as 'Notes'
--Function to get all the latest notes for an incident
--Use for report: weekly_prospect_status
CREATE FUNCTION dbo.getIncidentNotes(@iIncidentID int)
DECLARE @allnotes varchar(8000)
DECLARE @seqnotes varchar(255)
DECLARE @seqnum int
DECLARE @counter int
SELECT @seqnum = max(iseqnum) from dbo.frs_weekly_prospect_status2 where iIncidentId=@iIncidentID
WHILE (@COUNTER <=@seqnum)
SELECT @seqnotes = workNoteALL from dbo.frs_weekly_prospect_status2 where iIncidentId=@iIncidentID and iSeqNum=@counter
SELECT @allnotes = @allnotes + @seqnotes
SELECT @COUNTER = @COUNTER + 1
END --While Begin
Can someone please tell me what's wrong with the code?