Is there a beter way to do this?

It seems that nulls r havoc for queries using variables (you have to use
'is' instead of '=' for null).
My objective is to check if a record is already on file to decide if one
needs to be added or if it already exists before continuing the procedure.
I know I could try to insert it and see if it fails because of a dupe,
but that seems like the wrong way to do it (I also do not see another
approach for when trying to do a select).
Is there a better way to do this given the table definition?
The index is: person_name_ind
The approach I am taking is below the table definition.
The Table is defined as:
-- Person Table
CREATE TABLE person
( personID int IDENTITY (1,1) not null ,
prefix nvarchar(6) null,
firstname varchar(15) not null,
MI char(1) null,
lastname varchar(20) not null,
suffix nvarchar(6) null,
notes varchar(1000) null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
CONSTRAINT PK_personID PRIMARY KEY CLUSTERED(personID),
CONSTRAINT FK_pers_prefix FOREIGN KEY (prefix) REFERENCES prefix(prefix),
CONSTRAINT FK_pers_suffix FOREIGN KEY (suffix) REFERENCES suffix(suffix),
CONSTRAINT FK_pers_updateby FOREIGN KEY (updateby) REFERENCES users
(userID),
)
go
CREATE UNIQUE INDEX person_name_ind
ON person (prefix, firstname, lastname, MI, suffix)
go
Proposed start of procedure is:
SET NOCOUNT ON
DECLARE
@.pf nvarchar(12),
@.fn varchar(15),
@.mi char(1),
@.ln varchar(20),
@.sf nvarchar(12),
@.rowcount int
SELECT
@.pf = 'Mr.',
@.fn = 'Test',
@.mi = null,
@.ln = 'User',
@.sf = 'Sr.'
-- Check if prefix, MI, suffix are null
IF (@.pf is null AND @.mi is null and @.sf is null)
-- SELECT with prefix, MI, suffix null
BEGIN
SELECT * FROM person WITH (INDEX (person_name_ind))
WHERE (prefix is null
AND firstname = @.fn
AND MI is null
AND lastname = @.ln)
SET @.rowcount = @.@.ROWCOUNT
IF @.rowcount > 0
GOTO record_found
ELSE
GOTO record_not_found
END
-- Check if prefix, suffix are null, MI not null
IF (@.pf is null AND @.mi is not null and @.sf is null)
-- SELECT with prefix, suffix null, MI not null
BEGIN
SELECT * FROM person WITH (INDEX (person_name_ind))
WHERE (prefix is null
AND firstname = @.fn
AND MI = @.mi
AND lastname = @.ln
AND suffix is null)
SET @.rowcount = @.@.ROWCOUNT
IF @.rowcount > 0
GOTO record_found
ELSE
GOTO record_not_found
END
-- Check if prefix null, suffix and MI not null
IF (@.pf is null AND @.mi is not null and @.sf is not null)
-- SELECT with prefix null, suffix and MI not null
BEGIN
SELECT * FROM person WITH (INDEX (person_name_ind))
WHERE (prefix is null
AND firstname = @.fn
AND MI = @.mi
AND lastname = @.ln
AND suffix = @.sf)
SET @.rowcount = @.@.ROWCOUNT
IF @.rowcount > 0
GOTO record_found
ELSE
GOTO record_not_found
END
-- Check if suffix and MI null, prefix not null
IF (@.pf is not null AND @.mi is null and @.sf is null)
-- SELECT with prefix not null, suffix and MI null
BEGIN
SELECT * FROM person WITH (INDEX (person_name_ind))
WHERE (prefix = @.pf
AND firstname = @.fn
AND MI is null
AND lastname = @.ln
AND suffix is null)
SET @.rowcount = @.@.ROWCOUNT
IF @.rowcount > 0
GOTO record_found
ELSE
GOTO record_not_found
END
-- Check if MI null, prefix and suffix not null
IF (@.pf is not null AND @.mi is null and @.sf is not null)
-- SELECT with MI null, prefix and suffix not null
BEGIN
SELECT * FROM person WITH (INDEX (person_name_ind))
WHERE (prefix = @.pf
AND firstname = @.fn
AND MI is null
AND lastname = @.ln
AND suffix = @.sf)
SET @.rowcount = @.@.ROWCOUNT
IF @.rowcount > 0
GOTO record_found
ELSE
GOTO record_not_found
END
-- Check if with suffix null, prefix and MI not null
IF (@.pf is not null AND @.mi is not null and @.sf is null)
-- SELECT suffix null, prefix and MI not null
BEGIN
SELECT * FROM person WITH (INDEX (person_name_ind))
WHERE (prefix = @.pf
AND firstname = @.fn
AND MI = @.mi
AND lastname = @.ln
AND suffix is null)
SET @.rowcount = @.@.ROWCOUNT
IF @.rowcount > 0
GOTO record_found
ELSE
GOTO record_not_found
END
-- Check if suffix, prefix and MI not null
IF (@.pf is not null AND @.mi is not null and @.sf is not null)
-- SELECT with suffix, prefix and MI not null
BEGIN
SELECT * FROM person WITH (INDEX (person_name_ind))
WHERE (prefix = @.pf
AND firstname = @.fn
AND MI = @.mi
AND lastname = @.ln
AND suffix = @.sf)
SET @.rowcount = @.@.ROWCOUNT
IF @.rowcount > 0
GOTO record_found
ELSE
GOTO record_not_found
END
ELSE
GOTO this_should_not_happen
record_found:
PRINT 'Name already on file'
GOTO get_out
record_not_found:
PRINT 'Name is not yet on file'
GOTO get_out
this_should_not_happen:
PRINT 'You screwed up somewhere'
get_out:
goOk, from someone else's post earlier I found out I could code as below.
It is a lot better.
But is there a way I can check the index for a matching value?
What if I don't want anything from the row, I just want to see if there is
already a matching index out there, is there no choice but to try to select
in order to find out?
Thank you in advance.
SET NOCOUNT ON
DECLARE
@.pf nvarchar(12),
@.fn varchar(15),
@.mi char(1),
@.ln varchar(20),
@.sf nvarchar(12),
@.rowcount int
SELECT
@.pf = 'Mr.',
@.fn = 'Test',
@.mi = null,
@.ln = 'User',
@.sf = 'Sr.'
SELECT personID
FROM person
WHERE (ISNULL(prefix,'') = ISNULL(@.pf,'')
AND firstname = @.fn
AND ISNULL(MI,'') = ISNULL(@.mi,'')
AND lastname = @.ln
AND ISNULL(suffix,'') = ISNULL(@.sf,'')
)
SET @.rowcount = @.@.ROWCOUNT
IF @.rowcount > 0
GOTO record_found
ELSE
GOTO record_not_found
record_found:
PRINT 'Name already on file'
GOTO get_out
record_not_found:
PRINT 'Name is not yet on file'
GOTO get_out
this_should_not_happen:
PRINT 'You screwed up somewhere'
get_out:
go
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uvWwdz9uFHA.3556@.TK2MSFTNGP12.phx.gbl...
> It seems that nulls r havoc for queries using variables (you have to
> use 'is' instead of '=' for null).
> My objective is to check if a record is already on file to decide if one
> needs to be added or if it already exists before continuing the procedure.
> I know I could try to insert it and see if it fails because of a dupe,
> but that seems like the wrong way to do it (I also do not see another
> approach for when trying to do a select).
> Is there a better way to do this given the table definition?
> The index is: person_name_ind
> The approach I am taking is below the table definition.
> The Table is defined as:
> -- Person Table
> CREATE TABLE person
> ( personID int IDENTITY (1,1) not null ,
> prefix nvarchar(6) null,
> firstname varchar(15) not null,
> MI char(1) null,
> lastname varchar(20) not null,
> suffix nvarchar(6) null,
> notes varchar(1000) null,
> lastupdate smalldatetime not null,
> updateby varchar(10) not null,
> CONSTRAINT PK_personID PRIMARY KEY CLUSTERED(personID),
> CONSTRAINT FK_pers_prefix FOREIGN KEY (prefix) REFERENCES prefix(prefix),
> CONSTRAINT FK_pers_suffix FOREIGN KEY (suffix) REFERENCES suffix(suffix),
> CONSTRAINT FK_pers_updateby FOREIGN KEY (updateby) REFERENCES users
> (userID),
> )
> go
> CREATE UNIQUE INDEX person_name_ind
> ON person (prefix, firstname, lastname, MI, suffix)
> go
> Proposed start of procedure is:
> SET NOCOUNT ON
> DECLARE
> @.pf nvarchar(12),
> @.fn varchar(15),
> @.mi char(1),
> @.ln varchar(20),
> @.sf nvarchar(12),
> @.rowcount int
> SELECT
> @.pf = 'Mr.',
> @.fn = 'Test',
> @.mi = null,
> @.ln = 'User',
> @.sf = 'Sr.'
> -- Check if prefix, MI, suffix are null
> IF (@.pf is null AND @.mi is null and @.sf is null)
> -- SELECT with prefix, MI, suffix null
> BEGIN
> SELECT * FROM person WITH (INDEX (person_name_ind))
> WHERE (prefix is null
> AND firstname = @.fn
> AND MI is null
> AND lastname = @.ln)
> SET @.rowcount = @.@.ROWCOUNT
> IF @.rowcount > 0
> GOTO record_found
> ELSE
> GOTO record_not_found
> END
> -- Check if prefix, suffix are null, MI not null
> IF (@.pf is null AND @.mi is not null and @.sf is null)
> -- SELECT with prefix, suffix null, MI not null
> BEGIN
> SELECT * FROM person WITH (INDEX (person_name_ind))
> WHERE (prefix is null
> AND firstname = @.fn
> AND MI = @.mi
> AND lastname = @.ln
> AND suffix is null)
> SET @.rowcount = @.@.ROWCOUNT
> IF @.rowcount > 0
> GOTO record_found
> ELSE
> GOTO record_not_found
> END
> -- Check if prefix null, suffix and MI not null
> IF (@.pf is null AND @.mi is not null and @.sf is not null)
> -- SELECT with prefix null, suffix and MI not null
> BEGIN
> SELECT * FROM person WITH (INDEX (person_name_ind))
> WHERE (prefix is null
> AND firstname = @.fn
> AND MI = @.mi
> AND lastname = @.ln
> AND suffix = @.sf)
> SET @.rowcount = @.@.ROWCOUNT
> IF @.rowcount > 0
> GOTO record_found
> ELSE
> GOTO record_not_found
> END
> -- Check if suffix and MI null, prefix not null
> IF (@.pf is not null AND @.mi is null and @.sf is null)
> -- SELECT with prefix not null, suffix and MI null
> BEGIN
> SELECT * FROM person WITH (INDEX (person_name_ind))
> WHERE (prefix = @.pf
> AND firstname = @.fn
> AND MI is null
> AND lastname = @.ln
> AND suffix is null)
> SET @.rowcount = @.@.ROWCOUNT
> IF @.rowcount > 0
> GOTO record_found
> ELSE
> GOTO record_not_found
> END
> -- Check if MI null, prefix and suffix not null
> IF (@.pf is not null AND @.mi is null and @.sf is not null)
> -- SELECT with MI null, prefix and suffix not null
> BEGIN
> SELECT * FROM person WITH (INDEX (person_name_ind))
> WHERE (prefix = @.pf
> AND firstname = @.fn
> AND MI is null
> AND lastname = @.ln
> AND suffix = @.sf)
> SET @.rowcount = @.@.ROWCOUNT
> IF @.rowcount > 0
> GOTO record_found
> ELSE
> GOTO record_not_found
> END
> -- Check if with suffix null, prefix and MI not null
> IF (@.pf is not null AND @.mi is not null and @.sf is null)
> -- SELECT suffix null, prefix and MI not null
> BEGIN
> SELECT * FROM person WITH (INDEX (person_name_ind))
> WHERE (prefix = @.pf
> AND firstname = @.fn
> AND MI = @.mi
> AND lastname = @.ln
> AND suffix is null)
> SET @.rowcount = @.@.ROWCOUNT
> IF @.rowcount > 0
> GOTO record_found
> ELSE
> GOTO record_not_found
> END
> -- Check if suffix, prefix and MI not null
> IF (@.pf is not null AND @.mi is not null and @.sf is not null)
> -- SELECT with suffix, prefix and MI not null
> BEGIN
> SELECT * FROM person WITH (INDEX (person_name_ind))
> WHERE (prefix = @.pf
> AND firstname = @.fn
> AND MI = @.mi
> AND lastname = @.ln
> AND suffix = @.sf)
> SET @.rowcount = @.@.ROWCOUNT
> IF @.rowcount > 0
> GOTO record_found
> ELSE
> GOTO record_not_found
> END
> ELSE
> GOTO this_should_not_happen
> record_found:
> PRINT 'Name already on file'
> GOTO get_out
> record_not_found:
> PRINT 'Name is not yet on file'
> GOTO get_out
> this_should_not_happen:
> PRINT 'You screwed up somewhere'
> get_out:
> go
>
>|||You do not have to use all of that procedural code to find a match.
EXISTS
(SELECT *
FROM Persons
WHERE COALESCE (prefix, ''')
= COALESCE (@.my_prefix, '?')
AND First_name = @.m,first_name
AND COALESCE (mi, '?')
= COALESCE (@.my_mi, '?')
AND last_name = @.my_last_name
AND COALESCE (suffix, ''')
= COALESCE (@.my_suffix, '''))|||Thank you
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127001335.056787.75800@.g14g2000cwa.googlegroups.com...
> You do not have to use all of that procedural code to find a match.
> EXISTS
> (SELECT *
> FROM Persons
> WHERE COALESCE (prefix, ''')
> = COALESCE (@.my_prefix, '?')
> AND First_name = @.m,first_name
> AND COALESCE (mi, '?')
> = COALESCE (@.my_mi, '?')
> AND last_name = @.my_last_name
> AND COALESCE (suffix, ''')
> = COALESCE (@.my_suffix, '''))
>|||What is the term given to '?', it works well but I wanted to read up on it
and find out more about it, what would I use to find more information in
help?
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:ub9%23or%23uFHA.904@.tk2msftngp13.phx.gbl...
> Thank you
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1127001335.056787.75800@.g14g2000cwa.googlegroups.com...
>|||Never mind, I get it now, a little slow.
Thank you
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:%23ed1X9%23uFHA.3100@.TK2MSFTNGP12.phx.gbl...
> What is the term given to '?', it works well but I wanted to read up on it
> and find out more about it, what would I use to find more information in
> help?
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:ub9%23or%23uFHA.904@.tk2msftngp13.phx.gbl...
>|||I just wanted a character that would not appear in the column. You can
guarantee that there are no question marks with a CHECK() constraint.|||A problem here is that an index will not likely be used for this query, at
least not where the nullable columns are concerned. Consider:

> WHERE COALESCE (prefix, ''')
> = COALESCE (@.my_prefix, '?') --I assume the three ?'s were a
> typo, right.
WHERE (prefix = @.my_prefix or COALESCE(@.my_prefix,''') = ''')
This should perform better, right?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127001335.056787.75800@.g14g2000cwa.googlegroups.com...
> You do not have to use all of that procedural code to find a match.
> EXISTS
> (SELECT *
> FROM Persons
> WHERE COALESCE (prefix, ''')
> = COALESCE (@.my_prefix, '?')
> AND First_name = @.m,first_name
> AND COALESCE (mi, '?')
> = COALESCE (@.my_mi, '?')
> AND last_name = @.my_last_name
> AND COALESCE (suffix, ''')
> = COALESCE (@.my_suffix, '''))
>sql