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

a query using GROUP BY or?

Is there a simple way to do the following?
The database table has many records, each record has its own unique RecordID
(PK, int), some of the records can have one text field like an intenrifier
(SomeID) with the same value. A simplified schema is looking like this:
RecordID SomeID Action
1 134 2
2 123 2
3 1243 2
4 134 1
5 1ytr 2
6 1fgh 2
7 1243 1
8 hgf 2
9 b4rfg 2
I need to assign the value '1' or '2' to the Action field so that if we
order the whole list by the RecordID and then group it by the SomeID field,
the first record in each group (with the same SomeID field) should have
Action=2, all next entries inside each group should have Action=1. All
records without duplicates should have Action=2.
I can set Action=2 to all records, it's fast and easy. How can I assign '1'
to all appropriate (duplicate) records?
Thanks,
Just D.Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
CREATE TABLE Foobar
(foo_id INTEGER NOT NULL PRIMARY KEY,
grp_id CHAR(5) NOT NULL,
action_code INTEGER DEFAULT 2 NOT NULL
CHECK (action_code IN (1,2)));
Action=2, all next entries inside each group should have Action=1. All
records [sic] without duplicates should have Action=2. I can set
Action=2 to all records [sic], it's fast and easy. How can I assign '1'
to all appropriate (duplicate) records [sic] ? <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. A normalized table should not have redundant duplicates.
I am going to guess that this is what you want:
UPDATE Foobar
SET action_code
= CASE WHEN foo_id
< (SELECT MAX(foo_id)
FROM Foobar AS F1
WHERE F1.grp_id = Foobar.grp_id)
THEN 1 ELSE 2 END;|||Try this one:
update tbl set
Action =
case when RecordID = (select min(RecordID) from tbl as t where
t.SomeID = tb.SomeID)
then 2
else 1
end|||HI,
Excellent! That's a very good idea! Thanks!
Just D.
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1127474970.840245.308070@.g44g2000cwa.googlegroups.com...
> Try this one:
> update tbl set
> Action =
> case when RecordID = (select min(RecordID) from tbl as t where
> t.SomeID = tb.SomeID)
> then 2
> else 1
> end
>