|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
SO i figured out that i need to place the 'BEGIN' & 'END' blocks within the IF statement.
Any suggestions on optimising the procedure?
|
|
|
|
|
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.
|
|
|
|
|
THANKS ROB!!!!!!!
Its actually quite easy when you know how, isnt it. 
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
You have to create a linked server first.
There is no foolish question, there is no final answer...
|
|
|
|
|
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.
|
|
|
|
|
ohhh.
sp_addlinkedsever is an SQL command that is included in the stored procedure
|
|
|
|
|
|
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.
|
|
|
|
|
Ya go ahead and let us know if any issue. All The Best 
|
|
|
|
|
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
|
|
|
|
|
Be sure to set delegation too. If you fail to do so you will run into problems.
From BOL:
SQL Server and Windows can be configured to enable a client connected to an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user. This arrangement is known as delegation. Under delegation, the instance of SQL Server to which a Windows user has connected by using Windows Authentication impersonates that user when communicating with another instance. Security account delegation is required for distributed queries when a self-mapping is used for a specific login against a specific linked server.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Looks like I need to take a few steps backwards. I can’t even get a table in the local DB to update.
As I said, this is my first SP.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [domain\me].[TestUpdate]
AS
BEGIN
update gis.crw_gis_data set block = 'test' where id = '123'
END
GO
Shouldn’t the above code update the table? I’m connected as the DB owner.
|
|
|
|
|
Hi Experts
I want To Encrypt my whole Database With Single Command or Any Query in SQL Server 2005
is Their Any Way To Encrypt The Database
Thanku
Dinesh Sharma
|
|
|
|
|
How do you mean "Encrypt my whole Database"?! Do you want to encrypt data inside tables?
By the way please type normal words, don't use CapsLock for every first letter of the word.
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.
|
|
|
|
|
i want encrypt data inside the table of database .
actually i am create my database and software using C#.Net now i face the problem when my customer tell me that i want that the data inside the table of my database is encrypted no one can see it
thank u
Dinesh Sharma
|
|
|
|
|
|
Sharma Dinesh Kumar wrote: i want encrypt data inside the table of database .
This is very different from your original stated problem. Are you sure this is now correct, or do you want to encrypt only a column, or Store Proc?
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
|
Hi,
encrypting the data will slow down all operations, especially search operations.
anyway, isn't username-and-password protection enough?
Luc Pattyn [Forum Guidelines] [My Articles]
DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
