A question of SP technique...

Hi All

I'm creating some SPs and I've got a query which is inserting data
into a table with a a unique constraint:

CREATE TABLE [fil_Films] (
[fil_ID] [int] IDENTITY (1, 1) NOT NULL ,
[fil_Film] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_Tfil_Film] PRIMARY KEY NONCLUSTERED
(
[fil_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_fil_Films] UNIQUE NONCLUSTERED
(
[fil_Film]
) ON [PRIMARY]
) ON [PRIMARY]
GO

When I insert data, should I check in the SP to see if there is an
existing record or simply catch the error if it already exists? Which
is the better technique? My current SP looks like:

CREATE PROCEDURE spAddFilm (@.Type varchar(50)) AS

DECLARE @.Count int

SET NOCOUNT ON

SELECT @.Count = Count(fil_ID) FROM fil_Films WHERE fil_Films.fil_Film
= @.Type
IF @.COUNT IS NULL
BEGIN
INSERT INTO dbo.Tfil_Film (Tfil_Type) VALUES (@.Type)
RETURN 1 -- OK
END
ELSE
BEGIN
RETURN 2 -- Exists
END

GO

Thanks

SamSamuel Hon (noreply@.samuelhon.co.uk) writes:
> I'm creating some SPs and I've got a query which is inserting data
> into a table with a a unique constraint:
> CREATE TABLE [fil_Films] (
> [fil_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [fil_Film] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> CONSTRAINT [PK_Tfil_Film] PRIMARY KEY NONCLUSTERED
> (
> [fil_ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_fil_Films] UNIQUE NONCLUSTERED
> (
> [fil_Film]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO

First question: if you have a natural key in fil_Film, why add another
extra key?

> When I insert data, should I check in the SP to see if there is an
> existing record or simply catch the error if it already exists? Which
> is the better technique? My current SP looks like:

Explicit check is better, since else the application must be able to
cope with the error. Better to let the application scream blue murder
if there is an SQL error.

Also, this can be improved:

> SELECT @.Count = Count(fil_ID) FROM fil_Films WHERE fil_Films.fil_Film
>= @.Type

IF EXISTS (SELECT * FROM fil_Films WHERE fil_Film = @.Type)

There is a slight performance gain here, as the EXISTS query terminates
on first hit.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland