Scope_Identity() is causing multiple inserts...

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

General SQL Server Forums

New to SQL Server Programming

Scope_Identity() is causing multiple inserts...

Author

Topic

JamesJenkins
Starting Member

5 Posts

Posted - 2009-02-28 : 03:59:29

I have created the following procedure, but when I run it using the command also below, I end up with two rows in the table.

Any ideas?

Cheers,

James

-------------------------

CREATE PROCEDURE InsertNewGenus

@GenusName varchar(100)

AS

INSERT INTO GenusTable (GenusName)
VALUES (@GenusName)

SELECT Scope_Identity() AS 'GenusID'

GO

-----------

EXEC InsertNewGenus 'Cucumis'

-----------

shaggy
Posting Yak Master

248 Posts

Posted - 2009-02-28 : 05:12:30

Obviously it will return 2 rowcount
1st rowcount is for insert statement
2nd for select statement
use setnocount on/off for not returning rowcount

JamesJenkins
Starting Member

5 Posts

Posted - 2009-02-28 : 05:24:59

Hi,

Thanks for your post, perhaps I wasn't very clear in my posting. I actually end up with two identical rows inserted into the table. So if I use the following:

EXEC InsertNewGenus 'Cucumis'

I would end up with the table populated as follows:

GenusID GenusName

1 Cucumis
2 Cucumis

If I remove the following part from the stored proceedure it works fine:

SELECT Scope_Identity() AS 'GenusID'

Any ideas?

Cheers,

James

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-28 : 05:53:47

Do you have a trigger on the GenusTable table?

E 12°55'05.63"
N 56°04'39.26"

JamesJenkins
Starting Member

5 Posts

Posted - 2009-02-28 : 05:54:31

No, I don't think so.

James

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-28 : 06:13:55

Works execellent for me...

CREATE TABLE	#GenusTable
		(
			GenusID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			GenusName VARCHAR(100)
		)
GO
CREATE PROCEDURE dbo.uspInsertNewGenus
(
	@GenusName VARCHAR(100)
)
AS

SET NOCOUNT ON

INSERT	#GenusTable
	(
		GenusName
	)
VALUES	(
		@GenusName
	)

SELECT	SCOPE_IDENTITY() AS GenusID
GO
SELECT	*
FROM	#GenusTable
GO
EXEC	dbo.uspInsertNewGenus	'Cucumis'
GO
SELECT	*
FROM	#GenusTable
GO
DROP PROCEDURE	dbo.uspInsertNewGenus
GO
DROP TABLE	#GenusTable
GO

E 12°55'05.63"
N 56°04'39.26"

JamesJenkins
Starting Member

5 Posts

Posted - 2009-02-28 : 11:28:03

I am wondering if it is something to do with the way my Query Analyzer is working. You script ran (as far as I could tell), but didn't return any results.

Interestingly if I run:

EXEC InsertNewGenus 'Allium'

GO

I get only one entry in the table, but no scope_identity value returned.

This is all a bit strange...

James

JamesJenkins
Starting Member

5 Posts

Posted - 2009-02-28 : 15:45:24

Yes, I think it must be query analyzer. It works fine when called from ASP.

Strange,

James

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources