Functions keeps returning NULL

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)

Functions keeps returning NULL

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-28 : 18:51:12

I'm trying to create a function that outputs a 32 length string into uniqueindentifier format and another that returns a 32 length string by removing the -,{ and } from a uniqueindentifier. This code works grat on my asp page, but I decided to take this to the sql level. I'm having a problem with the function that formats the string to a uniqueidentifier. It only returns NULL! Why?

CREATE function FormatGUID
	(
	@GUID char(32)
	)
returns
	uniqueidentifier
as
begin
	declare @RetVal char(38)
	, @i int
	, @Char char(1)
	
	select @i = 1
	,@RetVal = '{'
	
	while @i < Len(@GUID)
	begin
		select @Char = substring(@GUID,@i,1)
		select @RetVal =
			case @i
				when 8 then @RetVal + @Char + '-'
				when 12 then @RetVal + @Char + '-'
				when 16 then @RetVal + @Char + '-'
				when 20 then @RetVal + @Char + '-'
				else @RetVal + @Char
			end
		select @i = @i + 1
	end
	
	select @RetVal = @RetVal + '}'
	
	return convert(uniqueidentifier,@RetVal)
end

- RoLY roLLs

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-28 : 19:05:00

Change your @RetVal to a varchar(38) and it works.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-28 : 19:08:04

hmm i get this:

Server: Msg 8169, Level 16, State 2, Procedure FormatGUID, Line 32
Syntax error converting from a character string to uniqueidentifier.

--EDIT1: Should I take out the convert?

--EDIT2: Nevermind...didn't work if I take out the convert.

- RoLY roLLs

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-28 : 19:13:31

I got this error too before I converted it to varchar.

The convert was trying to convert the string '{ ...' (ie { with 31 spaces) into a u/i. If you run the procedure as a normal script with a few 'print xxx' inserted in you can see what's happening. Always a good idea to debug stuff like this before creating the function/procedure.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-28 : 19:19:33

yeah, just saw this prob. fixed with this line 'while @i < Len(@GUID)' to 'while @i <= Len(@GUID)'

--Edit: but still getting the syntax error

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-28 : 19:23:52

quote:
Originally posted by timmy

Always a good idea to debug stuff like this before creating the function/procedure.

Correct, but I even did that change before i decided to post and still got a null returned, even without the converting, I was testing this on QA first untill I gave up and decided to post

- RoLY roLLs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-28 : 19:24:19

You need to debug this outside of a function as timmy mentioned. Something like this in Query Analyzer:

DECLARE @GUID varchar(32)
declare @RetVal varchar(38), @i int, @Char char(1)

select @i = 1
,@RetVal = '{'

while @i <= Len(@GUID)
begin
select @Char = substring(@GUID,@i,1)
select @RetVal =
case @i
when 8 then @RetVal + @Char + '-'
when 12 then @RetVal + @Char + '-'
when 16 then @RetVal + @Char + '-'
when 20 then @RetVal + @Char + '-'
else @RetVal + @Char
end
select @i = @i + 1
end

select @RetVal = @RetVal + '}'

PRINT @RetVal

Just add a bunch of PRINT statements so that you can visually inspect what's going on.

Tara

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-28 : 19:31:39

Ah...I found my problem. As I was testing, i was testing lower-case letters and numbers. But then I tried upper-case letters and numbers and works fine. Damn simple fixes!

- RoLY roLLs

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-28 : 19:35:14

How do you get a lower case number??? ;-)

Also - you can do without the curly braces. It works just as well without them. Saves you a few lines too

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 01:19:45

quote:
Originally posted by timmy

How do you get a lower case number??? ;-)

for testing purposes in QA i declared a variable and i assigned it 32 characters, i didn't grab it from anywhere

quote:
Originally posted by timmy

Also - you can do without the curly braces. It works just as well without them. Saves you a few lines too

yep, works that way too, but I said what the heck, i don't wanna run into any problems later on, even tho i'm sure i won't, but i don't know enough to know i don't have to use it. get confused yet

- RoLY roLLs

Kristen
Test

22859 Posts

Posted - 2004-07-29 : 02:00:29

Can't this more simply be done:


CREATE FUNCTION FormatGUID
(
	@inGUID	varchar(32)
)
RETURNS
	uniqueidentifier
AS
BEGIN
DECLARE	@outGUID	uniqueidentifier

	IF LEN(@inGUID) < 32
		-- Check for ONLY 0-9, A-Z and a-z (warning:allows through ASCII 128-255)
	   OR @inGUID LIKE '%['+CHAR(0)+'-/:-@G-Z\[-`g-z{-'+CHAR(127)+']%' ESCAPE '\'
	BEGIN
		SET @outGUID = NULL	-- Badly formed
	END
	ELSE
		SET @outGUID  = CONVERT(uniqueidentifier, 
				  SUBSTRING(@inGUID, 1, 8) + '-'
				+ SUBSTRING(@inGUID, 9, 4) + '-'
				+ SUBSTRING(@inGUID, 13, 4) + '-'
				+ SUBSTRING(@inGUID, 17, 4) + '-'
				+ SUBSTRING(@inGUID, 21, 12)
			   )
	END
	RETURN @outGUID
END

or am I missing something?

Kristen

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 02:33:20

that works great too. altho i wish i knew what "'%['+CHAR(0)+'-/:-@G-Z\[-`g-z{-'+CHAR(127)+']%' ESCAPE '\'" does.

- RoLY roLLs

Kristen
Test

22859 Posts

Posted - 2004-07-29 : 05:52:36

Its a set of illegal characters:

CHAR(0) - / is everything up to '0'

actually, what am I talking about, SQL does support "characters not in" so I think that whole complicatedness could be replaced with

@inGUID LIKE '%[^0-9A-Fa-f]%'

which says that @inGUID contains a character which is NOT one of 0-9, A-F or a-f

Kristen

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 12:56:02

quote:
Originally posted by Kristen

@inGUID LIKE '%[^0-9A-Fa-f]%'

I figured as much 'what' it does, but I meant more how does the syntax work. for example, I dunno what the '^' does.

- RoLY roLLs

Kristen
Test

22859 Posts

Posted - 2004-07-29 : 14:37:21

[abcd] = character set of a,b,c,d. Only true if MyThing matches that character.

this can be shortened to [a-d]

Put "^" as the first character for "Not", so MyColumn LIKE [^a-d] is true if MyColumn is a single character which is NOT a through d.

Genuine regular expressions have HEAPS more useful stuff - like "Zero or more occurences of" and "One or more occurences of" which makes it very easy to describe a number, even if it contains optional commas at the thousand break. But unfortuanately SQL Server only has the "%" anything, "_" any character and "[xyy]" / "[^xyz]" character sets.

Kristen

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 15:53:19

thanks kristen. i seen that kinda code on some jscripts, but never really researched how it works. thanks again

- RoLY roLLs

Kristen
Test

22859 Posts

Posted - 2004-07-30 : 01:59:11

Worth looking into if you want to validate data in javascript (say)

www.regexlib.com has a library of Regular Expressions for stuff like phone numbers, email addresses and so on. They are hard to understand (write-only!) but easy to use.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources