Stored Procedure with complex(ish) IF.....ELSE

munklefish

Hi, Thanks to a massively helpful ROb ive manged to learn a little about conditional statements in Stored Procedures. However i have now created the following SP and it gives an error when compiling. Incorrect syntax near the keyword 'ELSE'

CREATE PROCEDURE web.createDefaultAddressBook
(
@tmp_custID bigint,
@tmp_Description varchar(500),
@tmp_NoRecipients bigint,
@tmp_Email varhar(100),
@tmp_Forename varchar(100),
@tmp_Surname varchar(100)
)
AS
BEGIN
declare @key bigint
declare @newSignUpID bigint
--determine id of record if exists
SELECT @key = addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default'

   IF @key IS null
   --address book doesnt exist just so create default
   
   INSERT INTO tbl\_AddressBookMaster (\[addMaster\_UserId\], \[addMaster\_Title\], \[addMaster\_Description\], \[addMAster\_NoRecipients\])
   VALUES (@tmp\_custID, 'Default', @tmp\_Description, 0)
   SELECT @newSignUpID = SCOPE\_IDENTITY()
   
  --add addresses to second table
   INSERT INTO tbl\_AddressBookAddresses (\[adds\_ABMId\], \[adds\_Email\], \[adds\_RecipientForename\], \[adds\_RecipientSurname\])
   VALUES (@newSignUpID, @tmp\_Email, @tmp\_Forename, @tmp\_Surname)

   ELSE
   --default already exists
   
   --add addresses to second table
   INSERT INTO tbl\_AddressBookAddresses (\[adds\_ABMId\], \[adds\_Email\], \[adds\_RecipientForename\], \[adds\_RecipientSurname\])
   VALUES (@key, @tmp\_Email, @tmp\_Forename, @tmp\_Surname)

END

What am i doing wrong? Is there a more efficient way to do this considering i will be using it in code which will loop through a huge array and pass new records into the SP? Effective but simple is probably best at this stage though. Thanks in advance.

Blue_Boy

munklefish wrote:

SELECT @key = addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default' IF @key IS null

I guess column addMaster_Key return integer value and your query should be: set @key = (select addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default') IF @key is null then begin end

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.

munklefish

SO i figured out that i need to place the 'BEGIN' & 'END' blocks within the IF statement. Any suggestions on optimising the procedure?

munklefish

Thats how it was suggested to me by someone else on here. That part of it works. Is it more efficient to use SET rather than the SELECT method i have used? Thanks.

Rob Philpott

Yeah, as posted above if you have more than one statement in your conditional code you need to place them in a begin-end block. As a general point, the last two inserts you have there are almost identical, the first parameter being the only difference. I think you could shorten it to this (or similiar):

IF @key IS null
begin
INSERT INTO tbl_AddressBookMaster ([addMaster_UserId], [addMaster_Title], [addMaster_Description], [addMAster_NoRecipients])
VALUES (@tmp_custID, 'Default', @tmp_Description, 0)
SELECT @key = SCOPE_IDENTITY()
end

INSERT INTO tbl_AddressBookAddresses ([adds_ABMId], [adds_Email], [adds_RecipientForename], [adds_RecipientSurname])
VALUES (@key, @tmp_Email, @tmp_Forename, @tmp_Surname)

Make sense?

Regards, Rob Philpott.

Rob Philpott

People argue about that a lot. There's really not much in it. I tend to use SET outside of a select statement (so would use it in your example).

set @now = getdate()

But either is fine.

Regards, Rob Philpott.

munklefish

THANKS ROB!!!!!!! Its actually quite easy when you know how, isnt it. :laugh:

Niladri_Biswas

The block of code inside the IF & ELSE statement should be within BEGIN and END block. Like this:

IF @key IS null
--address book doesnt exist just so create default
BEGIN
INSERT INTO tbl_AddressBookMaster ([addMaster_UserId], [addMaster_Title], [addMaster_Description], [addMAster_NoRecipients])
VALUES (@tmp_custID, 'Default', @tmp_Description, 0)
SELECT @newSignUpID = SCOPE_IDENTITY()

  --add addresses to second table
   INSERT INTO tbl\_AddressBookAddresses (\[adds\_ABMId\], \[adds\_Email\], \[adds\_RecipientForename\], \[adds\_RecipientSurname\])
   VALUES (@newSignUpID, @tmp\_Email, @tmp\_Forename, @tmp\_Surname)

 ** END**

   ELSE
   --default already exists
   **BEGIN**
   --add addresses to second table
   INSERT INTO tbl\_AddressBookAddresses (\[adds\_ABMId\], \[adds\_Email\], \[adds\_RecipientForename\], \[adds\_RecipientSurname\])
   VALUES (@key, @tmp\_Email, @tmp\_Forename, @tmp\_Surname)

    **END**

Niladri Biswas