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

A function that returns comma delimited results

Hi all,

I need to create a Function that returns a table that coverts the repeated results of its original (grouped on a certain column) and comma delimit a field.

I'll give an example, as I cant explain to well what I want.

I have this table:

ANSWER
ID TITLE QUESTIONID
0 y 1
1 n 1
2 y 2
3 n 3
4 m 3
5 y 3
6 n 4

and I want to create a function that will allow me to return the following, grouped by the question ID.

ANSWERS
TITLE QUESTIONID
y,n 1
y 2
n,m,y 3
n 4

Thanks,
Let me know if there is a more appropriate place to post this type of question.This should get you started.
create function dbo.udf(@.i int)
returns varchar(8000)
as
begin
declare @.s varchar(8000)
select @.s=isnull(@.s+',','')+Title
from Answers
where QuestionID=@.i
order by ID
return @.s
end
go
select dbo.udf(QuestionID) [Title],QuestionID
from (select distinct QuestionID
from Answers)Derived|||Thats fantastic!

I got as far as creating the function myself, but had no idea about the select you wrote.

Can I ask, what is Derived?
After integrating with my structure, I created a view which contains that SELECT, and then used that view as part of my query, and solving my issues. Thanks!!

Andre|||Derived is an alias for the derived table - virtual table formed by the inner select.
e.g.
select *
from (select OrderID from Northwind..Orders) as [derived table]