Stored procedure to loop and increment ints and varchars

[CherylD]

I have a stored procedure that takes a value out of the database (eg. A001UD7) and recursively increments it. If the value is a 9, the next should be A, and if the value is Z the next is 0. Here is the stored procedure:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE dbo.CREATEKEY (
@table VARCHAR(255))
AS

DECLARE @newKEY AS VARCHAR(32)
DECLARE @I AS INT
DECLARE @COMPARE AS CHAR(1)

/*
**select the last used key
*/
SELECT @newKEY = (SELECT KEYVALUE FROM sysdba.SITEKEYS WHERE KEYDESC=@table AND SITECODE='XXXX')

/*
**increment the key
*/

/*
** Reverse loop through the variable from right to left look at and increment
** characters and numbers.
*/
/*
SET @I = 7
WHILE @I>0
BEGIN
SET @COMPARE = SUBSTRING(@newKEY, @I, 1)
IF ((CAST(@COMPARE AS INT)) < 9 )
BEGIN
SET @COMPARE = @COMPARE + 1
SET @newKEY = @COMPARE SUBSTRING(@newKEY,1,@I-1) + @COMPARE + SUBSTRING(@newKEY,@I+1,7-@I)
SET @I = 0
BREAK
END
ELSE
BEGIN
IF (@COMPARE = 'Z')
BEGIN
SET @COMPARE = 0
SET @newKEY = SUBSTRING(@newKEY,1,@I-1) + @COMPARE + SUBSTRING(@newKEY,@I+1,7-@I)

END
ELSE
BEGIN
IF ((CAST(@COMPARE AS INT) = 9)
BEGIN
SET @COMPARE = 'A'
SET @newKEY = SUBSTRING(@newKEY,1,@I-1) + @COMPARE + SUBSTRING(@newKEY,@I+1,7-@I)
END
ELSE
BEGIN
SELECT @COMPARE = CHAR(ASCII(@COMPARE) + 1)
SET @newKEY = SUBSTRING(@newKEY,1,@I-1) + @COMPARE + SUBSTRING(@newKEY,@I+1,7-@I)
END
END


END
SET @I = @I - 1
END
*/
/*
**write it back
*/

UPDATE sysdba.SITEKEYS
SET KEYVALUE=@newKEY
WHERE KEYDESC=@table AND SITECODE='XXXX'


RETURN @newKEY

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

And here is the error:
Server: Msg 245, Level 16, State 1, Procedure CREATEKEY, Line 74
Syntax error converting the varchar value 'A001UD7' to a column of data type int.

 

[tlbroadbent]

You cannoit use RETURN to return the newkey value. RETURN only returns an INT value. You must use an output prarameter in the Store Procedure.

ALTER PROCEDURE dbo.CREATEKEY (
@table VARCHAR(255)
@newKEY AS VARCHAR(32) OUTPUT)
AS

DECLARE @I AS INT
DECLARE @COMPARE AS CHAR(1)

<the rest of your code omitting RETURN @newKEY>

Call The SP in this manner.

Declare @key varchar(32)
Exec dbo.CREATEKEY 'tablename',@key OUTPUT

@key will contain the value of @newkey in teh SP. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[CherylD]

Thanks,

No more errors, but the table doesn't update either

 

[tlbroadbent]

Which table doesn't update? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[CherylD]

UPDATE sysdba.SITEKEYS
SET KEYVALUE=@newKEY
WHERE KEYDESC=@table AND SITECODE='XXXX'

 

[CherylD]

Even on a return of @key, the value is still the same.

 

[tlbroadbent]

The code you posted has most of the procedure commented out. The /* before SET @I = 7 starts the comment.

*/ after END and before

/*
**write it back
*/

ends the comment. Is that the problem - no conversion code is actually being executed? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.