Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

String length? Expand / Collapse
Author
Message
Posted Monday, April 12, 2010 5:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 05, 2012 3:10 AM
Points: 101, 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
Post #901475
Posted Monday, April 12, 2010 5:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:19 AM
Points: 2,814, Visits: 3,851
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
Post #901490
Posted Monday, April 12, 2010 5:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 05, 2012 3:10 AM
Points: 101, Visits: 166
Yes, i wanted just to remark the comment from Bob, that a string cannot be returned in the return clause.
Post #901504
Posted Monday, April 12, 2010 6:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 12,208, Visits: 9,172
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #901537
Posted Monday, April 12, 2010 6:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 05, 2012 3:10 AM
Points: 101, Visits: 166
In this sample not, because it is defined as varchar(16) and always truncated.

kr/werner
Post #901542
Posted Monday, April 12, 2010 6:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:19 AM
Points: 2,814, Visits: 3,851
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
Post #901544
Posted Monday, April 12, 2010 6:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
Good question. I took me a minute to reason out why it behaves as it does.
Post #901565
Posted Monday, April 12, 2010 6:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 12,208, Visits: 9,172
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




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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #901568
Posted Monday, April 12, 2010 8:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, August 24, 2013 10:11 AM
Points: 83, 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
Post #901646
Posted Monday, April 12, 2010 9:34 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:53 PM
Points: 1,676, Visits: 1,744
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
Post #901712
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse