Check for an entry before Inserting a record

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)

Check for an entry before Inserting a record

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-03-06 : 12:16:10

hello
I have this Stored Proceedure that inserts up to 10 entries in my db, based on the users entries.

Its a phone book entry, however i would like to confirm before entry that the user hasnt entered the phone number before running the insert. Please correct me in the code below

DO I USE AN IF EXISTS or just leave it the way it is ?

thanks
Afrika

INSERT INTO [1day_com_sql].[dbo].[sms_phonebook]
([username],
[Phone_number],
[Nickname])
select
@username_1,
@Phone_number_1,
@Nickname_1

where @Phone_number_1 is not null and @phone_number_1 <> '23480' and @phone_number_1 <>
(select @Phone_number_1 from sms_phonebook where username = @username_1)
union all

select
@username_1,
@Phone_number_2,
@Nickname_2
where @Phone_number_2 is not null and @phone_number_2 <> '23480' and @phone_number_2 <>
(select @Phone_number_2 from sms_phonebook where username = @username_1)

... code continues till 10

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-06 : 13:18:00

If your sub-query can only return 1 record then that will work. If it can return more than 1 reocrd then you'll just need to change the "<>" to a "NOT IN".


and	@phone_number_1 NOT IN 
			(select @Phone_number_1 
			from sms_phonebook 
			where username = @username_1)

Be One with the Optimizer
TG

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-03-06 : 13:22:47

Yes its only one record returned.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources