I have an autoID store procedure that I am sure most anyone but me can
understand. I need an explanation of what it's doing at each point as it
runs. It's not that it is not working correctly, it's just I need to
understand it so that I can modify it to allow me to batch import data into a
table. I would think that after running the import the store procedure would
see the addition of a new record(s) and add the next new record with the next
primary key, but instead it returns a primary key violation. (I was careful
to start my data with the next primary key and without dupes). If someone
would be kind enough to help me out, I could adjust the procedure to allow me
to mass batch import data without causing the PK error. Thank you in advance.
Here is the SP
/****** Object: Stored Procedure dbo.sp_AUTOID Script Date: 11/30/2000
10:12:18 AM ******/
/****** Object: Stored Procedure dbo.sp_AUTOID Script Date: 9/22/00
12:08:38 PM ******/
create procedure sp_AUTOID
(
@.TABLENAME varchar(30),
@.AUTOVALUE int output
)
AS
declare
@.rc int,
@.tc int
/*Use transaction to prevent contention.*/
select @.tc = @.@.trancount
if (@.tc = 0)
begin tran px_AUTOID
else
save tran px_AUTOID
if exists (select * from AUTOID where TABLENAME = @.TABLENAME)
begin
update AUTOID set AUTOVALUE = AUTOVALUE + 1 where TABLENAME = @.TABLENAME
select @.rc = @.@.error
if (@.rc<>0) goto OnError
end
else
begin
insert AUTOID(TABLENAME, AUTOVALUE) values(@.TABLENAME,1)
select @.rc = @.@.error
if (@.rc<>0) goto OnError
end
select @.AUTOVALUE = AUTOVALUE from AUTOID where TABLENAME = @.TABLENAME
if (@.tc = 0)
commit tran px_AUTOID
return(0)
OnError:
rollback tran px_AUTOID
raiserror("sp_AUTOID Error :%d", 18, 1, @.rc)
return(1)
grant execute on dbl.sp_AUTOID to publicWhat is the DDL of the table?
"Steve1445" <Steve1445@.discussions.microsoft.com> wrote in message
news:AE61DE23-CE94-4255-BE04-BB25485B4822@.microsoft.com...
>I have an autoID store procedure that I am sure most anyone but me can
> understand. I need an explanation of what it's doing at each point as it
> runs. It's not that it is not working correctly, it's just I need to
> understand it so that I can modify it to allow me to batch import data
> into a
> table. I would think that after running the import the store procedure
> would
> see the addition of a new record(s) and add the next new record with the
> next
> primary key, but instead it returns a primary key violation. (I was
> careful
> to start my data with the next primary key and without dupes). If someone
> would be kind enough to help me out, I could adjust the procedure to allow
> me
> to mass batch import data without causing the PK error. Thank you in
> advance.
> Here is the SP
> /****** Object: Stored Procedure dbo.sp_AUTOID Script Date: 11/30/2000
> 10:12:18 AM ******/
> /****** Object: Stored Procedure dbo.sp_AUTOID Script Date: 9/22/00
> 12:08:38 PM ******/
> create procedure sp_AUTOID
> (
> @.TABLENAME varchar(30),
> @.AUTOVALUE int output
> )
> AS
> declare
> @.rc int,
> @.tc int
> /*Use transaction to prevent contention.*/
> select @.tc = @.@.trancount
> if (@.tc = 0)
> begin tran px_AUTOID
> else
> save tran px_AUTOID
> if exists (select * from AUTOID where TABLENAME = @.TABLENAME)
> begin
> update AUTOID set AUTOVALUE = AUTOVALUE + 1 where TABLENAME => @.TABLENAME
> select @.rc = @.@.error
> if (@.rc<>0) goto OnError
> end
> else
> begin
> insert AUTOID(TABLENAME, AUTOVALUE) values(@.TABLENAME,1)
> select @.rc = @.@.error
> if (@.rc<>0) goto OnError
> end
> select @.AUTOVALUE = AUTOVALUE from AUTOID where TABLENAME = @.TABLENAME
> if (@.tc = 0)
> commit tran px_AUTOID
> return(0)
> OnError:
> rollback tran px_AUTOID
> raiserror("sp_AUTOID Error :%d", 18, 1, @.rc)
> return(1)
> grant execute on dbl.sp_AUTOID to public
>|||It would be an update query.
"SullivanYee" wrote:
> What is the DDL of the table?
> "Steve1445" <Steve1445@.discussions.microsoft.com> wrote in message
> news:AE61DE23-CE94-4255-BE04-BB25485B4822@.microsoft.com...
> >I have an autoID store procedure that I am sure most anyone but me can
> > understand. I need an explanation of what it's doing at each point as it
> > runs. It's not that it is not working correctly, it's just I need to
> > understand it so that I can modify it to allow me to batch import data
> > into a
> > table. I would think that after running the import the store procedure
> > would
> > see the addition of a new record(s) and add the next new record with the
> > next
> > primary key, but instead it returns a primary key violation. (I was
> > careful
> > to start my data with the next primary key and without dupes). If someone
> > would be kind enough to help me out, I could adjust the procedure to allow
> > me
> > to mass batch import data without causing the PK error. Thank you in
> > advance.
> >
> > Here is the SP
> >
> > /****** Object: Stored Procedure dbo.sp_AUTOID Script Date: 11/30/2000
> > 10:12:18 AM ******/
> >
> > /****** Object: Stored Procedure dbo.sp_AUTOID Script Date: 9/22/00
> > 12:08:38 PM ******/
> > create procedure sp_AUTOID
> > (
> > @.TABLENAME varchar(30),
> > @.AUTOVALUE int output
> > )
> > AS
> >
> > declare
> > @.rc int,
> > @.tc int
> >
> > /*Use transaction to prevent contention.*/
> >
> > select @.tc = @.@.trancount
> > if (@.tc = 0)
> > begin tran px_AUTOID
> > else
> > save tran px_AUTOID
> >
> > if exists (select * from AUTOID where TABLENAME = @.TABLENAME)
> > begin
> > update AUTOID set AUTOVALUE = AUTOVALUE + 1 where TABLENAME => > @.TABLENAME
> > select @.rc = @.@.error
> > if (@.rc<>0) goto OnError
> > end
> > else
> > begin
> > insert AUTOID(TABLENAME, AUTOVALUE) values(@.TABLENAME,1)
> > select @.rc = @.@.error
> > if (@.rc<>0) goto OnError
> > end
> >
> > select @.AUTOVALUE = AUTOVALUE from AUTOID where TABLENAME = @.TABLENAME
> >
> > if (@.tc = 0)
> > commit tran px_AUTOID
> > return(0)
> >
> > OnError:
> > rollback tran px_AUTOID
> > raiserror("sp_AUTOID Error :%d", 18, 1, @.rc)
> > return(1)
> >
> > grant execute on dbl.sp_AUTOID to public
> >
> >
>
>
No comments:
Post a Comment