A SQL procedure to STOP repeated info being stored

In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would then be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.

My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.

So I want a SQL procedure where it takes theinvoice number of the customer ( contained in its string of info ) and looks inside a table to see if it wasalready stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new infoor not and then activate ( or not ) some related tasks.

I am still learning SQL and I tried the below procedure but it is not working. Which alternative procedure could solve the problem ?

~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE PROCEDUREVerifyIfInvoiceExists

(@.InvoiceVARCHAR(50))

AS

SELECT COUNT(*)FROMIPN_received

WHEREInvoice = @.Invoice

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Before you add the info into the table you can check to see if it has arlready been added:

create proc..

as..

IF NOT EXISTS (SELECT * FROM IPN_Received WHERE Invoice = @.Invoice)

Begin

--Do the insert

END

|||

Hindinakar

I just want a procedure that verifies if a given invoice number is in the table and then return 1 or return 0 if it is not.

Ho lines of code I should add to it?

|||

Sorry the typo. It should read like that:

Hindinakar

I just want a procedure that verifies if a given invoice number is in the table and then return 1 or return 0 if it is not.

What lines of code I should add to it?

|||

Hi,

The better option is create a unique key constraint for the field/fields you don't want to store duplicate data. Now when you are inserting the data sql server will trigger unique key constraint error which can be trapped from asp.net. Error number '2627'. Look for this exception at the time of inserting the data.

Cheers,

Hamlin stephen

hamlin_stephen@.yahoo.com

|||

It is good to have a unique constraint. It is also a good practice to check before doing an insert rather than running the insert anyway and checking for an error message.

Throw some return statements in the code.

IF EXISTS( SELECT...)

Return 1

ELSE

return 0