Checking existance of record in Stored Procedure

Hi,

I am new to Stored Procedures. There is a simple procedure I want to create.

The scenario is this :

There is a Table named Test. In that table is a Column named 'TestID'. Now I want to create a Stored Procedure which inserts a record in Test, but if 'TestID' passed to it already exists, it should throw an error.

I am using C# and SQL Server 2005 Express.
Some code in C# also would be helpful.

You would need to create EITHER a unique index on the column, or a TRIGGER.

With the information at hand, I would vote for a UNIQUE index, and set the column to NOT NULL (so that a value MUST be supplied).

|||Thanks for the reply.

But I want to manage it programaticaly. I know about the constraints and SQL. But I am learning T-SQL and have created a scenario by myself to work on.

Basically I want to know how to throw error from stored proc? Or any way to let the app. know of some exceptional situations, like in .Net?|||

Inside your sp put the following code..

Code Snippet

Create Procedure YouSpName(@.TestId int, other params....)

as

Begin

If Exists(Select 1 From Test Where TestId=@.TestId)

Begin

Raiserror ('Recorde Alreay Found', 16, 1)

Return

End

--Your actual code will be appended here

..

..

..

End

|||Thanks, It worked fine.|||

One of the 'hallmarks' of a competent and experienced SQL Server developer/dba, is to understand the impact of various options.

A TRIGGER executing consumes more server resources than a CONSTRAINT. A TRIGGER will, most likely, issue locks on data that could, under high usage scenarios, cause blocking situations for other users.

A CONSTRAINT has NONE of the negative impact of a TRIGGER, in fact, it is the least impactful way to force data conformance. When a CONSTRAINT is indicated, it is the 'best' option to engage. And a CONSTRAINT failure will definitely 'throw' an error that the application can catch and handle.

|||Thanks Arnie,

I can see what you are saying. I should have used the Unique Constraint. But as I said, this not the real thing. It's not part of a project or even a program. I wanted to know how to tell the application that an unexpected situation occurred in the stored procedure. Same as throwing exceptions in C#, VB or any .Net language. So, I created this hopeless scenario.

But I appreciate your concern and this information you typed might be useful to a lot of people who doesn't know this.

Thanks.