error in UDF data conversion-HELP! HELP!

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 2005 Forums

Transact-SQL (2005)

error in UDF data conversion-HELP! HELP!

Author

Topic

ramya_ep
Starting Member

2 Posts

Posted - 2011-08-22 : 22:35:07

I am working on this UDF which converts say, 123 to '000123' .Each digit in 000123 is converted to ASCII decimal(ie 48 48 48 49 50 51) and then an offset 25 needs to be added to each (ie 73 73 73 74 75 76). This decimal needs to be converted to HEX. Pls see below UDF.The problem is i get "The conversion of the nvarchar value '7479828280' overflowed an int column."

Sample test query : SELECT [dbo].[getAnonymizedID](123)

The UDF is :

CREATE FUNCTION [dbo].[getAnonymizedID] (@UserID int)
RETURNS nvarchar(MAX)

AS
BEGIN
DECLARE @AnonymousId nvarchar(MAX),@position INT , @tempID nvarchar(MAX), @radID nvarchar,@hex nvarchar(MAX)
DECLARE @i int,@temp int, @s varchar(MAX),@s1 BIGINT
SET @position = 1

SET @AnonymousId=''
SET @tempID = RIGHT ('000000'+ CAST (@UserID AS nvarchar), 6)

WHILE @position <= DATALENGTH(@tempID)
BEGIN
SET @AnonymousId = @AnonymousId + ISNULL(CAST((ASCII(SUBSTRING(@tempID, @position, 1)) + 25 )As nvarchar(MAX)),'')

SET @i=@AnonymousId
SET @s=''
WHILE (@i>0)
BEGIN
SET @temp=@i % 16
SET @i=@i /16
IF @temp>9
SET @s=char(55+@temp)+@s
ELSE
SET @s=char(48+@temp)+@s
END
SET @position = @position + 1
END
RETURN @s
END

Please help!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2011-08-22 : 22:42:58

Change data type of @i from int to bigint.

Harsh Athalye
http://www.letsgeek.net/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-23 : 04:18:22

You've also got a potentially dangerous cast in there. It's not a good idea to blindly cast to NVARCHAR (or VARCHAR, VARBINARY....) without specifying a size for the type. Defailt is 30 (I think) but it's a server config so not guaranteed across instances.

In red.....


CREATE FUNCTION [dbo].[getAnonymizedID] (@UserID int)
RETURNS nvarchar(MAX)

AS
BEGIN
       DECLARE @AnonymousId nvarchar(MAX),@position INT , @tempID nvarchar(MAX), @radID nvarchar,@hex nvarchar(MAX)
       DECLARE @i int,@temp int, @s varchar(MAX),@s1 BIGINT
       SET @position = 1
       
       SET @AnonymousId=''
       SET @tempID = RIGHT ('000000'+ CAST (@UserID AS nvarchar), 6)
 
       WHILE @position <= DATALENGTH(@tempID)
       BEGIN
             SET @AnonymousId =  @AnonymousId + ISNULL(CAST((ASCII(SUBSTRING(@tempID, @position, 1)) + 25 )As nvarchar(MAX)),'')
             
             SET @i=@AnonymousId
		SET @s=''
		WHILE (@i>0)
		BEGIN
			SET @temp=@i % 16
			SET @i=@i /16
			IF @temp>9
				   SET @s=char(55+@temp)+@s
			ELSE
				   SET @s=char(48+@temp)+@s
			END             
				 SET @position = @position + 1
		 END
 RETURN @s
END

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 04:28:05

see

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ramya_ep
Starting Member

2 Posts

Posted - 2011-08-25 : 14:29:06

Thank you all...it worked now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources