Indentity and SPROC - SQL Server Forums

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

SQL Server Administration (2000)

Indentity and SPROC

Author

Topic

steve65
Starting Member

5 Posts

Posted - 2003-08-28 : 22:30:19

Hi All,

As you can tell from my post count I am new to the board and MS SQL. I have been using Oracle to get sequenced ids that I then use in an external program.

The query that I ran was something like this:

select dbo.seq1.nextval from sys.dual

This incremented the sequence and returned the value back to me.

I have spent most of the night reading about this subject and how it relates to SQL. From what I can tell I need to set up a table with an Identity column in it and then through the use of a stored procedure insert a row in the table and then do a select. A couple of questions come to mind for me.

1. I assume I need to use begin and end trans in the procedure to make sure that I get the id that I just inserted. Am I right?

2. I read a few times that the above procedure is just a hack. If this is the case, please tell me why so that I can learn, and what would be the proper way of doing this.

3. Is there available a SPROC example of how to do this?

Thanks for any input you may be able to offer.
Steve

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-28 : 22:42:50

Steve,

1) No.. Look up @@Identity, SCOPE_IDENTITY() etc.. in BOL..
2) Read BOL (Books On Line).. It is the SQL Server Help file..
3) See (2)

DavidM

"SQL-3 is an abomination.."

steve65
Starting Member

5 Posts

Posted - 2003-08-29 : 09:18:30

Dave I have done that and thanks for reminding me about the RTFM rule

I have created the following stored procedure

Alter PROCEDURE [AVSP_CNT_DOC]

AS

begin transaction

declare @dTempDate datetime

select @dTempDate = getdate()

INSERT INTO [DEVDatabase].[dbo].[CNT_DOC]([DateEntered])
VALUES (@dTempDate)

SELECT @@IDENTITY AS 'Identity'

commit

I added the transaction statement because my thinking was I did not want someone else doing an insert on the same table until I was able to get my @@IDENTITY. To be honest I was not sure if this really had an impact or not.

The App that I am writing this for can issue an exec statement but it will not return any values that the procedure above returns. So I am trying to figure out a way to generate the next number in the series using a select statement while making sure that if someone issues the same command the sequences will not be confused.

Thanks for any input

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources