Key Exists? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Key Exists?

Author

Topic

HockeyFan
Starting Member

26 Posts

Posted - 2005-12-13 : 15:43:33

What's the best SQL statement to use to detect if a Key Exists in a particular table?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-13 : 16:11:11

depends.

"exists" is usually a good choice. ie:
if exists(select 'hockeyFan' from myTable where key = @key)

if you want to do an update or insert depending the existance then maybe:

update myTable where key = @key
if @@rowcount = 0 insert myTable

Be One with the Optimizer
TG

HockeyFan
Starting Member

26 Posts

Posted - 2005-12-13 : 16:20:42

couldn't get that to work.
Tried:
SELECT 1
FROM Folders
WHERE KEY = RecID

and tried
SELECT 1
FROM Folders
WHERE KEY = 'RecID'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-13 : 16:35:31

Does this help clear it up?


set nocount on
create table #folders (RecID int primary key)
go
insert #folders (RecID) values (12)
go

declare @RecID int
set @RecID = 12

if exists(select 1 from #folders where RecID = @RecID)
begin
	print 'exists'
end
else
begin
	print 'doesn''t exist'
end

go
drop table #folders

Be One with the Optimizer
TG

HockeyFan
Starting Member

26 Posts

Posted - 2005-12-13 : 16:44:57

Actually, I'm looking for a key.
Originally, I was using SQLDMO:

SQLDMOConnection.Databases(UCase(DatabaseName)).Tables(TableName).Keys(X).Name)

to access key names and search for a particular one.
I've decided not to use SQLDMO and would like to embedded SQL statements in my program to do the equivalent.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-12-13 : 16:55:53

Use the INFORMATION_SCHEMA views...

Look it up in BOL

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources