quote:

---

Vivek writes "Hi,

using SQL Server 2000 on win2k advanced server

What I am doing -

Inserting a bunch of values using a stored procedure.
Stored proc loops thr and tries to insert the value(s) in
a table. If duplicate record is found then this value is added to the list which is declared as OUT param.

What I want to do -

Get back the list containing dup values and inform user.

What is happening -

If stored procedure is executed thr' SQL query analyser all works fine and I do get back list of dup values.

But thr ADO(v2.6), I am not getting the list of values rejected as being dups because ADO.Command.Execute method throws exception -"dup values not allowed...", etc. There is no problem in ADO code otherwise, works fine for everything else.

I am thinking, and cud be dead wrong, that because of errors in stored proc and interaction betn OLE DB, SQLCODE, SQLSTATE etc...
the OUT parameter is not being populated.

I am wondering is there a way to pretend at the end of stored proc that nothing went wrong?

does it make sense, what am I doing wrong, or you dont answer questions if they contain questions on ADO?

Appreciate your help."

---

quote:

---

You don't need to insert a value and catch a SQL Server error when a key is duplicated; run a query at the beginning to see which values will be duplicated first. then insert the ones which won't be.

[quote]
Vivek writes "Hi,

using SQL Server 2000 on win2k advanced server

What I am doing -

Inserting a bunch of values using a stored procedure.
Stored proc loops thr and tries to insert the value(s) in
a table. If duplicate record is found then this value is added to the list which is declared as OUT param.

What I want to do -

Get back the list containing dup values and inform user.

What is happening -

If stored procedure is executed thr' SQL query analyser all works fine and I do get back list of dup values.

But thr ADO(v2.6), I am not getting the list of values rejected as being dups because ADO.Command.Execute method throws exception -"dup values not allowed...", etc. There is no problem in ADO code otherwise, works fine for everything else.

I am thinking, and cud be dead wrong, that because of errors in stored proc and interaction betn OLE DB, SQLCODE, SQLSTATE etc...
the OUT parameter is not being populated.

I am wondering is there a way to pretend at the end of stored proc that nothing went wrong?

does it make sense, what am I doing wrong, or you dont answer questions if they contain questions on ADO?

Appreciate your help."

---

quote:

---

Thanks. You got it. Actually, thats how it is implemented right now.

Was wondering if there is a way to avoid doing a SELECT. Reason being many values will be inserted and not just one and I will end up running SELECT for each. But again may be it is far better than issuing individual INSERTS.


-Vivek

---

insert myTable
select
    myColumn
from
    myListofValues l
where
    not exists (
    select
        47
    from
        myTable
    where
        myUniqueColumn = l.myColumn )