Function with Select * that references a View

  • In the code below, the function continues to return the columns from the original view after the view is altered. The only way I found to "fix" this is to execute sp_refreshsqlmodule. However, I need to run this for any Function or Stored Procedure that references the view. Is there a more elegant way to keep the function(s) in sync with view changes.

    If object_id('TestTable') is Not Null Drop Table TestTable

    If Object_id('TestView') is Not Null Drop View TestView

    If Object_id('TestFunction') is Not Null Drop Function TestFunction

    Create Table TestTable(EmployeeID Int,FName varchar(20),LName varchar(20))

    Insert TestTable values(1,'John' ,'Smith')

    Insert TestTable values(2,'Tom' ,'Jones')

    Insert TestTable values(1,'Sally','Smith')

    /* Initial View with 2 columns---*/

    Go

    Create View TestView as

    Select

    EmployeeID,FName

    From TestTable

    Go

    Create Function TestFunction()

    Returns Table

    As

    Return

    (

    Select * from TestView

    )

    Go

    Select 'Before Alter',* from testfunction()

    /* Modified View with 3 columns---*/

    Go

    Alter View TestView as

    Select

    EmployeeID,FName,LName

    From TestTable

    Go

    Select 'Alter Alter',* from testfunction()

  • Simple answer:

    There is no "more elegant way". You can script executing of sp_refreshsqlmodule for each UDF in your database and run this script after altering any view/table (please note that sp_refreshsqlmodule does not work for schemabound functions...).

    BTW. It is generaly a bad practicve to do SELECT *...

    You can add SCHEMABINDING option in your function. It will stop anyone altering objects referred in function without dropping function first. I know it is not what you really want, but at least it will insure your functions integrity...

    Just a sample of script to refresh all non-schemabound functions:

    DECLARE @SQL NVARCHAR(1000)

    DECLARE myFoos CURSOR FAST_FORWARD

    FOR

    SELECT 'EXEC sys.sp_refreshsqlmodule ''' + ROUTINE_SCHEMA + '.' + ROUTINE_NAME + ''''

    FROM INFORMATION_SCHEMA.ROUTINES

    WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_DEFINITION NOT LIKE '%SCHEMABINDING%'

    OPEN myFoos

    FETCH NEXT FROM myFoos INTO @SQL

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @SQL

    EXEC sp_executesql @SQL

    FETCH NEXT FROM myFoos INTO @SQL

    END

    CLOSE myFoos

    DEALLOCATE myFoos

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • I wrote a tool for this type of dependency refresh problem

    it calculates the dependency graph for a group of changing objects

    and generates the correct dependency-aware script to refresh the objects

    it detects schemabound objects and correctly removes the schemabinding

    after the objects are changed it replaces the schemabinding and recreates

    any indexes (on views) that were silently dropped during the alter

    because it performs all the operations in the correct order the fragile sys.depends in SQL 2005

    stays intact (normally it deletes all child dependency records when a parent changes)

    furthermore this approach avoids the nasty SQL problem of intermediate execution plans

    being cached with stale column indices - this can result in columns jumping:

    select [name], [address]

    can end up returning

    [name], [some other column]

    if you alter objects in the wrong order

    I have been curious for years how other SQL developers solve these issues - when I ask them

    they just look blank ...

  • doobya (7/19/2010)


    ...

    I have been curious for years how other SQL developers solve these issues - when I ask them

    they just look blank ...

    Other SQL developers may be not generaly using "SELECT * ..." in such situations...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Eugene Elutin (7/19/2010)


    Other SQL developers may be not generaly using "SELECT * ..." in such situations...

    Huh? I don't mean SELECT *

    I mean solve the general dependency / refresh problem in SQL Server

    especially in the case of hot / online schema changes

    OP's issue wasn't the * it was refreshing the view (and dependent objects)

    (The refresh idea above won't work properly in many cases)

  • doobya (7/19/2010)


    I wrote a tool for this type of dependency refresh problem

    it calculates the dependency graph for a group of changing objects

    and generates the correct dependency-aware script to refresh the objects

    Care to post it?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I plan to one day soon

    Even though I use it everyday, it is still a work in progress - and not ready for general consumption

    (It is a Windows Application - not a T-SQL script)

  • To my knowledge, SQL2008 provides a better and more reliable way to check object dependency in SQL Server than in SQL2005 - it is new sys.sql_expression_dependencies. Some more details in:

    http://sqlserverplanet.com/sql-server-2008/find-dependent-objects/

    I have not tested the above.

    There was never 100% reliable way to find all dependent objects such as stored procs or udfs because of delayed name resolution. Different methods were employed to do so and they would potentially return different results (http://www.mssqltips.com/tip.asp?tip=1294). Also, this task was often achieved by parsing syscomments, however it would find depenedency even if the object name was mentioned in the comment section...

    The simple practice to not use SELECT * in views and recreating all UDFs on release can help you to ensure required integrity...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply