A SQL procedure to STOP repeated info

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

A SQL procedure to STOP repeated info

Author

Topic

paradise_wolf
Starting Member

32 Posts

Posted - 2007-02-04 : 11:34:32

In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-04 : 11:47:41

CREATE PROCEDURE dbo.uspVerifyIfInvoiceExists
(@Invoice VARCHAR(50),
@DoesExist BIT OUT)
AS
IF EXISTS (SELECT NULL FROM IPN_received
WHERE Invoice = @Invoice)
SELECT @Bit = 1
ELSE SELECT @Bit = 0

Peter Larsson
Helsingborg, Sweden

paradise_wolf
Starting Member

32 Posts

Posted - 2007-02-04 : 12:10:00

Hi peso

CREATE PROCEDURE dbo.uspVerifyIfInvoiceExists
(@Invoice VARCHAR(50),
@DoesExist BIT OUT)
AS
IF EXISTS (SELECT NULL FROM IPN_received
WHERE Invoice = @Invoice)
SELECT @Bit = 1
ELSE SELECT @Bit = 0

Shouldn’t the last two lines be like in below instead ?

SELECT @ DoesExist = 1
ELSE SELECT @ DoesExist = 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-04 : 13:00:21

Yes, they should!
Just testing your presense...

Peter Larsson
Helsingborg, Sweden

paradise_wolf
Starting Member

32 Posts

Posted - 2007-02-05 : 06:50:24

Hi Peso

I modified your SQL code like below but I got a error message: “Procedure or Function 'VerifyIfInvoiceExists' expects parameter '@DoesExist', which was not supplied.”

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE VerifyIfInvoiceExists
(@Invoice VARCHAR(50),
@DoesExist BIT OUT)
AS
IF EXISTS (SELECT NULL FROM IPN_received
WHERE Invoice = @Invoice)
SELECT @DoesExist = 1
ELSE SELECT @DoesExist = 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

So I tried to fix it by declaring the variable ‘@DoesExist’ like below but then I got an exception error.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE VerifyIfInvoiceExists
(@Invoice VARCHAR(50))
AS
DECLARE @DoesExist BIT
IF EXISTS (SELECT NULL FROM IPN_received
WHERE Invoice = @Invoice)
SELECT @DoesExist = 1
ELSE SELECT @DoesExist = 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

What should I change in the code ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-05 : 07:04:27

quote:
Originally posted by paradise_wolf

Hi Peso

I modified your SQL code like below but I got a error message: “Procedure or Function 'VerifyIfInvoiceExists' expects parameter '@DoesExist', which was not supplied.”

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

CREATE PROCEDURE VerifyIfInvoiceExists
(@Invoice VARCHAR(50),
@DoesExist BIT OUT)
AS
IF EXISTS (SELECT NULL FROM IPN_received
WHERE Invoice = @Invoice)
SELECT @DoesExist = 1
ELSE SELECT @DoesExist = 0

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

So I tried to fix it by declaring the variable ‘@DoesExist’ like below but then I got an exception error.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE VerifyIfInvoiceExists
(@Invoice VARCHAR(50))
AS
DECLARE @DoesExist BIT
IF EXISTS (SELECT NULL FROM IPN_received
WHERE Invoice = @Invoice)
SELECT @DoesExist = 1
ELSE SELECT @DoesExist = 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

What should I change in the code ?

The code in blue is correct. It is how you call the SP.


declare @exists bit
exec VerifyIfInvoiceExists 'xxx', @exist OUTPUT

paradise_wolf
Starting Member

32 Posts

Posted - 2007-02-05 : 08:35:05

Hi khtan

I modified the method that is calling the 'blue' procedure but I am getting exception error. Here is the code:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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);

// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@DoesExist";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);

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

}
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-05 : 08:37:59

// create a new parameter
param = comm.CreateParameter();
param.ParameterName = "@DoesExist";
param.Direction = ParameterDirection.InputOutput;
param.DbType = DbType.Boolean;
comm.Parameters.Add(param);

Peter Larsson
Helsingborg, Sweden

paradise_wolf
Starting Member

32 Posts

Posted - 2007-02-05 : 09:02:08

Hi Peso

Now, I am getting an exception error in the below method ( however it works fine to all other countless associated methods ) :

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
// execute a select command and return a single result as a string
public static string ExecuteScalar(DbCommand command)
{
// The value to be returned
string value = "";
// Execute the command making sure the connection gets closed in the end
try
{
// Open the connection of the command
command.Connection.Open();
// Execute the command and get the number of affected rows
value = command.ExecuteScalar().ToString();
}
catch (Exception ex)
{
// Log eventual errors and rethrow them
Utilities.SendErrorLogEmail(ex);
throw ex;
}
finally
{
// Close the connection
command.Connection.Close();
}
// return the result
return value;
}
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Here is the error message:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Server Error in '/SI' Application.
________________________________________
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 94: // Log eventual errors and rethrow them
Line 95: Utilities.SendErrorLogEmail(ex);
Line 96: throw ex;
Line 97: }
Line 98: finally

Source File: e:\SI\App_Code\GenericDataAccess.cs Line: 96

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources