August 18, 2010 at 4:41 pm
I have two seperate SQL procedures DeleteUser, to delte the user from login table and SetNewOrigIdOnXXX to update the wrong OrigID of the user who entered data with the usedID he was not supposed to . The update has to be perfomed on tables YYYY ZZZZ and UUUUU as well, now instead of creating several stored procedures, i.e. 1 for deleting users from login another for updating users from other various tables can i combine the stored procedures above and just create one procedure to accomplish all these tasks in various tables at once??
1) ALTER PROCEDURE [dbo].[DeleteUser]
@userid nvarchar(15) = 0,
@USERName nvarchar(30) = 0,
@USERLastName nvarchar(30) = 0
AS
BEGINSET NOCOUNT ON;
DELETE From Login Where UserID = @userid And FirstName = @UserName AND LastName =@USERLastName
END
2) ALTER PROCEDURE [dbo].[SetNewOrigIdOnXXX]
@XXXIDOld nvarchar(15) = 0,
@XXXIDOld1 nvarchar(15) = 0,
@XXXIDNew nvarchar(15) = 0
AS
BEGIN SET NOCOUNT ON;
UPDATE XXXTable SET XXXID =@XXXIDNew where ORIGID =@XXXIDOld OR ORIGID =@XXXIDOld1
END
August 19, 2010 at 10:58 am
now i know that i can combine the two procedurs but yet to come up witha way to update all the XXXID's in the datbase with the new XXXID's that are exiting in multiple table tthrough out the DB, any help is highly appreciated
August 20, 2010 at 9:58 am
does anyone have any idea about this or can point me somewhere
oh by the way i now know a way to create update statements for all the tables but it is not really what i want because it was create update statements for tables that donot have the column i am lookin to update so it is kind of moot. below is what i got by searching, hope this helps someone else
SELECT 'UPDATE ',RTRIM(Name),' SET XXXID=x WHERE XXXID=y Or XXXID= z' FROM sysobjects
WHERE Type='U'
I guess i could have an IF exits in this to create update for only those tables that have da column but that doesnot help me either
thanks for the help
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply