1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

TSQL - Can I 'recompile' all TVFs?

Discussion in 'HTML, Graphics & Programming' started by Pho, 19 Apr 2010.

  1. Pho

    Sgarrista

    Joined: 18 Oct 2002

    Posts: 9,206

    Location: Derbyshire

    Not sure what the correct wording is, but essentially if I have one TVF which uses another TVF, i.e:

    TVF1:
    PHP:
    SELECT FROM Table WHERE ID = @ID
    And another TVF which calls TVF1 at some point, i.e.:
    PHP:
    SELECT Col1Col2 FROM TVF1(100)
    and then I make some changes to TVF1 (i.e. change the column order) TVF2 returns data from the wrong columns - so if Col2 was originally the second column, but is now the third column in TVF1 I'll see the data from the second column still.

    I can fix this by opening up TVF1 and executing the 'ALTER FUNCTION' code, but this is annoying and easy to forget when moving to the live site.

    Does anyone have any ideas?

    Cheers.
     
  2. Deanje

    Hitman

    Joined: 16 Oct 2006

    Posts: 526

    Location: U.K.

    Try using the stored proc sp_recompile.
     
  3. Pho

    Sgarrista

    Joined: 18 Oct 2002

    Posts: 9,206

    Location: Derbyshire

    Sorry for the late reply.

    Looks like this only works for stored procedures, not TVFs :(.
     
  4. SimonCHere

    Wise Guy

    Joined: 24 Jun 2008

    Posts: 1,168

    Don't use select *. Name the columns? then your second one shouldn't have any problems.
     
  5. Pho

    Sgarrista

    Joined: 18 Oct 2002

    Posts: 9,206

    Location: Derbyshire

    True, but if we change the 'base' TVF and have a lot of other TVFs using it then it would be a pain to update everything else still - and would still require a manual update of the TVF with the new column names.

    The base TVF is essentially pulling out the raw data from the tables and takes a few filter parameters to shape what data we get. The derived TVFs then perform different calculations on this data depending on what you want to do with it - so you'll end up with all the base data + new columns with the calculations results. We don't need to change these too often so it's not a huge deal but would be nice if it could be done automatically so to speak.