Insert Stored Procedure

I want to create a Stored Procedure to Insert values into a table only if the are no such entry in there. eg. if i don't want to have Interests with the same title.

I was thinking i should do something like:
If rows from (SELECT * FROM Table WHERE Title = @.Title) > 0 Return an Error (and how do i display an error in my ASP site)
Else INSERT INTO Table ...I tried:

Code Snippet

ALTER PROCEDURE dbo.InsertInterest
(
@.Title varchar,
@.Category int
)
AS
SET NOCOUNT ON
RETURN

SELECT ID, CategoryID, Title FROM TempInterests WHERE (Title = @.Title)

DECLARE @.rv int
DECLARE @.RETURN_VALUE int
SET @.rv = @.RETURN_VALUE

IF @.rv > 0
INSERT INTO TempInterests (CategoryID, Title) VALUES (@.Category, @.Title)

But didn't work it always returns:
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE = 0

Code Snippet

ALTER PROCEDURE dbo.InsertInterest
(
@.Title varchar,
@.Category int
)
AS
begin
SET NOCOUNT ON
if exists (SELECT * FROM TempInterests WHERE Title = @.Title )
return
INSERT INTO TempInterests (CategoryID, Title) VALUES (@.Category, @.Title)
end

|||

The most efficient way is to simply have a UNIQUE CONSTRAINT on the [Title] column. Do the insert, it will fail if the [Title] already exist in the table.

Alternatively, you could use a IF NOT EXISTS test:

Code Snippet

ALTER PROCEDURE dbo.InsertInterest
(
@.Title varchar,
@.Category int
)
AS

IF NOT EXISTS
( SELECT ID
FROM TempInterests
WHERE Title = @.Title
)
INSERT INTO TempInterests ( CategoryID, Title )
VALUES (@.Category, @.Title)

|||whats strange is the first time i tested with Title1, it inserted "t" 1 character only. but looks like it works, bur the prob is what happens if i have a table with many values to insert do i have to type 1 by 1? which can create a lot of mistakes.

And how do i use it? it seems DataSource1.Insert() inserts NULLs into the DB and when i go into the Insert Query in the Properties (In VS2005), in the Query it shows the Insert Command is "InsertInterest" then i selected the parameters value to be selected from the controls, it didn't seem to insert anything at all

and if i do:
Dim temp As Integer = dsForInsert.Insert()
i get temp = -1, and what is that actually?
|||Seems like thats because i have declared the variable as varchar that might have defaulted to 1 character, changing to VARCHAR(50) sis the job.

my stored procedure currently:

Code Snippet

ALTER PROCEDURE dbo.InsertInterest
(
@.Title varchar(50),
@.CategoryID int
)
AS

SELECT ID
FROM TempInterests
WHERE Title = @.Title

DECLARE @.rc int
SET @.rc = @.@.rowcount

if @.rc = 0
{
INSERT INTO TempInterests (CategoryID, Title)
VALUES (@.CategoryID, @.Title)
}

but how do i return a value like 1 if it completed successfully and 0 if not.
and in VS2005 to use the stored procedure, selecting Stored Procedure instead of SQL Statement put "InsertInterests" in the Insert Command. do it need to be Exec <StoredProcName> ?

|||

Code Snippet

ALTERPROCEDURE dbo.InsertInterest

(

@.Title varchar(50),

@.CategoryID int

)

AS

IFNOTEXISTS(SELECT ID FROM TempInterests

WHERE Title = @.Title)

INSERTINTO TempInterests (CategoryID, Title)

VALUES(@.CategoryID, @.Title)

SELECT@.@.rowcount

|||thanks that is a cleaner code! but 1 question,

SELECT@.@.rowcount

then whats RETURN?






|||

If you wish to use the procedure @.RETURN value for success/failure (and that is often a good idea), try the following variation:

Code Snippet

ALTER PROCEDURE dbo.InsertInterest
( @.Title varchar(50),
@.CategoryID int
)
AS
BEGIN
IF NOT EXISTS
(SELECT ID
FROM TempInterests
WHERE Title = @.Title
)
BEGIN
INSERT INTO TempInterests
( CategoryID,
Title
)
VALUES
( @.CategoryID,
@.Title
)

RETURN 0
END
ELSE
RETURN 1
END

@.RETURN values: 0=Success, 1=Failure

|||thats fine but in Visual Studio, how do i do something like IntVal = Datasource.Insert() - Using stored procedure. so that i can return different values for different errors. like if there is a Dup then return -1, something else wrong during insert return -2 if everything is all right then 1. something like that

- EDIT
And if i don't put BEGIN, will anything wrong happen?

And whats the Syntax for If like: its like
IF (condition) Do Somthing
but if Do Something is many lines? or how do i ensure what is done in the True part and false part, like in VB i would use

Code Snippet

IF Condition Then
Do Something
Else
Do Something else
END IF

i do

Code Snippet

ALTER PROCEDURE dbo.InsertInterest
(
@.Title varchar(50),
@.CategoryID int
)
AS

if not exists (
SELECT InterestID
FROM Interests
WHERE Title = @.Title
)
INSERT INTO Interests (CategoryID, Title)
VALUES (@.CategoryID, @.Title)

dosen't make much diff? oh, and currentlu using intSuccess = DataSource.Insert(). dunno how to retrun a values to VS not to the debug window only.

and how do i debug SQL? can't get break points to work

|||

All stored procedures have a @.RETURN parameter. SET a VS variable to capture the @.RETURN parameter.

As in the suggestion I posted, the integer value following the RETURN keywork will be returned to the calling application as the @.RETURN value.

RETURN 0
END
ELSE
RETURN 1

BEGIN...END denotes a 'block' of code that must run as one unit. So following an IF, or an ELSE, use BEGIN...END to identify all the code statements that will execute for the IF condition, and all the code statements that will execute for the ELSE condition. In VB, all IF code statements will execute up until the ELSE or the END -in SQL Server only the next code 'block' will execute. A block is a single statement OR several statements wrapped in a BEGIN...END block.

In SQL 2005, you debug using Visual Studio. Check the Visual Studio/MSDN Help.