Friday, March 30, 2012

question with a stored procedure

I have a stored procedure that inserts a new row if the user doesn't exist in the table...that part works. I then need it to check to see if the user has changed their program code and update the row if they have. the way I'm trying to accomplish this is by selecting their id and program code by what I'm passing it, and if it does not exist, I update the row. For some reason it's not updating the table. I'm new to stored procedures so I'm not sure if I'm doing this correctly or not. Can you please take a look and let me know if this looks ok? This is for MSSQL 2000:

CREATE PROCEDURE [dbo].[InsertUpdateProcedure] (@.LastNamevarchar(255),@.FirstNamevarchar(255),@.Emailvarchar(255),@.ColleagueIDvarchar(50),@.Programvarchar(50))AS IFNOT EXISTS(SELECT users_colleague_idFROM ept_usersWHERE users_colleague_id = @.ColleagueID)BEGIN--The row doesn't exist. Insert code goes hereINSERT INTO ept_users (users_colleague_id,users_last_name,users_first_name,users_email_address,users_program)VALUES(@.ColleagueID,@.LastName,@.FirstName,@.Email,@.Program)ENDIFNOT EXISTS(SELECT users_colleague_idFROM ept_usersWHERE users_colleague_id = @.ColleagueIDAND users_program = @.Program)BEGINUPDATE ept_usersSETusers_program = @.Program,users_email_sent = 0,users_billed_current = 0,users_second_email_sent = 0WHEREusers_colleague_id = @.ColleagueIDENDGO
nevermind...it was choking on some null values. All is working now.sql

No comments:

Post a Comment