recursive Stored procedure or function

Question

Post reply

recursive Stored procedure or function

Eskandari

Ten Centuries

Points: 1078
More actions
March 5, 2014 at 10:41 pm

#304964

is there any way to have a stored procedure or function like below c# code:
Comment :"i want to convert this c# code to sql server t-sql "
public static IEnumerable<String> GetWords(Int32 length)
{
if (length <= 0)
yield break;
for (Char c = '!'; c <= '~'; c++)
{
if (length > 1)
{
foreach (String restWord in GetWords(length - 1))
yield return c + restWord;
}
else
yield return "" + c;
}
}

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 1

Try recursive TVF

create function testrec (@l int )

returns @t table

(c varchar(100))

as

begin

if @l > 0

begin

insert @t

select z.c + isnull(x.c,'')

from

(select top (ascii('~') - ascii('!') +1)

char (ROW_NUMBER() over (order by (select null)) + ascii('!') - 1 ) c

from sys.all_columns ) z

left join testrec(@l-1) x on 1=1;

end

return

end

go

select * from testrec (2)

But guess building dynamic plain join may have better perfomance

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Eskandari,

Sorry but I don't speak C#. What is the desired result?

Are you just trying to identify the strings between "!" and "~" ??

If I were to pass in the string "abc!def~ghi!jkl~mnop" what should the output look like?

I'm asking because the best SQL solution often bears no resemblance to the procedural code found in your C# routine.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Dwain Camps SSC Guru Points: 86978 More actions · Answer 3

Scratch that previous post. Coffee deficit disorder.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

OP's c# code generates all the words of given length L consisting of characters from the set {'!',..,'~'} .

When L<=0 it generates empty set of words.

Eskandari Ten Centuries Points: 1078 More actions · Answer 5

Eskandari

Ten Centuries

Points: 1078

March 7, 2014 at 12:12 am

#1694983

c# code generates all the words of given length L

Nevyn SSChampion Points: 14210 More actions · Answer 6

Are you looking for a solution to this specific problem, or are you looking specifically to use recursion? And do you need all the output words in a table, or in query output?

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 7

Eskandari (3/5/2014)
is there any way to have a stored procedure or function like below c# code:
Comment :"i want to convert this c# code to sql server t-sql "
public static IEnumerable<String> GetWords(Int32 length)
{
if (length <= 0)
yield break;
for (Char c = '!'; c <= '~'; c++)
{
if (length > 1)
{
foreach (String restWord in GetWords(length - 1))
yield return c + restWord;
}
else
yield return "" + c;
}
}

Interesting. If Length=8, then result = 6.1 Quadrillion Values. That makes for one helluva rainbow table. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

Like Jeff said, you want a lot of time and disk space to go over 5 in length. However, this problem is what CROSS JOINS in SQL do well.

Just for fun, I threw together a recursive CTE that does a series of CROSS JOINS and compared it to a dynamic SQL solution that builds the appropriate number of CROSS JOINS and runs once. The dynamic SQL solution is by far the fastest. (See attachment.) Someone else may find a more elegant solution. Best of luck.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills