September 10, 2014 at 8:52 am
After seeing the code , I checked the options for answers, when I saw the option, "display shiva 2 times"
some how I felt this is the answer
By any chance do you wanted your name to be displayed in the everyone's result set who ever tries this quesion 😛
September 10, 2014 at 8:57 am
Nice question, Shiva, although it is not something that you would expect to see (hopefully:-)) in the real world. It's an interesting brain teaser, though.
It is interesting to note that BOL doesn't seem to document the maximum nesting level for WHILE loops (at least not that I could easily find).
September 10, 2014 at 2:09 pm
I fail to see the point of this question.
I personally think the maximum nesting depth of 160-odd is completely irrelevant. Rewrite your code, long before you ever hit this.
But if you do think that this is an interesting fact for the QotD, then why not simply submit a question "what is the maximum nesting level for WHILE", optionally add this script in the explanation section, and definitely add a link to whereever this maximum is documented. This script is just hurting the eyes - especially because the newlines in the strings and the lack of the color coding that SSMS normally gives me makes it almost impossible to see what commands are enclosed within the string and which are not.
Oh and then some comments on the script itself:
* Please don't prefix column and variable names with the data type. That might perhaps (I don't know, but I am willing to accept claims) be useful in Visual Basic or C#, but it is pure nonsense in SQL. Imagine what happens if you have a tinyint column GenderCode that uses ISO/IEC 5218 standard codes for storing genders, but you need to implement support for the CID 7455 coding, so the column becomes varchar(6). That change in itself a already a royal pain, but at least I do not need to change any code that merely includes the column in select lists; and I only need to change the datatype definition for variables and temporary tables in code that does not include specific constant values. Imagine how much more work I would have had if the column had been named intGenderCode and now has to be renamed to strGenderCode across all my code!
* Be careful with upper and lower case. On a lot of computers, the actual result of running the code would be a long list of "Must declare the scalar variable "@intloopCounter", because the name of the variable in the DECLARE is "intLoopcounter", not "intloopCounter".
September 11, 2014 at 7:18 am
Brought a smile to my face... thanks!
While I agree with previously commented sentiment that these are purposefully confusing questions and therefore should be avoided, I also agree with the interesting way to look at this particular issue. The real issue of how many nested loops are permitted is obfuscated, but I've also faced this type of code so there was real-world application as well.
In the end, this is one of those overly complicated bits of code that I find particular joy in re-factoring and simplifying: SELECT 'Shiva'; SELECT 'Shiva';
September 16, 2014 at 10:14 am
Good to see the question but got it wrong. Then I took it to the SQL Server and it operated exactly as I thought it should. Must be something as to how the sql was written and posted.
The real value in this is that I need to insure when I look at other people's work that I am certain that I understand it and if there is a problem go and ask them about it if possible.
Thank you!
Not all gray hairs are Dinosaurs!
September 30, 2014 at 10:03 am
I couldn't get PRINT to show the entire script. This really bugged me -- I had to see it. For those who are also morbidly curious about trivial SQL challenges, I offer a small rework of Shiva's code that will show you the entire script that is generated, in pretty printed format, just in case you don't have Red Gate's SQL Prompt:
DECLARE @intLoopCounter INT = 2
, @intMaxLoop INT = 162
, @strbegin VARCHAR(MAX) = '' -- Note the change to MAX
, @strend VARCHAR(MAX) = ''
, @intDecre INT
, @ResultStr VARCHAR(MAX)
, @BeginIndentStr VARCHAR(8000) = ''
, @EndIndentStr VARCHAR(8000) = ''
SET @intDecre = @intMaxLoop
WHILE @intLoopCounter <= @intMaxLoop
BEGIN
SET @EndIndentStr = SPACE((@intDecre-2)*3) -- 3 spaces per indent level
SET @strbegin = @strbegin + @BeginIndentStr +
'WHILE @intLoopCounter <= ' + CAST(@intDecre AS VARCHAR) + '
'
+ @BeginIndentStr +
'BEGIN
'
SET @strend = @strend + @EndIndentStr +
' SET @intLoopCounter=@intLoopCounter+1
'
+ @EndIndentStr +
'END
'
SET @intLoopCounter = @intLoopCounter + 1
SET @intDecre = @intDecre - 1
SET @BeginIndentStr = @BeginIndentStr + ' '
END
/* PRINT truncates the output. Stuff the text into the @ResultsStr var and ship it to XML.*/
-- uncomment to Print your generated query
--PRINT 'DECLARE @intLoopCounter INT=1
--' + (@strbegin) + '
--SELECT ''Shiva'''
--PRINT (@strend)
SET @ResultStr =
'-- While Loop Test
DECLARE @intLoopCounter INT=1
'
+ @strbegin +
@BeginIndentStr +
'SELECT ''Shiva''
'
+ @strend
-- String too long to PRINT completely--Use XML instead
select @ResultStr as [processing-instruction(x)] FOR XML PATH
-- Run the generated dSQL
EXEC (
'-- While Loop Test
DECLARE @intLoopCounter INT=1
'
+ @strbegin +
@BeginIndentStr +
'SELECT ''Shiva''
'
+ @strend
)
This was a very amusing diversion. Thanks, Shiva.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply