Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bangingtunes
Starting Member
23 Posts |
 Posted - 2004-07-14 : 13:29:21
|
| Im looking for some advice as to when i should be using a view.I have a lot of stored procedures returning different selections of products, and they all need code that limits the products displayed based on display type, stock level, and release date.Would combining this into a view like SELECT *FROM dbo.ProductsWHERE (DisplayType = 1) OR (DisplayType = 2) AND (DATEADD(MONTH, 3, OfficialReleaseDate) > GETDATE()) AND (StockLevel < 1) OR (DisplayType = 3) AND (StockLevel > 0)And then updating all the stored procedures to simply get there data from the view be sensible. Seems like it but im happy to leave it as it is if there are likely to be any performance implications.Kristian |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-14 : 13:39:42
|
| So what do you do today?Pass the parameters in to the sporc?A layer is a layer...just leave it alone...Views are good for simplifying business logic and security...what else....let's go take a look in BOL....Brett8-) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-14 : 13:48:56
|
| Where I have several "Flags" etc that I need to incorporate into WHERE clauses I tend to use a UDF to give me a table of PKs to JOIN to the real table.That way the parameters to the UDF are available to me to override if necessary.So I can set my "@OnlyIfInStock" parameter to 1 or 0 as appropriate for the query and not worry about remembering that I also need to reduce StockQty by pending-orders-not-yet-shipped etc. and also any New business rules in the future that change how "In Stock" is calculated can be done centrally in the UDF.(Business Rule changes can also be done centrally in the VIEW too of course)Kristen |
|
|
|
bangingtunes
Starting Member
23 Posts |
Posted - 2004-07-14 : 14:09:48
|
| I didnt consider UDFs, there is no need for me to override the parameters though. if i do ill go straight to the tables, the view just centalizes some logic that determines which products in the product table are actually on sale. Im thinking i can rework the product table and elimiate the nondeterministic GETDATE from the view and then put a clustered index on it which should eliminate the performance worries i was having.Kristian |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-14 : 14:46:48
|
| Sounds good to me. (Whilst we have UDFs for tables which have lots of "flags" we also have views to flatten a bunch of JOINs and stuff)Kristen |
|
|
|
|
|
|
When to use a view?