A question of SP technique...

[Samuel Hon]

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

Sam

[Erland Sommarskog]

Samuel Hon (no*****@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Samuel Hon]

Thanks Erland