Post reply

Creating a Store Procedure to Update and Delete users from different tables

  • August 18, 2010 at 4:41 pm

    #222754

    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

    BEGIN SET 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

    #1209254

    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

    #1209774

    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