update in a stored procedure
-
I am trying to update two different talbes in the same stored procedure but I keep getting an error message.
UPDATE dbo.User_Information SET Salutation = @Salutation, Email_1 = @Email1, Address_1 = @Address1
Where User_ID = @UserID
UPDATE dbo.MySurvey_Progress SET Start_User_Information = @Date
Where User_ID = @UserID
-
What error are you getting? Are there referential integrity constraints or update triggers on either of these tables?
And then again, I might be wrong ...
David Webb -
it tells me "Incorrect syntax near the keyword 'Else'
IF EXISTS(SELECT User_ID FROM dbo.User_Information WHERE User_ID = @UserID)
UPDATE dbo.User_Information SET Salutation = @Salutation, Email_1 = @Email1, Address_1 = @Address1, Address_2 = @Address2, City = @City, State_Province = @State, Postal_Code = @PostalCode, Home_Phone = @HomePhone, Work_Phone = @WorkPhone, Cell_Phone = @CellPhone, Fax_Phone = @FaxPhone, Birth_Date = @DateofBirth
Where User_ID = @UserID
UPDATE dbo.MySurvey_Progress SET Start_User_Information = @Date
Where User_ID = @UserID
ELSE
BEGIN
-
I think you need to wrap the updates in a begin-end block like:
IF EXISTS(SELECT User_ID FROM dbo.User_Information WHERE User_ID = @userid)
Begin
UPDATE dbo.User_Information SET Salutation = @Salutation, Email_1 = @Email1, Address_1 = @Address1, Address_2 = @Address2, City = @City, State_Province = @State, Postal_Code = @PostalCode, Home_Phone = @HomePhone, Work_Phone = @WorkPhone, Cell_Phone = @CellPhone, Fax_Phone = @FaxPhone, Birth_Date = @DateofBirth
Where User_ID = @userid
UPDATE dbo.MySurvey_Progress SET Start_User_Information = @Date
Where User_ID = @userid
END
ELSE
And then again, I might be wrong ...
David Webb -
That worked, thank you. I have been trying to figure it out all day.
-
IF EXISTS(SELECT User_ID FROM dbo.User_Information WHERE User_ID = @userid)
Begin
UPDATE dbo.User_Information SET Salutation = @Salutation, Email_1 = @Email1, Address_1 = @Address1, Address_2 = @Address2, City = @City, State_Province = @State, Postal_Code = @PostalCode, Home_Phone = @HomePhone, Work_Phone = @WorkPhone, Cell_Phone = @CellPhone, Fax_Phone = @FaxPhone, Birth_Date = @DateofBirth
Where User_ID = @userid
There doesn't seem much point in the 'IF EXISTS" part. Just do the update and check the value of @@rowcount. If @@rowcount is > 0 then the update worked and of course the row existed! This saves doing two i/o's to the db when one will do the trick.
-
Hi there,
The IF EXISTS has value IF he is trying to validate that the user being updated (based on the parameter @userid supplied) is in the database. Which is good practice, since trying to update a user that doesn't exist doesn't go over that well
However, to make the IF EXISTS effective, there has to be something that goes along with the ELSE portion, error handling, etc to deal with the other "option" in the proper flow of logic in an IF/ELSE statement.
I.e. code could be:
IF EXISTS (SELECT User_ID FROM dbo.User_Information WHERE User_ID = @userid)
BEGIN
UPDATE dbo.User_Information
SET Salutation = @Salutation, Email_1 = @Email1, Address_1 = @Address1
, Address_2 = @Address2, City = @City, State_Province = @State
, Postal_Code = @PostalCode, Home_Phone = @HomePhone
, Work_Phone = @WorkPhone, Cell_Phone = @CellPhone
, Fax_Phone = @FaxPhone, Birth_Date = @DateofBirth
WHERE User_ID = @userid
UPDATE dbo.MySurvey_Progress
SET Start_User_Information = @Date
WHERE User_ID = @userid
END
ELSE
RaisError ('StoredProcName - The user with the UserID supplied does not exist. Please verify the UserID supplied.',16,1)
--This error can then be returned to the calling app/code/function/etc and handled in an elegant fashion
Btw, is the intent to be using the SQL function User_ID, or has the column on this table been called "User_ID"?
Hope that helps!
Cheers,
Dolphin/Michelle.
"Work like you don't need the money;
dance like no one is watching;
sing like no one is listening;
love like you've never been hurt;
and live every day as if it were your last."
~ an old Irish proverb -
IF EXISTS (SELECT User_ID FROM dbo.User_Information WHERE User_ID = @userid)
BEGIN
UPDATE dbo.User_Information
SET Salutation = @Salutation, Email_1 = @Email1, Address_1 = @Address1
, Address_2 = @Address2, City = @City, State_Province = @State
, Postal_Code = @PostalCode, Home_Phone = @HomePhone
, Work_Phone = @WorkPhone, Cell_Phone = @CellPhone
, Fax_Phone = @FaxPhone, Birth_Date = @DateofBirth
WHERE User_ID = @userid
Perhaps if the IF EXISTS was on a different table then the it would have some added value but in this case it doesn't serve any purpose. Just the Update with a check of @@rowcount will tell you everything you need to know and save one trip to the database.
Also, you still need to check @@rowcount after each update since even after checking IF EXISTS the row may not be there when the Update is executed and no error will be raised. Only @@rowcount will tell you if any rows were affected.
-
I check to see if the User_ID exists and if it does then I update the information, and if it doesn't then I add the user's information to the table.
-
There is no need to check to see if the row exists before you do the update.
Think about it. The first thing the Update must do is check to see if the row exists! So just checking @@rowcount after the Update will tell you if the row was updated and if not you know the row/user did not exist.
In your example the IF EXISTS statement is on the same table/user_id as the Update statement there is just no point to that.
-
I am not a SQL expert so I am not familiar with the method you are talking about. Will it make a big difference in performance?
Declare @Date datetime
Set @Date = getdate()
IF EXISTS(SELECT User_ID FROM dbo.MySurvey_Positions WHERE User_ID = @UserID AND Position_ID = @PositionID)
Begin
UPDATE dbo.MySurvey_Positions SET Company_Name = @CompanyName, Position_Title = @PositionTitle, Start_Date = @StartDate, Salary = @Salary
Where User_ID = @UserID AND Position_ID = @PositionID
UPDATE dbo.MySurvey_Progress SET Start_Positions = @Date
Where User_ID = @UserID
END
ELSE
BEGIN
INSERT dbo.MySurvey_Positions(User_ID,
Company_Name, Position_Title, Start_Date, Salary)
Values(@UserID, @CompanyName, @PositionTitle, @StartDate, @Salary)
END
If I use @@rowcount then how would I tell it to add the new information if the row does not exist?
-
Declare @rowcount int,
@error int
UPDATE dbo.MySurvey_Positions SET Company_Name = @CompanyName, Position_Title = @PositionTitle, Start_Date = @StartDate, Salary = @Salary
Where User_ID = @userid AND Position_ID = @PositionID
Select @error = @@error, @rowcount = @@rowcount
-- do error checking here --
If @rowcount > 0 -- row exists and was updated
Begin
UPDATE dbo.MySurvey_Progress
SET Start_Positions = GetDate()
Where User_ID = @userid
End
ELSE -- row did not exist
BEGIN
INSERT dbo.MySurvey_Positions(User_ID,
Company_Name, Position_Title, Start_Date, Salary)
Values(@UserID, @CompanyName, @PositionTitle, @StartDate, @Salary)
END
Performace? well its one trip to the db instead of two. So if you do this 1,000 times a day thats 1,000 extra db hits. If you are doing this in other stored procedures...well you do the math.
I only hinted at error checking you can read a good article here: http://www.sommarskog.se/error-handling-II.html#presumptions
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply