Is it possible to use two SCOPE_IDENTITY calls in the same procedure?

Question

The way I want my stored procedure to work is this, the user passes all of the customer data, name, phone, and all of address info into the procedure, then I want to perform an insert into the address table, get the id generated from that insert, use that id in another insert for the customer table then return the customer id to the user on the app side. Is this possible within the same stored procedure or is it highly against standards and what do you suggest?

Answer 1

Yes it's possible, but personally I'd compartmentalize this with separate procedures for the Address and Customer tables. This should give you greater flexibility and more opportunities for code reuse in the future.