SQLTeam.com | Weblogs | Forums

Dynamically Update Views

tsql
sql2008r2
sql2008

#1

Hello,

I have created Stored Procedure which dynamically Creates 150 views based on Product that each view is created for each product. There will be changes in product or any type of modification in future or there may be new product added...etc
To over come these problem, In my Stored Procedure - I drop all my views and recreates views each night. But Now I got requirement that - The view Object ID has to be same. As I am dropping views and recreates that will create new Object ID each time. So I need an advise or solution.


#2

If the view exists then ALTER VIEW instead of DROP / CREATE VIEW. Use CREATE VIEW for any new view.


#3

P.S. if that doesn't work, in your context, and assuming that "requirement that - The view Object ID has to be same." is somewhat flexible :slight_smile: then you could create an Extended Property on the VIEW, with an ID or GUID or somesuch, and then the Other Process could check the Extended Property, to find the VIEW, instead of the Object ID.

But i suppose if the Other Process could do that ... then it could just find the Obejct_ID for the VIEW using the NAME of the view ...


#4

The reason why I am creating views is that one of our integration partner need to access these views. They can only access views through ObjectID. So my views object ID should not be changing each night. They are not able to access if its changes each night.


#5

[quote="faiyazu99, post:4, topic:10253, full:true"]
The reason why I am creating views is that one of our integration partner need to access these views. They can only access views through ObjectID. [/quote]

I can't imagine how they are accessing views through ObejctID ... they could just as easily hold the View Name (provided that doesn't change) and use that ... and find the ObjectID from that ... or use an Extended Property, instead of ObjectID to find it via that, it's as broad as it is long - and ALL much easier to manage than the requirement that a VIEW's ObjectID must NEVER change ... but there we go, mine not to reason why, its just that I think that its a daft requirement, because there may well be circumstances where you HAVE to drop the view and then you will NOT be able to reassign it to the original ObjectID period.

Here's an example: Your view is based on an Object with SCHEMA BINDING. At some future point that Schema Bound object needs to change, so your VIEW(s), and anything else depending on it, will have to be dropped first, and then recreated (with new ObjectIDs)

As I said earlier, using ALTER rather than DROP / CREATE will achieve that.


#6

Oh. ok. Thanks for the Help. I really appreciate it.