October 8, 2002 at 4:52 am
Is there a simple way to anticipate a doomed CAST operation, or trap the resulting error, clear the error and exit?
In context ...
I am passing data of type uniqueidentifier as a parameter in a VB ADO Command. I prefer to store this parameter as a string on the VB side
(i.e. '111BB228-66CE-4A72-85D5-0A61F38C961')
and so really I am passing a string parameter, which I convert in the stored procedure, e.g.
CREATE PROCEDURE up_wwwValidateSessionGUID @vcrSessionGUID varchar(36)
AS
DECLARE @guidSessionGUID as uniqueidentifier
SET @guidSessionGUID=CAST(@vcrSessionGUID as uniqueidentifier)
SELECT ...
-- Do other stuff (if CAST succeeds)
It works fine as long as the string can be cast as a valid uniqueidentifier, but if the string is invalid, an error is generated (reassuringly!).
<Server: Msg 8169, Level 16, State 2, Procedure up_wwwValidateSessionGUID, Line 16
Syntax error converting from a character string to uniqueidentifier.>
In this context I'ld be happy for the s.p. to terminate and return nothing after such a failure, but I'ld like to clear the error first. Even better would be to anticipate the error and exit.
I could validate the uniqueidentifier string on the client side, or I could just clear the error when it is returned to the calling vb, but I suspect there is a cleaner way (?)
As an aside, one reason why I prefer to maintain all the error handling logic in the s.p is that it's a security token being passed and I'ld prefer that the outcome is either success or failure with no explanatory information.
Cheers
Will
October 8, 2002 at 4:55 am
Internal to SQL the answer is no. You will need to build that logic into the application itself to check for an error return code and message. Then you decide based on that how to fix and resubmit in the application.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 8, 2002 at 5:02 am
In response to myself (inspiration strikes too late again!) ....
I guess one way to validate a uniqueidentifier field is to test thus, but anyone know a cleaner more generic way (that might work for other datatypes etc.)
IF @vcrGUID LIKE '
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[-]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[-]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[-]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[-]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
[A-Z,1-9][A-Z,1-9]
'
...
October 8, 2002 at 5:04 am
Thanks Antares - I couldn't find any way to do it in the documentation, but without your reply I would have wasted plenty of time looking for it here there and everywhere -
Cheers
PS (didn't see your post before I wrote back to myself).
October 16, 2002 at 2:45 am
I have the same problem but I really need to anticipate the error in the S.P.
This is the function I wrote based in planet115 self reply.
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'varchar36isuniqueidentifier')
DROP FUNCTION varchar36isuniqueidentifier
GO
CREATE FUNCTION varchar36isuniqueidentifier
(@cadena varchar(36))
RETURNS bit
AS
BEGIN
declare @error bit
IF (@cadena LIKE '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][-][A-F0-9][A-F0-9][A-F0-9][A-F0-9][-][A-F0-9][A-F0-9][A-F0-9][A-F0-9][-][A-F0-9][A-F0-9][A-F0-9][A-F0-9][-][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]')
set @error = 1
else
set @error = 0
RETURN @error
END
GO
-- =============================================
-- Example to execute function
-- =============================================
declare @GUID uniqueidentifier
,@cad varchar(36)
set @GUID = newid()
set @cad = CAST (@guid as varchar(36))
select @GUID
SELECT dbo.varchar36isuniqueidentifier('lo que sea')
SELECT dbo.varchar36isuniqueidentifier(@cad)
GO
--Thanks planet115 for the inspiration.
October 16, 2002 at 6:56 am
This is a bit neater
like replicate('[A-F0-9/-]',36)
You needed to check for the - as well, or you could replace them with nothing.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 16, 2002 at 8:11 am
Simon
Thanks for the suggestion. However, unless I am missing something, I don't think I can use your extremely elegant solution for two reasons.
The first is that it would 'allow' a string such as '----------------1---------------------'
As you say, you could strip out any hyphens before testing, but then the next problem is that (to the best of my knowledge), the CAST function is very specific about what it will accept as a string representation of a uniqueidentifier.
For example:
SELECT CAST('5EAE1A80-4B4A-4FC8-A413-358F6D3E55CB' as uniqueidentifier)
Works. Whereas (no hyphens)
SELECT CAST('5EAE1A804B4A4FC8A413358F6D3E55CB' as uniqueidentifier)
fails. So if you did remove the hyphens, you'ld have to put them back in, and by that time .... 🙂
However, thanks for introducing me to the replicate function which I'm sure will come in very handy.
I must check out your book ... I've dabbled briefly with SQLXML for a data driven web application (I'm a bit of an XSL fan) and was really pleased with the performance and ease of use (once I got a handle on XML EXPLICIT queries), but my current knowledge is pretty superficial.
October 18, 2002 at 3:26 am
Ok, let’s put it together.
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'varchar36isuniqueidentifier')
DROP FUNCTION varchar36isuniqueidentifier
GO
CREATE FUNCTION varchar36isuniqueidentifier
(@cadena varchar(36))
RETURNS bit
AS
BEGIN
declare @error bit
IF (@cadena LIKE replicate('[A-F0-9]',8)
+'[-]'+replicate('[A-F0-9]',4)
+'[-]'+replicate('[A-F0-9]',4)
+'[-]'+replicate('[A-F0-9]',4)
+'[-]'+replicate('[A-F0-9]',12))
set @error = 1
else
set @error = 0
RETURN @error
END
GO
-- =============================================
-- Example to execute function
-- =============================================
declare @GUID uniqueidentifier
,@cad varchar(36)
set @GUID = newid()
set @cad = CAST (@guid as varchar(36))
select @GUID
SELECT dbo.varchar36isuniqueidentifier('lo que sea')
SELECT dbo.varchar36isuniqueidentifier(@cad)
GO
When I wrote the function it seemed to me that the solution was not very elegant, the function replicate did not came into my mind. Now I think that the solution is shorter but not neater. Anyway, looks better.
Thanks planet115 for the inspiration and simonsabin for the appreciation.
October 18, 2002 at 5:54 am
🙂
I'll be using that. Thanks everybody.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply