Solved

MS SQL 2005 :: Stored Procedure Looping - Output Value

Posted on 2008-10-14
3
820 Views
Last Modified: 2008-10-15
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

0
Comment
Question by:Marius0188
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 300 total points
Comment Utility
try this
ALTER PROCEDURE [dbo].[SP_TritonPartTypes] (
      @po varchar(25), @output varchar(255) output
)
AS
BEGIN
      DECLARE
            @orderout varchar(255)
 declare @output2 xml
      SET NOCOUNT ON;
      SELECT @output = 'NOT LOOPED';
 
set @output2 = (SELECT P.POrderNumber [text()]
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 for xml path(''))
set @output = cast(@output2 as varchar(255))
END
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 200 total points
Comment Utility
Well, what is there that will stop that "while" loop ?

You will need to put in some kind of termination for that while loop, or, change it around so it does not use the "find" as the loop condition ...

But it does not make a great deal of sense at the moment, because you are stringing together P.POrderNumber, which is a selection based on @po - which to me seems to say, just "repeat" @po for how ever many P.POrdernumber's exist...

Let's assume that it isn't the case, and that there is some kind of uniquess identifier in tblPrejig (if not, then do : alter table tblprejig add ID int identity;) For now we will call that uniqueness "ID" (or it could be a compound file like date + customer or something)...

e.g.


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  @foundcounter int,

		@orderout varchar(255),

                  @id int 

 

	SET NOCOUNT ON;

	SET @output = 'NOT LOOPED';

         SET @id = 999999

         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)
 

	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 <> @id)
 

		SELECT @output = @output + @orderout;

	END

END

GO
 
 

-- or if really just wanting to repeat p.PorderNumbers, then try:

if @foundcounter > 0 

   set @output = replicate(@po,@foundcounter)

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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

0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now