Click here to Skip to main content
15,352,594 members
Home / Discussions / Database
   

Database

 
Questionhow to return primary key when insert statement violates unique constraint Pin
Nico Haegens10-May-14 7:21
professionalNico Haegens10-May-14 7:21 
AnswerRe: how to return primary key when insert statement violates unique constraint Pin
PIEBALDconsult10-May-14 7:44
professionalPIEBALDconsult10-May-14 7:44 
GeneralRe: how to return primary key when insert statement violates unique constraint Pin
Nico Haegens10-May-14 8:05
professionalNico Haegens10-May-14 8:05 
GeneralRe: how to return primary key when insert statement violates unique constraint Pin
PIEBALDconsult10-May-14 10:22
professionalPIEBALDconsult10-May-14 10:22 
GeneralRe: how to return primary key when insert statement violates unique constraint Pin
Nico Haegens10-May-14 23:07
professionalNico Haegens10-May-14 23:07 
GeneralRe: how to return primary key when insert statement violates unique constraint Pin
PIEBALDconsult11-May-14 5:56
professionalPIEBALDconsult11-May-14 5:56 
AnswerRe: how to return primary key when insert statement violates unique constraint Pin
jschell11-May-14 8:17
Memberjschell11-May-14 8:17 
GeneralRe: how to return primary key when insert statement violates unique constraint Pin
Nico Haegens12-May-14 3:20
professionalNico Haegens12-May-14 3:20 
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:
SQL
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?
GeneralRe: how to return primary key when insert statement violates unique constraint Pin
Richard.Berry10012-May-14 7:55
MemberRichard.Berry10012-May-14 7:55 
GeneralRe: how to return primary key when insert statement violates unique constraint Pin
Eddy Vluggen12-May-14 8:49
professionalEddy Vluggen12-May-14 8:49 
GeneralRe: how to return primary key when insert statement violates unique constraint Pin
Corporal Agarn12-May-14 9:45
professionalCorporal Agarn12-May-14 9:45 
GeneralRe: how to return primary key when insert statement violates unique constraint Pin
jschell12-May-14 10:31
Memberjschell12-May-14 10:31 
AnswerRe: how to return primary key when insert statement violates unique constraint Pin
Mycroft Holmes12-May-14 22:25
professionalMycroft Holmes12-May-14 22:25 
AnswerRe: how to return primary key when insert statement violates unique constraint Pin
Nico Haegens13-May-14 2:18
professionalNico Haegens13-May-14 2:18 
GeneralRe: how to return primary key when insert statement violates unique constraint Pin
Mycroft Holmes13-May-14 12:52
professionalMycroft Holmes13-May-14 12:52 
QuestionDatabase SQL Pin
Syafiqah Zahirah10-May-14 4:47
MemberSyafiqah Zahirah10-May-14 4:47 
AnswerRe: Database SQL Pin
Peter Leow10-May-14 5:24
professionalPeter Leow10-May-14 5:24 
GeneralRe: Database SQL Pin
Syafiqah Zahirah11-May-14 3:43
MemberSyafiqah Zahirah11-May-14 3:43 
AnswerRe: Database SQL Pin
Peter Leow11-May-14 4:13
professionalPeter Leow11-May-14 4:13 
GeneralRe: Database SQL Pin
Syafiqah Zahirah11-May-14 4:45
MemberSyafiqah Zahirah11-May-14 4:45 
GeneralRe: Database SQL Pin
Syafiqah Zahirah15-May-14 6:50
MemberSyafiqah Zahirah15-May-14 6:50 
GeneralRe: Database SQL Pin
Peter Leow15-May-14 14:47
professionalPeter Leow15-May-14 14:47 
QuestionArithmetic rounding Pin
Richard.Berry1009-May-14 11:04
MemberRichard.Berry1009-May-14 11:04 
AnswerRe: Arithmetic rounding Pin
Richard Andrew x649-May-14 12:26
professionalRichard Andrew x649-May-14 12:26 
GeneralRe: Arithmetic rounding Pin
Richard.Berry1009-May-14 20:58
MemberRichard.Berry1009-May-14 20:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.