SQL2005 Stored Procedure Conditional Statement
-
Hi, Im still fairly new to stored procedures but have reached a point where i need to use a conditional statement. At present i have to code below which will not compile due to various errors. Please can you help me understand how i can make this work. Thanks;
CREATE PROCEDURE web.InsertManualAddressBook
(
@tmp_custID bigint
)
AS
BEGIN
IF EXISTS (SELECT * FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default')=1
SELECT * FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID
DELETE FROM tbl_AddressBookAddresses WHERE tbl_AddressBookAddresses.adds_ABMId = tbl_AddressBookMaster.addMaster_Key;
ELSE
'Do something else
ENDEssentially what i want to do is; 1)Check if a record exists based on the input @tmp_custID(bigint) value. 2)If a record exists check a second table for records where the 'addMaster_UserID' column matches the 'addMaster_Key' of the initial table queried. Delete this records. 3)If the condition in item 1 isnt met, do something else. Any help is correcting the above statement would be really appreciated. Thanks.
-
Hi, Im still fairly new to stored procedures but have reached a point where i need to use a conditional statement. At present i have to code below which will not compile due to various errors. Please can you help me understand how i can make this work. Thanks;
CREATE PROCEDURE web.InsertManualAddressBook
(
@tmp_custID bigint
)
AS
BEGIN
IF EXISTS (SELECT * FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default')=1
SELECT * FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID
DELETE FROM tbl_AddressBookAddresses WHERE tbl_AddressBookAddresses.adds_ABMId = tbl_AddressBookMaster.addMaster_Key;
ELSE
'Do something else
ENDEssentially what i want to do is; 1)Check if a record exists based on the input @tmp_custID(bigint) value. 2)If a record exists check a second table for records where the 'addMaster_UserID' column matches the 'addMaster_Key' of the initial table queried. Delete this records. 3)If the condition in item 1 isnt met, do something else. Any help is correcting the above statement would be really appreciated. Thanks.
Ok dude, let's have a shot. in step 1, I presume you're checking for a unique record, in which case:
declare @key [datatype of key]
select @key = [key field] from tbl_addressBookMaster where .....will get the key if ones exists. It will either contain your key or will be null, so to see if a record did match we can just do
if @key is not null
to do the delete is straightforward
delete from tbl_AddressBookAddresses where Key = @key
so, all together you'd have something like this
declare @key [datatype of key]
select @key = [key field] from tbl_addressBookMaster where .....
if @key is not null
delete from tbl_AddressBookAddresses where Key = @key
else
-- do your other thing hereMake any sense?
Regards, Rob Philpott.
-
Ok dude, let's have a shot. in step 1, I presume you're checking for a unique record, in which case:
declare @key [datatype of key]
select @key = [key field] from tbl_addressBookMaster where .....will get the key if ones exists. It will either contain your key or will be null, so to see if a record did match we can just do
if @key is not null
to do the delete is straightforward
delete from tbl_AddressBookAddresses where Key = @key
so, all together you'd have something like this
declare @key [datatype of key]
select @key = [key field] from tbl_addressBookMaster where .....
if @key is not null
delete from tbl_AddressBookAddresses where Key = @key
else
-- do your other thing hereMake any sense?
Regards, Rob Philpott.
Hey Rob, Looks like that should be what im after. It'll probably be morning before i test it though. Ill let you know how i get on. Thanks!!!!
-
Ok dude, let's have a shot. in step 1, I presume you're checking for a unique record, in which case:
declare @key [datatype of key]
select @key = [key field] from tbl_addressBookMaster where .....will get the key if ones exists. It will either contain your key or will be null, so to see if a record did match we can just do
if @key is not null
to do the delete is straightforward
delete from tbl_AddressBookAddresses where Key = @key
so, all together you'd have something like this
declare @key [datatype of key]
select @key = [key field] from tbl_addressBookMaster where .....
if @key is not null
delete from tbl_AddressBookAddresses where Key = @key
else
-- do your other thing hereMake any sense?
Regards, Rob Philpott.
Rob, Ive adapted my SP to the following but it wont compile as it says there is a syntax error near the 'else' bit;
CREATE PROCEDURE web.InsertManualAddressBook
(
@tmp_custID bigint
)
AS
declare @key bigint
SELECT @key = addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default'
IF @key IS NOT null
DELETE FROM tbl_AddressBookAddresses WHERE adds_ABMId = @key;
ELSE
-- do your other thing here -
Rob, Ive adapted my SP to the following but it wont compile as it says there is a syntax error near the 'else' bit;
CREATE PROCEDURE web.InsertManualAddressBook
(
@tmp_custID bigint
)
AS
declare @key bigint
SELECT @key = addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default'
IF @key IS NOT null
DELETE FROM tbl_AddressBookAddresses WHERE adds_ABMId = @key;
ELSE
-- do your other thing hereOk, SO ive updated at, and it seems to be working ok although not fully tested. Does this look correct?
CREATE PROCEDURE web.InsertManualAddressBook
(
@tmp_custID bigint
)
AS
BEGIN
declare @key bigint
SELECT @key = addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default'
IF @key IS NOT null
DELETE FROM tbl_AddressBookAddresses WHERE adds_ABMId = @key;
ELSE
-- do your other thing here
SELECT @@ROWCOUNT
ENDThanks!!!!!!!!
-
Ok, SO ive updated at, and it seems to be working ok although not fully tested. Does this look correct?
CREATE PROCEDURE web.InsertManualAddressBook
(
@tmp_custID bigint
)
AS
BEGIN
declare @key bigint
SELECT @key = addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default'
IF @key IS NOT null
DELETE FROM tbl_AddressBookAddresses WHERE adds_ABMId = @key;
ELSE
-- do your other thing here
SELECT @@ROWCOUNT
ENDThanks!!!!!!!!
Looks good! Not sure the point of the @@rowcount, but I'm sure these is one.
Regards, Rob Philpott.
-
Looks good! Not sure the point of the @@rowcount, but I'm sure these is one.
Regards, Rob Philpott.
@@rowcount was purely to show that the 'Else' condition was being fired. Thanks for all the help, really appreciate it.
-
Ok dude, let's have a shot. in step 1, I presume you're checking for a unique record, in which case:
declare @key [datatype of key]
select @key = [key field] from tbl_addressBookMaster where .....will get the key if ones exists. It will either contain your key or will be null, so to see if a record did match we can just do
if @key is not null
to do the delete is straightforward
delete from tbl_AddressBookAddresses where Key = @key
so, all together you'd have something like this
declare @key [datatype of key]
select @key = [key field] from tbl_addressBookMaster where .....
if @key is not null
delete from tbl_AddressBookAddresses where Key = @key
else
-- do your other thing hereMake any sense?
Regards, Rob Philpott.
Rob, Ive started to develop things a bit more now towards where i need to be going. However im now having trouble with something a bit more complex, please take a look at the new thread: <a href="http://www.codeproject.com/Messages/3078712/Stored-Procedure-with-complex-ish-IF-ELSE.aspx"> http://www.codeproject.com/Messages/3078712/Stored-Procedure-with-complex-ish-IF-ELSE.aspx [^] Thanks!