Database

12-Jun-09 4:02

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_Boy12-Jun-09 4:12

Blue_Boy

12-Jun-09 4:12

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.

munklefish12-Jun-09 4:18

12-Jun-09 4:18

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 Philpott12-Jun-09 4:22

Rob Philpott

12-Jun-09 4:22

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.

munklefish12-Jun-09 4:16

12-Jun-09 4:16

SO i figured out that i need to place the 'BEGIN' & 'END' blocks within the IF statement.

Any suggestions on optimising the procedure?

Rob Philpott12-Jun-09 4:20

Rob Philpott

12-Jun-09 4:20

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.

munklefish12-Jun-09 4:35

12-Jun-09 4:35

THANKS ROB!!!!!!!

Its actually quite easy when you know how, isnt it. Laugh | :laugh:

Stored procedure across multiple servers?

Niladri_Biswas17-Jun-09 20:55

Niladri_Biswas

17-Jun-09 20:55

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

enipla12-Jun-09 3:13

12-Jun-09 3:13

I have two servers with SQL 2005 on them. Is it possible to write a stored procedure that moves data from a table in a database on server A to a table on server B?

Or must both tables be in the same Database on the same server where the stored procedure resides?

I’ve never written a SP before. I am also looking into SP written with .NET in managed code. Perhaps that is the way to go?

saanj12-Jun-09 3:16

saanj

12-Jun-09 3:16

You have to create a linked server first.

There is no foolish question, there is no final answer...

enipla12-Jun-09 3:41

12-Jun-09 3:41

Thanks. I guess there is a utility in Server Management Studio to do this?

Microsoft help (for what it's worth) mentioned that I should run sp_addlinked server. I suppose a System SP. It does not exist in this DB though.

enipla12-Jun-09 3:47

12-Jun-09 3:47

ohhh.

sp_addlinkedsever is an SQL command that is included in the stored procedure

Abhijit Jana12-Jun-09 3:53

Abhijit Jana

12-Jun-09 3:53

Here you go for Linked Server.
And
Walkthrough: Creating a Stored Procedure in Managed Code

cheers,
Abhijit
CodeProject MVP
Web Site:abhijitjana.net
View My Recent Article

enipla12-Jun-09 4:00

12-Jun-09 4:00

I'm going to need some time to digest this. Not sure if I'll do it in managed code or not.

Basically. I have 10 fields in a view that need to update multiple tables on another server. I'll probably schedule this to run nightly.

First things first. I'm going to see if I can link the servers, and list the tables.

Thanks to both of you. I suspect I will return with more questions.

Abhijit Jana12-Jun-09 4:15

Abhijit Jana

12-Jun-09 4:15

Ya go ahead and let us know if any issue. All The Best Thumbs Up | :thumbsup:

cheers,
Abhijit
CodeProject MVP
Web Site:abhijitjana.net
View My Recent Article

enipla12-Jun-09 5:11

12-Jun-09 5:11

Hmmmmm.....

Just for a test, I tried the code below. It does not through any errors, but it does not perform the update either.

Any ideas?

GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [test\me].[GIStoCRW]

AS
BEGIN

	SET NOCOUNT ON;

EXEC sp_addlinkedserver 
   @server = 'CRW',
   @provider_name = 'SQLNCLI',
   @datasrrc = 'TheServerName',
   @catalog = 'TheTablename'

EXEC sp_addlinkedsrvlogin CRW, FALSE, 'My Login’, NULL

update CRW.GEO_UDF set WATERDISTRICT = 'TEST' where SITE_APN = '123'

END
GO

Thanks