| Author |
Topic |
|
vaidyanathanpc
Starting Member
24 Posts |
 Posted - 2002-03-12 : 02:02:10
|
| CREATE PROCEDURE CreateMenuItems @in_cls_id int AS declare .....DECLARE cursor_name CURSOR FOR SELECT DISTINCT column1,column1,column3 .... FROM viewUserPrivileges WHERE ..... ORDER BY .....OPEN cursor_name FETCH NEXT FROM cursor_nameINTO declared local variableswhile @@FETCH_STATUS <> -1begin if <condition> begin Print 'null' insert into table.... Execute CreateMenuItems @in_cls_id end else insert into table... Print 'Not null' FETCH NEXT FROM cls_cursor INTO declared local variablesEndclose cursor_namedeallocate cursor_nameIn the above procedure CreateMenuItemscalls itself. Is this possible? If not is there any other way to achieve the same?Thanks in advance P.C. Vaidyanathan |
|
|
Lou
Yak Posting Veteran
59 Posts |
Posted - 2002-03-12 : 02:18:08
|
| I can't make any sense of what you're trying to do, unless you are deliberately trying to lock up your server. You need to be more specific. In general, avoid cursors and avoid views (they don't do anything for you unless you have indexed views and even then a JOIN is still faster). |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-03-12 : 02:33:54
|
quote:
avoid views (they don't do anything for you unless you have indexed views and even then a JOIN is still faster).
I take GREAT offense to that comment.How is a JOIN faster than a VIEW?DavidMTomorrow is the same day as Today was the day before. |
|
|
|
Lou
Yak Posting Veteran
59 Posts |
Posted - 2002-03-12 : 10:59:54
|
| My experience with views have been all bad. I've posted the BOL which sums up my problem with views, unless all of your queries are basically the same, indexed views cannot use the cached results.-----------------------------------------------------------------SET Options That Affect ResultsIndexed views and indexes on computed columns involve storing results in the database for later reference. These stored results are valid only if all connections referring to the results can generate the same result set as the connection that created the stored result set.Indexed ViewsIndexed views store the result set returned by a view by creating a clustered index on the view. For complex views, the stored result set greatly speeds data retrieval. An indexed view is useful only as long as all operations referencing the view use exactly the same algorithms when building their results. Like indexes for computed columns, this includes: The CREATE INDEX statement that first builds the result set.Any subsequent INSERT, UPDATE, or DELETE statements that affect the base data used to build the view result set.All queries for which the optimizer must determine if the indexed view will be useful. These SET options must be set correctly for any connection that creates an index on a view or computed column. Any connection executing INSERT, UPDATE or DELETE statements that change data values stored in the indexes must have the correct settings. This includes bulk copy, Data Transformation Services (DTS), and replication operations. Microsoft® SQL Server™ 2000 generates an error and rolls back any insert, update, or delete operation attempted by a connection that does not have the proper option settings. The optimizer does not consider using an index on a computed column or view in the execution plan of any Transact-SQL statement if the connection does not have the correct option settings. |
|
|
|
lfmn
Posting Yak Master
141 Posts |
Posted - 2002-03-12 : 14:32:18
|
| vaidyanathanpc - wrapped in an incredibly RUDE response is a valid question - what are you trying to do? Why would you have a stored procedure call itself like your code indicates?Lou - you might want to check BOL more closely - a view is not a join. You can have views which have nothing to do with joins. This is the first time that I've heard that non-indexed views don't do anything.p.s. I agree that cursors should be avoided whenever possible.SQL is useful if you don't know cursors :-) |
|
|
|
Lou
Yak Posting Veteran
59 Posts |
Posted - 2002-03-12 : 15:23:56
|
| I didn't realize my comment was rude, I apologize. A cursor calling itself looks like an infinite loop waiting to happen to me. As I understand it, a view is basically a select statement. When you reference it, it is built on the fly. If your view is not doing a JOIN, it is basically doing a select * from ... where..., except you're adding overhead on top of the select statement. When you reference a nonindexed view, I doubt if the optimizer will be able to pick a useful index. If you're trying to hide data from users, then I can see a view being useful. In web apps, I use front-end to filter the data for users. If you indexed your view, the same criteria applies to the view, as a table, whether the optimizer will use it. On top of that, the set options must be same as the user who indexed the view--or it can't use the cached results. In general, I find views to add another layer of complexity without much benefit. If you can get views, and in particular indexed views to work, then more power to you. I haven't had the same luck.Edited by - lou on 03/12/2002 15:36:36 |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-03-12 : 17:01:26
|
| lou,Why are you confusing indexed views with a normal view?When a normal view is executed, SQL Server parses the underlying statements, generates a execution plan and then executes.It uses the underlying indexes when it can.How is that any different or slower than a normal SQL statement?DavidMTomorrow is the same day as Today was the day before. |
|
|
|
Lou
Yak Posting Veteran
59 Posts |
Posted - 2002-03-12 : 18:51:48
|
| You're right, normal views are different from indexed views.Edited by - lou on 03/12/2002 19:04:12 |
|
|
|
|
Help on Stored procedure