SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


String length?


String length?

Author
Message
werner.broser
werner.broser
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 166
This is partly true, because as usual conversion will take place, and if possible to convert the string to an integer it would be returned, otherwise an error occurs

kr/Werner
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5327 Visits: 3889
werner.broser (4/12/2010)
This is partly true, because as usual conversion will take place, and if possible to convert the string to an integer it would be returned, otherwise an error occurs

kr/Werner

We might need to start with the definition of output. It seems that you are referring to the output of the stored procedure. But I would consider the output of the SQL Batch from reading the question. And obviously the batch never outputs anything, as the return value is silently swallowed in the EXEC statement. But I agree that your understanding was probably more the intention of the author than mine.

Best Regards,

Chris Büttner
werner.broser
werner.broser
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 166
Yes, i wanted just to remark the comment from Bob, that a string cannot be returned in the return clause.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63520 Visits: 13298
A small question:

isn't the variable @InputStr stored somewhere? As the loop goes on forever, at some point in time that variable will become too large to reside in memory/physical location. So some sort of out of memory exception has to take place, right?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
werner.broser
werner.broser
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 166
In this sample not, because it is defined as varchar(16) and always truncated.

kr/werner
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5327 Visits: 3889
da-zero (4/12/2010)
A small question:

isn't the variable @InputStr stored somewhere? As the loop goes on forever, at some point in time that variable will become too large to reside in memory/physical location. So some sort of out of memory exception has to take place, right?


Try this to see whats happening under the hoods:
CREATE PROC #FixedLength 
@InputStr VARCHAR(16)
AS
WHILE LEN(@InputStr) < 16
BEGIN
SELECT @InputStr = @InputStr + ' '
PRINT '<' + @InputStr + '>'
END
RETURN @InputStr
GO
EXEC #FixedLength '154620';
GO



You need to cancel after one or two seconds to see the results.

Best Regards,

Chris Büttner
Daniel Bowlin
Daniel Bowlin
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8088 Visits: 2629
Good question. I took me a minute to reason out why it behaves as it does.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63520 Visits: 13298
werner.broser (4/12/2010)
In this sample not, because it is defined as varchar(16) and always truncated.

kr/werner


You're absolutely right, forgot about that one :-D


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Joseph D. Marsh
Joseph D. Marsh
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 60
THIS is why I play this silly QotD game -- today I learned something completely new about the LEN() function. Very Nice.

Thanks,
- Joseph Marsh
Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2225 Visits: 1817
This is a very good question, thank you. I did not have any problems answering after posting the datalength question expanding on the original question on len function by Ron Moses. It it took me a little bit of time to figure out that there are no issues with overflowing the input though. After the first 10 steps the input is simply right-padded with 10 blanks and further steps achieve nothing as there is a limit of 16 characters on the input definition, so the tight loop just keeps on going until it is killed since the queries never timeout in SSMS.

The question very cleverly shows how easy it is to introduce the tight endless loop in T-SQL, all it takes is not to think straight for a moment.

Oleg
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search