|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Is there a beter way to do this?'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 Ok, 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 Show quote "DazedAndConfused" <AceMago***@yahoo.com> wrote in message news:uvWwdz9uFHA.3556@TK2MSFTNGP12.phx.gbl... > It seems that nulls reek 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
Show quote "--CELKO--" <jcelko***@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? Show quote "DazedAndConfused" <AceMago***@yahoo.com> wrote in message news:ub9%23or%23uFHA.904@tk2msftngp13.phx.gbl... > Thank you > "--CELKO--" <jcelko***@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, '??????')) >> > > Never mind, I get it now, a little slow.
Thank you Show quote "DazedAndConfused" <AceMago***@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" <AceMago***@yahoo.com> wrote in message > news:ub9%23or%23uFHA.904@tk2msftngp13.phx.gbl... >> Thank you >> "--CELKO--" <jcelko***@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, '??????')) >>> >> >> > > 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, '??????') WHERE (prefix = @my_prefix or COALESCE(@my_prefix,'??????') = '??????')> = COALESCE (@my_prefix, '???') --I assume the three ?'s were a > typo, right. This should perform better, right? -- Show quote---------------------------------------------------------------------------- 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--" <jcelko***@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, '??????')) >  
Other interesting topics
|
|||||||||||||||||||||||
