A SQL procedure to STOP repeated info being stored

Question

Post reply

A SQL procedure to STOP repeated info being stored

Paradise Wolf

SSC Veteran

Points: 205
More actions
February 4, 2007 at 9:43 am

#172017

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 the invoice number of the customer ( contained in its string of info ) and looks inside a table to see if it was already 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 info or 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 PROCEDURE VerifyIfInvoiceExists
(@Invoice VARCHAR(50))
AS
SELECT COUNT(*) FROM IPN_received
WHERE Invoice = @Invoice
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

CREATE PROCEDURE VerifyIfInvoiceExists

(@Invoice VARCHAR(50))

AS

IF EXISTS (SELECT 1 FROM IPN_received WHERE invoice = @Invoice)

RETURN 1

ELSE

RETURN 0

MohammedU
Microsoft SQL Server MVP

Paradise Wolf SSC Veteran Points: 205 More actions · Answer 2

Hi MohammedU

I tried your code but it is throwing an exception error.

Here is the method that call this procedure:

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

public static bool VerifyIfInvoiceExists(string invoice)

{

// get a configured DbCommand object

DbCommand comm = GenericDataAccess.CreateCommand();

// set the stored procedure name

comm.CommandText = "VerifyIfInvoiceExists";

// create a new parameter

DbParameter param = comm.CreateParameter();

param.ParameterName = "@Invoice";

param.Value = invoice;

param.DbType = DbType.String;

param.Size = 30;

comm.Parameters.Add(param);

return bool.Parse(GenericDataAccess.ExecuteScalar(comm));

}

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

What is wrong with the code ? What am I missing ?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

ExecuteScalar returns the value of a one row, one field result set. Mohammed's procedure doesn't return any result set, it sets the return value.

You have to create a return value parameter and add it to the command's parameters collection to see the result.

DCPeterson SSCoach Points: 15266 More actions · Answer 4

Call me naive, but I thought this is what a Primary Key was for...

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

vadba SSChampion Points: 11133 More actions · Answer 5

Or, change Mohammed's solution to:

CREATE PROCEDURE VerifyIfInvoiceExists

(@Invoice VARCHAR(50))

AS

IF EXISTS (SELECT 1 FROM IPN_received WHERE invoice = @Invoice)

SELECT 1 AS retval

ELSE

SELECT 0 AS retval

Paradise Wolf SSC Veteran Points: 205 More actions · Answer 6

Paradise Wolf

SSC Veteran

Points: 205

February 6, 2007 at 2:58 pm

#688154

Thank you all for your help.