Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS SQL 2005 :: Stored Procedure Looping - Output Value

Posted on 2008-10-14
3
Medium Priority
?
828 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 1200 total points
ID: 22718561
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 800 total points
ID: 22719389
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
ID: 22720817
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

Quick Cloud Training

Looking for some quick training on the cloud in 2 hours or less? Check out these how-to guides in AWS, Linux, OpenStack, Azure, and more!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

722 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