Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Function with Select * that references a View Expand / Collapse
Author
Message
Posted Monday, July 19, 2010 10:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:51 AM
Points: 60, Visits: 894
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()
Post #954927
Posted Monday, July 19, 2010 10:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #954969
Posted Monday, July 19, 2010 1:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:02 AM
Points: 147, Visits: 426
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 ...
Post #955068
Posted Monday, July 19, 2010 2:48 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #955123
Posted Monday, July 19, 2010 4:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:02 AM
Points: 147, Visits: 426
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)
Post #955163
Posted Monday, July 19, 2010 5:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #955209
Posted Tuesday, July 20, 2010 2:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:02 AM
Points: 147, Visits: 426
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)
Post #955360
Posted Tuesday, July 20, 2010 4:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #955426
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse