May 19, 2005 at 5:33 am
I need to create a StoredProcedure to calculate a field auto-inc.
Who helps me?
I tried many code, but i didn't have sucess.
My sample:
CREATE PROCEDURE SP_CT_ITEM
AS
DECLARE @CONTADOR NUMERIC(008)
SET NOCOUNT ON
BEGIN TRAN
SELECT @CONTADOR = CAST(NM1_PARAMETRO AS NUMERIC(008))
FROM PARAMETRO (UPDLOCK)
WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
UPDATE PARAMETRO SET NM1_PARAMETRO = @CONTADOR + 1
WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
SELECT @CONTADOR
COMMIT TRAN
GO
Thanks.
Marco
May 19, 2005 at 6:22 am
Assumption: You want to return the @CONTADOR value to the calling procedure
Change the proc from DECLARING @CONTADOR to
CREATE PROC...
@CONTADOR OUTPUT = NULL
AS
ALSO, I would AVOID naming your created procedures sp_ as they will ALWAYS go to master 1st to see if they are there AND THEN go to your DB. Also, it makes it a little harder to quickly ID what you have done vs. SQL provided....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 19, 2005 at 12:35 pm
CREATE PROCEDURE SP_CT_ITEM
AS
DECLARE @CONTADOR NUMERIC(8)
SET NOCOUNT ON
-- no need for explicit Transaction because of atomicity in single statements
UPDATE PARAMETRO SET @CONTADOR = NM1_PARAMETRO = NM1_PARAMETRO + 1
WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
SELECT @CONTADOR
GO
hth
* Noel
May 20, 2005 at 8:48 am
I modified my SP for:
CREATE PROCEDURE SP_CT_ITEM
AS
DECLARE @CONTADOR NUMERIC(008)
SET @CONTADOR = 0
SET NOCOUNT ON
UPDATE PARAMETRO SET
@CONTADOR = CAST(NM1_PARAMETRO AS NUMERIC(008)),
NM1_PARAMETRO = CAST(NM1_PARAMETRO AS NUMERIC(008)) + 1
WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
SELECT @CONTADOR
GO
In sometimes the @CONTADOR returned a next value, but the NM1_PARAMETRO didn't was updated. What's happining?
Sorry, my English is bad...
May 20, 2005 at 1:59 pm
Marco, LOOK at my code, the double assignment it is not a typo!
UPDATE PARAMETRO SET
@CONTADOR = NM1_PARAMETRO = NM1_PARAMETRO + 1
WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
is NM1_PARAMETRO non numeric ?
* Noel
May 20, 2005 at 2:17 pm
No, NM1_PARAMETRO isn't a numeric type.
So I create a new table with two fields: SIG_CONTADOR and NUM_CONTADOR.
But the problem continue, look my new SP:
CREATE PROCEDURE SP_CT_ITEM
AS
DECLARE @CONTADOR NUMERIC(015)
SET @CONTADOR = 0
SET NOCOUNT ON
IF @@TRANCOUNT > 0 BEGIN
SELECT ''
ROLLBACK
END
ELSE BEGIN
UPDATE CONTADOR SET @CONTADOR = NUM_CONTADOR = NUM_CONTADOR + 1
WHERE SIG_CONTADOR = 'ITEM'
SELECT @CONTADOR
END
GO
What do you think about its in red.
Thanks
May 20, 2005 at 3:13 pm
I don't think you need a rollback there.
If you want to prevent this proc from being executed in the middle of a transaction use "RETURN" instead
and You should be returning always the same datatype '' - is character and @contador is numeric
if you were to keep that logic you can try:
DECLARE @CONTADOR NUMERIC(015)
SET @CONTADOR = 0
SET NOCOUNT ON
IF @@TRANCOUNT > 0 BEGIN
SELECT -1 -- -1 could represent an invalid value
RETURN
END
UPDATE CONTADOR SET @CONTADOR = NUM_CONTADOR = NUM_CONTADOR + 1
WHERE SIG_CONTADOR = 'ITEM'
SELECT @CONTADOR
GO
[Edit:] Still don't know why you want to prevent this from within a transaction ?
* Noel
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply