when error - catch it - 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

SQL Server Development (2000)

when error - catch it

Author

Topic

noamg
Posting Yak Master

215 Posts

Posted - 2005-03-17 : 09:59:20

any idea how to catch an error in the sp, like try and catch of C++ ?
for example:
select convert( uniqueidentifier, '6D40AB13-D133-46DD-986B-CDEA74B17C11' )

Noam Graizer

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-17 : 10:37:05

until sql comes out with try-catch (which I understand they will), you have to anticipate what could raise an error and test for your condition before making the attempt. For your particular example, I wrote a udf (a long time ago - so be kind people) that I use to test a single value before I try to use at as a uniqueidentifier: (this is designed to work on a single value rather than be used in a select list)


If Object_ID('dbo.fnIsGuid') > 0
	Drop Function dbo.fnIsGuid
GO

Create Function dbo.fnIsGuid(@Guid varChar(128)) returns bit

as

Begin
	--Guid format: '22D6CE78-8DBF-426D-8911-337A7277665D'

	declare @i tinyint

	--if first and last characters are curly braces
	--get rid of them
	set @Guid = replace(replace(@Guid, '{', ''), '}', '')

	--uniqueidentifier converts to char(36)
	if len(isNull(@Guid,'')) <> 36
		return 0

	set @i = 1

	while @i < 37 
	Begin
		if @i IN (9,14,19,24)
		Begin
			if subString(@Guid, @i, 1) <> '-'
				return 0
		End
		else if charindex(subString(@Guid, @i, 1), '0123456789ABCDEF') = 0
			return 0

		set @i = @i + 1
	End
	return 1
End

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-17 : 10:59:33

I'm sure someone will do this so I'll beat them to it:
non-looping version


If Object_ID('dbo.fnIsGuid') > 0
	Drop Function dbo.fnIsGuid
GO
Create Function dbo.fnIsGuid(@Guid varChar(128)) returns bit
as
Begin
	--Guid format: '22D6CE78-8DBF-426D-8911-337A7277665D'

	--if first and last characters are curly braces
	--get rid of them
	set @Guid = replace(replace(@Guid, '{', ''), '}', '')

	if @Guid not like '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
			+ '-' + '[0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
			+ '-' + '[0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
			+ '-' + '[0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
			+ '-' + '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
	return 0
	return 1
End

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources