INSERT, SCOPE_IDENTITY, and parameters...

I have a simple table Person (PersonID, PersonName, and PersonAge). PersonID is the primary key and it's also an identity field. Let me paste a sample code and I'll explain at the bottom what's happening.

 SqlConnection conn =new SqlConnection(@."Server=.\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True"); conn.Open();try { SqlCommand cmd =new SqlCommand(); cmd.Connection = conn;// delete all rows cmd.CommandText ="DELETE FROM Person"; cmd.ExecuteNonQuery(); Response.Write("start... <br><br>");// ad-hoc insert cmd.CommandText ="SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText ="INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (5, 'John Smith', 20)"; cmd.ExecuteNonQuery(); cmd.CommandText ="SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText ="SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery();// parameter insert cmd.CommandText ="SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText ="INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (@.PersonID, @.PersonName, @.PersonAge)"; p =new SqlParameter("@.PersonID", 11); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p =new SqlParameter("@.PersonName","Jon Doe2"); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p =new SqlParameter("@.PersonAge", 21); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText ="SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText ="SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery(); Response.Write("<br>end."); }finally { conn.Close(); }

I'm basically trying to insert rows in the table in two ways: one is ad-hoc (hardcoded sql statement) and another using parameters. Using the ad-hoc method everything is OK. Whenever I use the "parameter insert" method I can not get back the ID using SCOPE_IDENTITY (I always get back a DbNull value, the data gets into the table just fine). I'm rather new to using parameters, so it's gotta be something very easy that I'm missing...

Thank you.

If I add "; SELECT SCOPE_IDENTITY()" to the INSERT statement and use ExecuteScalar instead of ExecuteNonQuery I get the identity back. The question is, why do I have to do this? If I use the SQL Server Profiler I don't see any difference between the two methods. Any thoughts?

Thank you.

|||Your SCOPE_IDENTITY() should be in the same batch as your INSERT. You are making 3 separate calls each with a different T-SQL statement and each one is treated independent of other. Better yet, use a stored proc and do it all in one place.|||

What exactly defines a batch? And if CommandText = "INSET INTO Table (fields..) VALUES (values); SELECT SCOPE_IDENTITY()" is a single batch when using parameters, why does SCOPE_IDENTITY work in two separate "batches" when not using parameters? I guess, what I'm trying to figure out is why are how are they different? Thanks.

|||

Hi,

A batch is guaranteed to be executed together. If you execute the command in 2 batches, there is possibility that a concurrent user may be also inserting into that table. Then your SELECT SCOPE_IDNETITY() query might return the incorrect number.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

sql

insert with multiple subqueries

Hi

what i like to do is insert in one table 2 value from 2 different row.

exp:

table1: person

id name

1 bob

2 john

so id like to make an insert that will result in this

table 2: person_knowed

idperson1: 1

idperson2: 2

so the wuery should look something like this:

insert into person_knowed(idperson1, idperson2)
(select id from personwhere name = 'bob',
select id from personwhere name = 'john'));

anybody have an idea of how to acheive this?

insert into person_knowed(idperson1, idperson2)
select A.id, B.id from
(select id from personwhere name = 'bob')A,
(select id from personwhere name = 'john')B

This will work as you are expecting it to work only if the subqueries return 1 row each. If they return more than 1 row, you will end up with a cross-join