SP: if exisit, then .... execute

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)

SP: if exisit, then .... execute

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-03-16 : 06:39:24

hello,
i have this code below that inserts phone numbers into a table. HOwever i would like to know how i could add a search to the script to check to see if the number exists before entering it into the table ?

thanks


AS INSERT INTO [1wday_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 .... BLAH BLAH BLAH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-16 : 06:50:26

If exists (select * from table where phoneno=value)
'No need to insert
else
'Insert

Madhivanan

Failing to plan is Planning to fail

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-03-16 : 06:54:18

Gracia Mon ami
Afrika

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-03-16 : 06:56:38

What a minute,

would this work for a union clause statement.

REASON; i have 10 entries with the union clause below.

AS INSERT INTO [1wday_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 .... BLAH BLAH BLAH

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-03-16 : 06:58:34

do i do a repeat for the where clause of the 1st line below ?

If exists (select * from sms_phonebook where phone_number = @phone_number_1 and username = @username_1)
else
INSERT INTO [1newday_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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-16 : 07:13:20

will this give you some ideas?
If NOT exists (select * from sms_phonebook where phone_number = @phone_number_1 and username = @username_1)
begin

insert into (...)
select ...
from
(
select ...
union all
select ...
union all
select ...
union all
etc
) t

end

Go with the flow & have fun! Else fight the flow

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-03-16 : 07:26:19

Yes, really sounds good, although i had posted another thread with this regard, but this takes care of it

thanks Spirit1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources