Web Development

Here is a procedure that will take the work order ID as a parameter, and return all the Parts concatenated as one string:

-------------------------------------------

CREATE PROCEDURE GetPartsAsText
@WorkOrderID numeric

AS

begin
declare @PartName varchar(50)
declare @AllParts varchar(1000)
declare @TempParts varchar(1000)

DECLARE ctblParts CURSOR FOR
SELECT PartName FROM tblParts WHERE WOID = @WorkOrderID

OPEN ctblParts

FETCH NEXT FROM ctblParts INTO @PartName

WHILE @@FETCH_STATUS = 0

BEGIN
PRINT @AllParts
SELECT @AllParts = RTRIM(ISNULL(@AllParts,'')) + RTRIM(@PartName) + ','
FETCH NEXT FROM ctblParts INTO @PartName
END

CLOSE ctblParts
DEALLOCATE ctblParts

SELECT SUBSTRING(@AllParts,1,LEN(@AllParts)-1)

end
GO

------------------------------------------

It works, I tested it myself.
You can either call this procedure for every record, or modify it to create a temp table and return the complete recorset with the new column added. Sadly, I was going to do it, but run out of time, he he.

Good luck!

This worked great. I was banging my head with this, new it could be done, but not experienced enough with stored procedures. Adding extra points for a great reply

Cheers

This question was closed by the author