An SQL question

[Shelly]

This is more of an SQL question, but I would like to know how to do it in
SQL Server.

In a given table I have two columns of importance. The first is account
number (and it is a foreign key). The other is agent_id. The combination
must be unique. What I would like to do is to autoincrement the agent_id
for a given account number. Example:

Account Number Agent ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3

When I add a new agent to account number 1200, I would like it to come up
automatically with 2 for the agent_id.. I know I could do a select on
account number and return MAX of agent_id. I could then increment that
value and use that pair for new agent creation. However, I wonder if thee
is a way to do that automatically in SQL?

Shelly

[Just Me]

Well, to get the lastAgentID number you could

Select max(AgentID) From Agent

"Shelly" <sh************@asap-consult.comwrote in message
news:13*************@corp.supernews.com...

This is more of an SQL question, but I would like to know how to do it in
SQL Server.

In a given table I have two columns of importance. The first is account
number (and it is a foreign key). The other is agent_id. The combination
must be unique. What I would like to do is to autoincrement the agent_id
for a given account number. Example:

Account Number Agent ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3

When I add a new agent to account number 1200, I would like it to come up
automatically with 2 for the agent_id.. I know I could do a select on
account number and return MAX of agent_id. I could then increment that
value and use that pair for new agent creation. However, I wonder if thee
is a way to do that automatically in SQL?

Shelly

[Shelly]

"Just Me" <news.microsoft.comwrote in message
news:Og**************@TK2MSFTNGP02.phx.gbl...

Well, to get the lastAgentID number you could

Select max(AgentID) From Agent

Like I said, I know I can do this (adding, of course, the clause WHERE
accountNumber=theaccountNumber) , but my question is can SQL do this
automatically on the insert statement into the Agent table? If it can, then
I can do an ExecuteScalar and return that value from the insert query
without having to do two queries in succession with the possiblity of a race
condition where someone else is adding an agent to the same account at the
same time.

Shelly

>


"Shelly" <sh************@asap-consult.comwrote in message
news:13*************@corp.supernews.com...

>This is more of an SQL question, but I would like to know how to do it in
SQL Server.

In a given table I have two columns of importance. The first is account
number (and it is a foreign key). The other is agent_id. The
combination must be unique. What I would like to do is to autoincrement
the agent_id for a given account number. Example:

Account Number Agent ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3

When I add a new agent to account number 1200, I would like it to come up
automatically with 2 for the agent_id.. I know I could do a select on
account number and return MAX of agent_id. I could then increment that
value and use that pair for new agent creation. However, I wonder if
thee is a way to do that automatically in SQL?

Shelly

[Just Me]

OK, now I understand what you are trying to acheive. I think the answer is
no there is not.


"Shelly" <sh************@asap-consult.comwrote in message
news:13*************@corp.supernews.com...

>
"Just Me" <news.microsoft.comwrote in message
news:Og**************@TK2MSFTNGP02.phx.gbl...

>Well, to get the lastAgentID number you could

Select max(AgentID) From Agent

Like I said, I know I can do this (adding, of course, the clause WHERE
accountNumber=theaccountNumber) , but my question is can SQL do this
automatically on the insert statement into the Agent table? If it can,
then I can do an ExecuteScalar and return that value from the insert query
without having to do two queries in succession with the possiblity of a
race condition where someone else is adding an agent to the same account
at the same time.

Shelly

>>


"Shelly" <sh************@asap-consult.comwrote in message
news:13*************@corp.supernews.com...

>>This is more of an SQL question, but I would like to know how to do it
in SQL Server.

In a given table I have two columns of importance. The first is account
number (and it is a foreign key). The other is agent_id. The
combination must be unique. What I would like to do is to autoincrement
the agent_id for a given account number. Example:

Account Number Agent ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3

When I add a new agent to account number 1200, I would like it to come
up automatically with 2 for the agent_id.. I know I could do a select
on account number and return MAX of agent_id. I could then increment
that value and use that pair for new agent creation. However, I wonder
if thee is a way to do that automatically in SQL?

Shelly

[Mark Rae [MVP]]

"Shelly" <sh************@asap-consult.comwrote in message
news:13*************@corp.supernews.com...

>
"Just Me" <news.microsoft.comwrote in message
news:Og**************@TK2MSFTNGP02.phx.gbl...

>Well, to get the lastAgentID number you could

Select max(AgentID) From Agent

Like I said, I know I can do this (adding, of course, the clause WHERE
accountNumber=theaccountNumber) , but my question is can SQL do this
automatically on the insert statement into the Agent table? If it can,
then I can do an ExecuteScalar and return that value from the insert query
without having to do two queries in succession with the possiblity of a
race condition where someone else is adding an agent to the same account
at the same time.

Shelly

>>


"Shelly" <sh************@asap-consult.comwrote in message
news:13*************@corp.supernews.com...

>>This is more of an SQL question, but I would like to know how to do it
in SQL Server.

In a given table I have two columns of importance. The first is account
number (and it is a foreign key). The other is agent_id. The
combination must be unique. What I would like to do is to autoincrement
the agent_id for a given account number. Example:

Account Number Agent ID
1000 1
1000 2
1100 1
1200 1
1200 2
1200 3

When I add a new agent to account number 1200, I would like it to come
up automatically with 2 for the agent_id.. I know I could do a select
on account number and return MAX of agent_id. I could then increment
that value and use that pair for new agent creation. However, I wonder
if thee is a way to do that automatically in SQL?

Shelly

--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Alan Silver]

In article <13*************@corp.supernews.com>, Shelly
<sh************@asap-consult.comwrites

>my question is can SQL do this automatically on the insert statement
into the Agent table? If it can, then I can do an ExecuteScalar and
return that value from the insert query without having to do two
queries in succession with the possiblity of a race condition where
someone else is adding an agent to the same account at the same time.

You don't need two queries, nor do you need to worry about racing. You
use a composite SQL query like this...

begin transaction

declare @maxagentid int

select @maxagentid=max(agentid) from agents where...

insert agents (agentid,...) values (@maxagentid,...)

commit transaction

Bear in mind that this can all go in one string from your ASP.NET code,
just separate the statements with commas...

string sql = "begin transaction; declare @maxagentid int; ...";

Note that this was written off the top of my head, so may contain some
errors, plus it will need to be modified to your exact situation, but
hopefully it should be clear enough to show what I meant.

HTH

--
Alan Silver
(anything added below this line is nothing to do with me)

[Shelly]

"Alan Silver" <al*********@nospam.thanx.invalidwrote in message
news:nE**************@nospamthankyou.spam...

In article <13*************@corp.supernews.com>, Shelly
<sh************@asap-consult.comwrites

>>my question is can SQL do this automatically on the insert statement into
the Agent table? If it can, then I can do an ExecuteScalar and return
that value from the insert query without having to do two queries in
succession with the possiblity of a race condition where someone else is
adding an agent to the same account at the same time.

You don't need two queries, nor do you need to worry about racing. You use
a composite SQL query like this...

begin transaction

declare @maxagentid int

select @maxagentid=max(agentid) from agents where...

insert agents (agentid,...) values (@maxagentid,...)

commit transaction

Bear in mind that this can all go in one string from your ASP.NET code,
just separate the statements with commas...

string sql = "begin transaction; declare @maxagentid int; ...";

Note that this was written off the top of my head, so may contain some
errors, plus it will need to be modified to your exact situation, but
hopefully it should be clear enough to show what I meant.

HTH

--
Alan Silver
(anything added below this line is nothing to do with me)

Yes, it is clear and a good suggestion. How would you increment the
@maxagentid by one before (or in) the insert line in the same sql
transaction?

Shelly

[Alan Silver]

In article <13*************@corp.supernews.com>, Shelly
<sh*******@asap-consult.comwrites
<snip>

>Yes, it is clear and a good suggestion. How would you increment the
@maxagentid by one before (or in) the insert line in the same sql
transaction?

Sorry, forgot that bit! It is very simple...

insert agents (agentid,...) values (@maxagentid + 1,...)

Simple eh? Note that I haven't tested this, but if it doesn't work, you
can just modify the @maxagentid value before this line.

HTH

--
Alan Silver
(anything added below this line is nothing to do with me)

[Shelly]

"Alan Silver" <al*********@nospam.thanx.invalidwrote in message
news:Ko**************@nospamthankyou.spam...

In article <13*************@corp.supernews.com>, Shelly
<sh*******@asap-consult.comwrites
<snip>

>>Yes, it is clear and a good suggestion. How would you increment the
@maxagentid by one before (or in) the insert line in the same sql
transaction?

Sorry, forgot that bit! It is very simple...

insert agents (agentid,...) values (@maxagentid + 1,...)

Simple eh? Note that I haven't tested this, but if it doesn't work, you
can just modify the @maxagentid value before this line.

This worked great except in one case. That case is where this is the first
agent to be added for that account. In that case, the first select will not
find a value for @maxAgentId and the transaction fails. The error message
is that it cannot insert the value NULL into column 'agentID' in table
Agent. I guss that this is because the operation of "@maxAgentID + 1"
failed to consider the NULL of "@maxAgentID to be a zero and so the
operation failed.

To solve this I added a line "IF @maxAgentID IS NULL @maxAgentID = 0"
before the insertion. It now works in all cases.

Thank you very much.

Shelly

[Mark Rae [MVP]]

"Shelly" <sh************@asap-consult.comwrote in message
news:13*************@corp.supernews.com...

To solve this I added a line "IF @maxAgentID IS NULL @maxAgentID = 0"
before the insertion. It now works in all cases.

Alternatively, you could have used the ISNULL T-SQL function:

ISNULL(@maxAgentID, 0)
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Alan Silver]

In article <13*************@corp.supernews.com>, Shelly
<sh************@asap-consult.comwrites

>To solve this I added a line "IF @maxAgentID IS NULL @maxAgentID = 0"
before the insertion. It now works in all cases.

Yup, that's a good way to do it.

>Thank you very much.

Pleasure ;-)

--
Alan Silver
(anything added below this line is nothing to do with me)