Help with T-SQL LEN() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with T-SQL LEN()

Author

Topic

waskelton4
Starting Member

12 Posts

Posted - 2006-03-08 : 11:29:34

Howdy group..

I'm working on a couple of functions that format code for display in my application and i'm having a little trouble.

I've found the LEN function that claims to do just what I'm looking for at the end of the functions but It doesen't seem to be acting as advertised..

Here is my code..


ALTER   FUNCTION dbo.getOutput  (@id bigint, @seperator varchar(5), @size int )  
RETURNS varchar(255) AS  
BEGIN 
-------------------------------------------------------------------------------------
DECLARE @retlength int, @seplength int, @state int,  @output varchar(255)

SELECT TOP 1 @state = autoStateID FROM tblState WHERE intVisitID = @id ORDER BY dtTime DESC
IF @state > 0
	BEGIN
		SELECT @output = @output+@seperator+RTRIM(LTRIM(m.chName))
		FROM  tblState s
		RIGHT OUTER JOIN link_StateName sd  on s.autoStateID = sd.intStateID
		LEFT OUTER JOIN tblName m on sd.intNameID = m.autoNameNum
		WHERE autoStateID = @state

		SET @drugs = SUBSTRING(@drugs, @size, 225)
	END

	

SELECT @retlength = LEN(@output)

IF @retlength = 0
	BEGIN
		SELECT @output = 'None'
	END


RETURN @output

END

I tried to include enough for you to see what i'm doing while not putting too much.
the main part i don't understand is the last Select down. What do I have wrong here?
TIA

Will

waskelton4
Starting Member

12 Posts

Posted - 2006-03-08 : 11:34:25

I guess I should have also said that..

If @output has data in it.. that data is output-ed correctly.. but if it doesn't.. there is nothing returned..

also.. I don't know T-SQL very well at all.. (which may be grossly apparent in the code

)

BAD SPELLERS OF THE WORLD UNTIE!!

Tahsin
Starting Member

34 Posts

Posted - 2006-03-08 : 12:13:28

You probably need to initialize the value of @output somewhere in your code. My thinking is that LEN(@output) will be equal to a residual value, which is why you are not seeing 'None' being returned, especially if @state < 0

waskelton4
Starting Member

12 Posts

Posted - 2006-03-08 : 12:41:36

thanks!

looks like that worked.
So when you say that @output is equal to a residual value.. do you mean that it contains whatever was in the memory space that @output now uses?

ws

BAD SPELLERS OF THE WORLD UNTIE!!

Tahsin
Starting Member

34 Posts

Posted - 2006-03-08 : 13:48:26

Yes, exactly ... since the data wasn't initialized, old data remained in @output

BTW, shouldn't your quote say "Dyslexics UNTIE"? Just a thought ;)

waskelton4
Starting Member

12 Posts

Posted - 2006-03-08 : 13:53:48

quote:
Originally posted by Tahsin

Yes, exactly ... since the data wasn't initialized, old data remained in @output

BTW, shouldn't your quote say "Dyslexics UNTIE"? Just a thought ;)

I suppose that would work too.. :)

BAD SPELLERS OF THE WORLD UNTIE!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources