Auto-incremnting numbers with stored procedures question

[GreatSeaSpider]

Hi,

I'm using stored procedures to add entries to my tables in my database. A couple of the tables have two different integers as keys eg:

customer: 1
Order: 1

etc.
My stored procedures auto increment one of these fields, in this case the order field.

my question is will the sql server be clever enough to increment the value of order in relation to the customer field? ie:

customer 1|order 1
customer 2|order 1
customer 1|order 2

NOT

customer 1|order 1
customer 2|order 2
customer 1|order 3

my brain is hurting on this one!

any advice appreciated

Pete

 

[nigelrivett]

if you code it it will be.

insert tbl
select customer = 1 ,
Order = coalesce((select max(Order) from tbl where customer = 1),0) + 1

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nikhilparchure]

SQL Server would not be clever...
You will have to write code to make it look clever

I hope nigels query would make SQL server clever enough

 

[GreatSeaSpider]

Thanks for the advice,

I'll try nigel's method and i'll let you know how i get on.

Pete

 

[GreatSeaSpider]

Hello again!

I've tried to use nigels sql in one of my stored procedures to get it working. it fails to assign a value for the field order_id. Here's a copy of my sp:
----------------------------------------------------------
CREATE PROCEDURE [sp_addorder]
(
@acc_number [int],
@project_name [ntext],
@status [ntext],
@order_id [int] OUT
)

AS INSERT INTO [*****].[dbo].[orders]
(
[acc_number],
[project_name],
[status]
)

VALUES
(
@acc_number,
@project_name,
@status
)
Set @order_id = coalesce((SELECT MAX(order_id) FROM [*****].[dbo].[orders] WHERE acc_number = @acc_number),0)+1
SELECT @order_id AS order_id
GO
----------------------------------------------------------
have i got the wrong end of the stick with this stored procedure? i'm new to tsql

any advice or a poke in the right direction would be appreciated.
thanks,

Pete