get a return value using sql (not stored procedure

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

Transact-SQL (2005)

get a return value using sql (not stored procedure

Author

Topic

jamest85
Starting Member

6 Posts

Posted - 2007-12-28 : 14:47:49

Hi:
Newbie question:
For some reason I don't want to use stored prodecure to get a retrun value (the return value is the primary key value after I insert a row, the primary key value is an auto generated int number).

Once the INSERT a row is done, I need get the auto generated number immediately for that row.

Thank you very much.

jt

jamest85

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-28 : 14:52:04

create table #junk (rowid int identity(1,1) , i int)
insert #junk (i) values (10)
select scope_identity()

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-28 : 15:04:55

I see you edited your original post, jamest85. Is that because the scope_identity() solution does not work for you?

Be One with the Optimizer
TG

jamest85
Starting Member

6 Posts

Posted - 2007-12-28 : 15:10:56

Thanks TG, but I am not quite follow:
My sql is like this:
===============
strSQL = "INSERT INTO tblForumsTopics(TopicsName) VALUES ("anyName")";
SqlConnection myConn = Config.DB.Open();
SqlCommand command = new SqlCommand(strSQL, myConn);
command.ExecuteNonQuery();
===============
Is there any way to get the tblForrumsTopics ID (auto generated) value after the INSERT is done?

Thanks.

quote:
Originally posted by TG

create table #junk (rowid int identity(1,1) , i int)
insert #junk (i) values (10)
select scope_identity()

Be One with the Optimizer
TG

jamest85

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-28 : 15:25:45

"INSERT INTO tblForumsTopics(TopicsName) VALUES ("anyName"); SELECT SCOPE_IDENTITY() as lastId";

or
take a look at the output clause:
http://weblogs.sqlteam.com/mladenp/archive/2006/06/08/10122.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

basic-vijay
Starting Member

1 Post

Posted - 2007-12-28 : 15:38:11

select ident_current('table')

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-28 : 15:41:43

ident_current isn't a good idea since it's not scope based. so it will return the lst id for all sessions and scopes.
it has the same problem @@identity has.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

jamest85
Starting Member

6 Posts

Posted - 2007-12-28 : 20:53:35

Hi: spirit1

Thanks for your post, I have added "SELECT SCOPE_IDENTITY() as lastId", it runs without any error.

My problem is: how to get that "lastId" value?

======================
strSQL = "SINSERT INTO tblForumsTopics(TopicsName) VALUES ("anyName"); SELECT SCOPE_IDENTITY() as lastId"
SqlConnection myConn = Config.DB.Open();
SqlCommand command = new SqlCommand(strSQL, myConn);
command.ExecuteNonQuery();
int myID = lastId(???? how to get the lastID value???)
command.Dispose();
myConn.close();
=====================
Thanks.
jt

quote:
Originally posted by spirit1

"INSERT INTO tblForumsTopics(TopicsName) VALUES ("anyName"); SELECT SCOPE_IDENTITY() as lastId";

or
take a look at the output clause:
http://weblogs.sqlteam.com/mladenp/archive/2006/06/08/10122.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

jamest85

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-28 : 20:59:31

int myID = (int)command.ExecuteScalar();

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

jamest85
Starting Member

6 Posts

Posted - 2007-12-28 : 22:52:24

thanks spirit1, it works great.

quote:
Originally posted by spirit1

int myID = (int)command.ExecuteScalar();

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

jamest85

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources