Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    9

    Unanswered: Stored Procedure Help!

    I have a table with one column called 'Abbreviation' that contains the values of Column names for another table. I want to update the columns of this table that match only with the abbreviations. How would I do this?

    Please Help!!

    Thank You!

  2. #2
    Join Date
    Oct 2002
    Posts
    66
    Can you give us more detail on the structure and exactly what it is you are trying to do?

  3. #3
    Join Date
    Mar 2004
    Posts
    9
    I have a stored procedure that I am currently having to hard code...it is used by two different applications that have the same table name...but use different columns based on the app...

    Here is the stored procedure:
    If @isReject = 0
    Begin
    Declare @QueryX nvarchar(500)
    If @Application_ID = 108
    Begin
    Set @QueryX= 'Update DBLandfillUser.tbl_ObjectApprovals' +
    @AppName + ' Set BUE = null, BCT = null, BUM = null, ' +
    'MAD = null, MAM = null, GCT = null, GE = null, ' +
    'CA = null, CE = null, CVP = null Where object_id = ' +
    Cast(@Object_ID as Varchar(20))
    End
    ELSE
    Begin
    Set @QueryX= 'Update DBLandfillUser.tbl_ObjectApprovals' +
    @AppName + ' Set BUE = null, BUM = null, ' +
    'MAM = null, GCT = null, GE = null, ' +
    'CA = null, CE = null, CVP = null Where object_id = ' +
    Cast(@Object_ID as Varchar(20))
    End

    Exec sp_executeSQL @QueryX
    End
    End

    I want to get rid of the hard coding by comparing the column names to the rows that contain the column names in a temporary table...

    Does this make sense...and is it worth the try???

    Thanks alot!

  4. #4
    Join Date
    Oct 2002
    Posts
    66
    I would definitely give it a shot and keep playing with it.

    I don't know if there was a BEGIN statement before your excerpt, but you have an extraneous END at the end.

    It looks like you will ALWAYS want run this code :

    'Update DBLandfillUser.tbl_ObjectApprovals' + @AppName + ' Set BUE = null, BUM = null, MAM = null, GCT = null, GE = null, CA = null, CE = null, CVP = null Where object_id = ' + Cast(@Object_ID as Varchar(20))

    and only set BCT and MAD to NULL when @Application_ID = 108. If that is a rare condition, I might do the firts statement unconditionally, and set these 2 fields to null only when needed.

  5. #5
    Join Date
    Mar 2004
    Posts
    9
    That helps a little...I guess what I was hoping for is to eliminate having to hardcode any of the column names...I just didn't know if this was possible


    Thanks for responding so quickly! I appreciate your help very much!

  6. #6
    Join Date
    Oct 2002
    Posts
    66
    No the column names unfortunately do need to be hardcoded (unless you want to get tricky with the system tables), but you could always put this statement:

    'Update DBLandfillUser.tbl_ObjectApprovals' + @AppName + ' Set BUE = null, BUM = null, MAM = null, GCT = null, GE = null, CA = null, CE = null, CVP = null Where object_id = ' + Cast(@Object_ID as Varchar(20))

    into another stored procedure, and call that as often as necessary. That will prevent you from having to have it hardcoded more than once.

    Coming from an OOP background, I still have the habit of breaking my SPs into little ones than can be called repeatedly from others.

  7. #7
    Join Date
    Mar 2004
    Posts
    9
    That looks like a good idea...I'll give it a try...

    Thanks again!

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    would somthing like this work?

    Code:
    declare @Application_ID int
    declare @Tmp table (BUE int, BCT int, BUM int, MAD int, MAM int, GCT int, GE int, CA int, CE int, CVP int, object_id int)
    insert into @Tmp values( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1)
    insert into @Tmp values(10,11,12,13,14,15,16,17,18,19, 2)
    insert into @Tmp values(20,21,22,23,24,25,26,27,28,29, 3)
    select * from @Tmp
    
    set @Application_ID = 108
    Update @Tmp
       Set BUE = null
         , BCT = case @Application_ID when 108 then null else BCT end
         , BUM = null
         , MAD = null
         , MAM = case @Application_ID when 108 then null else MAM end
         , GCT = null
         , GE = null
         , CA = null
         , CE = null
         , CVP = null 
     Where object_id = 2
    select * from @Tmp
    
    set @Application_ID = 10
    Update @Tmp
       Set BUE = null
         , BCT = case @Application_ID when 108 then null else BCT end
         , BUM = null
         , MAD = null
         , MAM = case @Application_ID when 108 then null else MAM end
         , GCT = null
         , GE = null
         , CA = null
         , CE = null
         , CVP = null 
     Where object_id = 3
    select * from @Tmp
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Mar 2004
    Posts
    9
    I was looking for a way that I didn't have to hard-code the column names...For instance, I wanted to build a temporary table that would grab the names of the columns I needed to update a specific app...

    I wanted to create a loop with a variable that would take the value of the row and compare it with the title of the column


    You can look at it like you are trying to multiply matrices except there is no math involved

    I think I was getting in way over my head!

  10. #10
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    doh!

    Okay, sorry about that. Yea, you can use tables to help you build quiries but I would ask my self dos the quiry info change requiring me to use table to drive everything or am I looking for a code solution?

    IMNSHO, I would opt for maintainability. If the table driven solution really is the way to go, set it up. I suspect who ever needs to work on this code after you will need a good explination of how and why.

    If you opt for the table driven solution I would be glad to help a little later this week. Post what you have and I will take a look.
    Paul Young
    (Knowledge is power! Get some!)

  11. #11
    Join Date
    Mar 2004
    Posts
    9
    Okay...This is what I started working on

    ************************************************** *******
    Declare @QueryX nvarchar(500)
    Declare @FieldName varchar(20)

    Create Table #UpdateRejDoc
    (
    Abbreviation varchar(20)
    )
    Insert into #UpdateRejDoc
    Select Abbreviation
    From tbl_Titles
    left JOIN tbl_TitleRouting on tbl_Titles.Title_ID = tbl_TitleRouting.Title_ID
    Where tbl_TitleRouting.Application_ID = @Application_ID
    While Exists (Select Abbreviation from #UpdateRejDoc)
    Begin

    Set @QueryX = 'Update DBLandfillUser.tbl_ObjectApprovals' +
    '@AppName + Set @FieldName = null Where object_id =' + Cast(@object_id as VarChar(20))
    End

    Exec sp_executeSQL @QueryX

    ************************************************** **********

    I'm not sure if that is how you would loop through the values in Abbreviation or not....I also don't know how to put the variable in place where it can take this value and know that it is a name of the column and the value inside that I want to change to null


    If you can help that's great!! If not...I understand completely!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •