@mynkdby,

I've been trying to figure out why you wanted to avoid a CTE for this and I believe I've come up with the answer. Since you're on a quest for knowledge, I thought I'd throw some additional info at you on the subject.

There are two different types of CTEs... recursive and non-recursive. Recursive CTEs (rCTE) that count out a sequence of numbers are horrible for performance and resource usage. You can find out more about that in the following article.

[font="Arial Black"]Hidden RBAR: Counting with Recursive CTE's

[/font][/url]

The bottom line is that you should pretty much never use a recursive CTE that counts.

Then, there's a form of non-recursive CTE first documented by Itzik Ben-Gan. I don't know what anyone else calls it but I call it a "Cascading CTE" or "cCTE" for short. This is a very high performance, low resource usage method for generating sequences of numbers for splitting delimited values and a whole bunch of other uses. You can see such a cCTE in action in the DelimitedSplit8K function that you were previously directed to. It's nearly as fast as using a Tally Table and uses zero reads. You can find out more about both a cCTE and a Tally Table and how they are both high performance replacements for certain types of loops in the following article. DO read the intro to that article which explains that the "splitter method" in the article is ineffecient when it comes to splitting delimited values but was included because it easily explains how a Tally Table works.

[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

[/font][/url]

Getting back to the original subject, it's flat out dangerous to create dynamic SQL from character based parameters that came from "the public" because those parameters can easily contain SQL Injection methods as I previously demonstrated on your other thread. If you haven't Googled "SQL Injection" and done a bit of a study on the subject, you could be leading the folks you're working for into a hack-attack.

If you have any other questions, please don't hesitate to ask.