Start Free Trial

asked on

MS SQL 2005 :: Stored Procedure Looping - Output Value

Dear Experts,

Please help me.
I need to create a stored procedure which takes one input parameter.
Then return all rows where equal to input parameter.
Loop the results while adding a certain field to a declared variable and after the loop return this variable as output parameter.

Please see my attempt below:

USE [DCSA_TRITON]
GO
/****** Object:  StoredProcedure [dbo].[SP_TritonPartTypes]    Script Date: 10/15/2008 08:12:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_TritonPartTypes] ( 
	@po varchar(25), @output varchar(255) output
)
AS
BEGIN
	DECLARE
		@orderout varchar(255) 
 
	SET NOCOUNT ON;
	SELECT @output = 'NOT LOOPED';
 
	WHILE EXISTS(SELECT P.POrderNumber FROM tblPrejig P LEFT OUTER JOIN tblParts PART ON Part.PartNumber
			= P.ItemNumber LEFT OUTER JOIN tblPartTypes PT ON PART.PartTypeID = PT.PartTypeID
		WHERE P.POrderNumber = @po AND PART.Deleted = 0 AND PT.Deleted = 0)
	BEGIN
		SELECT TOP 1 @orderout = P.POrderNumber FROM tblPrejig P;
		SELECT @output = @output + @orderout;
	END
END

Open in new window

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Just for the record - there was an error in my posting and cannot possible leave it like that (even after the event). momi_sabag's solution is pretty cool by the way.


alter PROCEDURE [dbo].[SP_TritonPartTypes] ( 
	@po varchar(25), @output varchar(255) output
)
AS
BEGIN
 
    DECLARE  @foundcounter int,
             @orderout varchar(255),
             @id int
 
    SET NOCOUNT ON;
    set @foundcounter = (SELECT count(*) 
                         FROM tblPrejig P 
                         LEFT OUTER JOIN tblParts PART ON Part.PartNumber = P.ItemNumber 
                         LEFT OUTER JOIN tblPartTypes PT ON PART.PartTypeID = PT.PartTypeID
                         WHERE P.POrderNumber = @po AND PART.Deleted = 0 AND PT.Deleted = 0)
 
    if @foundcounter > 0 
    begin
        print replicate(@po,@foundcounter)    
--could finish here if just wanting po repeated for number of finds ie set @output = replicate(@po,@foundcounter) look at messages tab.
        SET @output = ''
 
        WHILE @foundcounter > 0
        BEGIN
            SET @foundcounter = @foundcounter - 1
 
            SELECT top 1 @orderout = P.POrderNumber , @id = P.id
            FROM tblPrejig P 
            LEFT OUTER JOIN tblParts PART ON Part.PartNumber = P.ItemNumber 
            LEFT OUTER JOIN tblPartTypes PT ON PART.PartTypeID = PT.PartTypeID
            WHERE P.POrderNumber = @po AND PART.Deleted = 0 AND PT.Deleted = 0 and P.id <> isnull(@id,0)
 
            SET @output = @output + @orderout;
         END
    END
    ELSE SET @output = 'NOT LOOPED';
END
GO

Open in new window