Code generation

Question

Code generation

Viewing 7 posts - 31 through 36 (of 36 total)

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

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 1

Cool stuff! I definitely learn a lot from Jeff's code. So thanks Jeff!

It might be nice to change this very slightly:

,
cteCharacters AS
( --=== This is just the list of characters that we have to chose from.
-- Vowels and certain other characters have been removed to avoid confusion.
-- Note that both strings must be absolutely identical here.
SELECT LEN('BCDFGHJKLMNPQRSTVWXYZbcdfghjkmnpqrstvwxyz23456789') AS CharLen,
'BCDFGHJKLMNPQRSTVWXYZbcdfghjkmnpqrstvwxyz23456789' AS Characters
)

To use a variable (to ensure same string):

DECLARE @charsToBeUsed varchar(100);

SET @charsToBeUsed = 'BCDFGHJKLMNPQRSTVWXYZbcdfghjkmnpqrstvwxyz23456789'

,

cteCharacters AS

( --=== This is just the list of characters that we have to chose from.

-- Vowels and certain other characters have been removed to avoid confusion.

-- Note that both strings must be absolutely identical here.

SELECT LEN(@charsToBeUsed) AS CharLen,

@charsToBeUsed AS Characters

)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

mister.magoo (5/13/2010)
Brilliant answer Jeff!
I like this a lot, but every time I see NEWID() used like this it just makes me yearn for a decent random number generator so that you could just
select top 1600000 Number from RandomNumbers
:unsure:

Thanks Magoo... and I agree... even if the RAND function worked correctly would make life a whole lot easier.

Hmmmm... you may have given me an idea for a new "useful" function. And, contrary to popular belief, it IS possible to be able to use RAND and NEWID from a function... just drop it into a View and call the View from the function.

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Adam Gojdas (5/13/2010)
Cool stuff! I definitely learn a lot from Jeff's code. So thanks Jeff!
It might be nice to change this very slightly:
,
cteCharacters AS
( --=== This is just the list of characters that we have to chose from.
-- Vowels and certain other characters have been removed to avoid confusion.
-- Note that both strings must be absolutely identical here.
SELECT LEN('BCDFGHJKLMNPQRSTVWXYZbcdfghjkmnpqrstvwxyz23456789') AS CharLen,
'BCDFGHJKLMNPQRSTVWXYZbcdfghjkmnpqrstvwxyz23456789' AS Characters
)
To use a variable (to ensure same string):
DECLARE @charsToBeUsed varchar(100);
SET @charsToBeUsed = 'BCDFGHJKLMNPQRSTVWXYZbcdfghjkmnpqrstvwxyz23456789'
,
cteCharacters AS
( --=== This is just the list of characters that we have to chose from.
-- Vowels and certain other characters have been removed to avoid confusion.
-- Note that both strings must be absolutely identical here.
SELECT LEN(@charsToBeUsed) AS CharLen,
@charsToBeUsed AS Characters
)

Thanks for the feedback and good idea but the reason why I did it the way I did is just in case someone wants to turn the code into an iTVF (inline Table Valued Function) or a View. I suppose it would be just as easy to add yet another CTE to the "stream" of CTEs in the code but I wouldn't use a variable on this.

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Heh... typical... OP wants a "miracle" and gets it. OP is never heard from again especially if you ask a favor in return. Oh well... next problem, please. 😛

--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)

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

No problem here, Jeff!

Your miracle is in my "cool sql stuff" folder, it won't get wasted!

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Gianluca Sartori (5/17/2010)
No problem here, Jeff!
Your miracle is in my "cool sql stuff" folder, it won't get wasted!

Heh... thanks, Gianluca. :blush:

--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)

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 7

Jeff Moden (5/16/2010)
Thanks for the feedback and good idea but the reason why I did it the way I did is just in case someone wants to turn the code into an iTVF (inline Table Valued Function) or a View. I suppose it would be just as easy to add yet another CTE to the "stream" of CTEs in the code but I wouldn't use a variable on this.

Understandable. But I think in the case of the iTVF you could use a parameter passed to it which would be used in place of this variable to get the same result. And for the View you might create a function that has the string returned and then the view uses it where it wants by calling the function. My thought was just to ensure the same exact string is utilized. I see potential for error with the hardcoded string in 2 or more places. I like to avoid that kind of thing when possible. Although in this case it might not be as much of a threat but why leave the possibility.