|Here is one of the situations I have:
I insert a person and an address the person provided. Addresses are in a seperate table.
So brief table layout:
person table: firstname, lastname, addressid
address table: id, street, streetnbr, busnbr, placeid. There is a unique constraint on the combination of all 4 fields that aren't the id.
Person A comes along to one of my client's employees and gives his info and a certain address.
Person B comes along to another one of my client's employees, gives his info and the same address as person A.
here is what currently happens in my app:
app receives sqlexception with the error saying there was a unique constraint problem and I respond to it in app by doing a sql statement that selects the record that matches the unique address.
Here is what I would like to happen:
stored procedure for Address tries to insert the address, finds out it already exists(throws exception in db) cause of the unique constraint and instead of supplying the id of the newly inserted record, it supplies the id of the existing record. Imo, this is far faster than what I have now, as the problem is handled before it leaves the stored procedure.
Problem is, I know nothing about error handling in SQL Server.
Here is my current sql statement for my SP:
ALTER procedure [dbo].[AddressInsert](@street varchar(255), @streetnumber varchar(255), @busnumber varchar(255), @placeid bigint, @newid bigint output) as begin
set @newid = next value for dbo.baseidseq
insert into [Address](baseid, street, streetnumber, busnumber, placeid) values(@newid, @street, @streetnumber, @busnumber, @placeid) end
What statement(s) do I add to return the id of the row that matches the parameters provided?
I assume I have to add something like select * from address where street = @street, etc. But what else?