scope_identity() in Dynamic SQL(Help Me..)

Question

Post reply

scope_identity() in Dynamic SQL(Help Me..)

kiranpchandran

Ten Centuries

Points: 1010
More actions
October 9, 2002 at 6:49 am

#79574

Hi all,
I am using MS SQL Server 2000 as backend and VB6 as front end. In my database I have to use identity field in dynamic sql for some reasons. When I used scope_identity() for getting the last number generated from the same scope, it returned null. Please help me, it is urgent and I am working on a multi user project. If it is not possible, please tell me the remedy for this
Thanks in advance
What I did is:
Table1
A int identity(1,1)
B varchar(30)
declare @sql nvarchar(100)
set @sql = 'insert into table1 (b) values(''test'')'
exec sp_executesql @sql
select scope_identity()

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Did you run them in the same batch?

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

kiranpchandran Ten Centuries Points: 1010 More actions · Answer 2

hi chrhedga

u mean

begin tran

and

commit tran?

I have tried that also, but it doen't make any difference

Thanks for your reply

Kiran

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

No, what I meant was if you execute these statements from your VB application as a single batch, or as several statements executed after each other, one by one. What does your VB code look like?

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

kiranpchandran Ten Centuries Points: 1010 More actions · Answer 4

Dear chrhedga

I am calling a stored procedure in SQL Server from VB and I am using that code inside the SQL Server procedure and I will return the value to VB. So there is nothing to do with VB code. Sorry for confusing you.

Thank you

Kiran

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 5

Hmm... sorry for me being blind, I just spotted the problem. sp_executesql executes it's statements in a separate batch, so therefore scope_identity() will return null as there haven't been any inserts in the scope where it is executed. You can fix this by using @@identity instead.

declare @sql nvarchar(100)

set @sql = 'insert into table1 (b) values(''test'')'

exec sp_executesql @sql

select @@identity

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

kiranpchandran Ten Centuries Points: 1010 More actions · Answer 6

Dear chrhedga

Thanks again.

That worked !!...

You have been a tremendous help!

Thanks a lot

Kiran