lapucca
asked on
How do I have a FILO queue for strings element in stored procedure?
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.
you have to re-explain because I don't really understand what you're doing here.
ASKER
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.
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
ASKER
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.
Also, how do I clear all data from this table for each time the end of my inner while loop?
Thank you.
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.Pare ntResearch Interest_i d as parentId
from dbo.ResearchInterests
WHERE dbo.ResearchInterests.Id = @interestChildId
END
--- @name will contain your name list ordered the way you want
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
from dbo.ResearchInterests
WHERE dbo.ResearchInterests.Id = @interestChildId
END
--- @name will contain your name list ordered the way you want
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
So, with each pass, @Name is subtracted with the left most token(name), right?
let me try this and see. Thank you.
Yes you are right @Name is substracted with the left most token
ASKER
Thank you.
"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.
Yes you can. SQL Server just doesn't provide you one. You have to write/find one.
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.
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.
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
Thanks Brandon it is an interresting way to do it
I never tried to use a function in a from to get a table
I never tried to use a function in a from to get a table