Solved

How do I have a FILO queue for strings element in stored procedure?

Posted on 2008-10-16
13
593 Views
Last Modified: 2012-05-05
I have a while loope in my query that get a string value in each loop.  I want to store these strings in a queue and then access them when the loops ends in a reverse order, first-in-last-out.  How can I do this in SQL?  Also, can I code T-SQL in query or does it have to be a stored procedure?   Thanks.
0
Comment
Question by:lapucca
[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
  • 5
  • 5
  • 3
13 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22736897
you have to re-explain because I don't really understand what you're doing here.
0
 

Author Comment

by:lapucca
ID: 22736992
In this while loop, I would like to store the dbo.ResearchInterests.Name retrived in ecah loop some where in an array or queue.  This code of course has to be inside of the while loop.  For example, I got names total back when the while loop is finished, John, Mary, Nancy, Henry.
I want to get to these names in a reverse order.  The frist one poped or retried from the arry or queue should be Henry, then Nancy, Mary and John.  
I hope this is more clear of what I want to achieve.  Thank you.
	WHILE(parentId <> NULL)--USE THE PARENTID TO TRAVERSE TO ITS PARENT NODE UNTIL REACHES ROOT NODE
	BEGIN
		@interestChildId = parentId
		SELECT dbo.ResearchInterests.Name, dbo.ResearchInterests.ParentResearchInterest_id as parentId
		from dbo.ResearchInterests
		WHERE dbo.ResearchInterests.Id = @interestChildId
	END

Open in new window

0
 

Author Comment

by:lapucca
ID: 22737115
Ok, what if I just insert this into a table of one column.  What's the code like to read this column table backward to retrieve the data?
Also, how do I clear all data from this table for each time the end of my inner while loop?

Thank you.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 5

Expert Comment

by:jfmador
ID: 22737593
You can declare a varchar before looping and adding the value to the variable

DECLARE @name as VARCHAR(1000)
SET @Name = ''

      WHILE(parentId <> NULL)--USE THE PARENTID TO TRAVERSE TO ITS PARENT NODE UNTIL REACHES ROOT NODE
      BEGIN
            SET @interestChildId = parentId
            SELECT @Name = dbo.ResearchInterests.Name + ', ' + @Name, dbo.ResearchInterests.ParentResearchInterest_id as parentId
            from dbo.ResearchInterests
            WHERE dbo.ResearchInterests.Id = @interestChildId
      END
                     --- @name will contain your name list ordered the way you want
0
 

Author Comment

by:lapucca
ID: 22737713
That's a great idea.  Now, could I trouble you for how to access each of the name?  I need to get one at a time and write it to a row in a table.  I know how to do that in C# but has forgotten in sql.  Thank you so much.
0
 
LVL 5

Accepted Solution

by:
jfmador earned 500 total points
ID: 22737759
You cannot use a split function like in c# but you will be able to do it using CHARINDEX AND LEFT

DECLARE @FirstName as varchar(100)
WHILE (len(@name) > 0)
BEGIN
    if (CHARINDEX(',',@Name) > 0)
    BEGIN
        SET @FirstName = LEFT(@Name,CHARINDEX(',',@Name))
        SET @Name = SUBSTRING(@Name,CHARINDEX(',',@Name) + 2, LEN(@Name) - CHARINDEX(',',@Name) -2)   -- THERE is maybe some ajustement to do here with the +2 and minus 2 to remove the comma and the blank space, I didn't try this code
    END
    ELSE
    BEGIN
       SET @FirstName = @Name
       SET @Name = ''
   END
  --- here you have @FirstName containing the first name of @Name and @Name now have left the first name until it is empty
END

0
 

Author Comment

by:lapucca
ID: 22737810
Im a bit stunned on how difficult it seems to get the name "token" out of @name.  I'm starting to wonder should I insert each of the name to a sinle column temp table?  
So, with each pass, @Name is subtracted with the left most token(name), right?
let me try this and see.  Thank you.  
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22739612
Yes you are right @Name is substracted with the left most token

0
 

Author Comment

by:lapucca
ID: 22739687
Thank you.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22740232
"cannot use a split function like in c#"

Yes you can.  SQL Server just doesn't provide you one.  You have to write/find one.
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22740614
humm... I'm sorry Brandon but how do you write a function that will return a string array using SQL ?

maybe create a stored procedure that will return a table... but the code in this function will look like the one that I post earlier...

If you have another way i'm curious to learn something new.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22743056
Here's a little one I put together
if object_id('[dbo].[fn_DelimitedToTable]') is not null
     drop function [dbo].[fn_DelimitedToTable]
go
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString) 
*                   on a delimiter (@delimiter) and outputs 
*                   a table of values.
*    
*
*************************************************************/
begin
 
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
		from	dbo.vw_Nums
		where
			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 
 
 
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable]('a','|')
select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')
*/
GO

Open in new window

0
 
LVL 5

Expert Comment

by:jfmador
ID: 22743931
Thanks Brandon it is an interresting way to do it
I never tried to use a function in a from to get a table
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

738 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