Cursors with Static Views - SQL Server

Question

Well i want to begin saying that i have spend a couple of hours trying to understand how to add a static view to this cursor but i really dont understand the real functionality of the cursors, so i came here to beg for some advices. This is a similar to what im using, so i hope is clear enough.

DECLARE @name VARCHAR(50),
@last_name VARCHAR(50)

DECLARE C_People CURSOR GLOBAL
FOR 
SELECT name, last_name
FROM People

OPEN C_People 
    FETCH C_People INTO @name , @last_name
    WHILE(@@FETCH_STATUS = 0)
        BEGIN
            PRINT 'Hello ' + @name + ' ' + @last_name
            FETCH C_People INTO @name, @last_name
        END
CLOSE C_People 
DEALLOCATE C_People 
GO

What do you mean by "add a static view to this cursor"? Could you explain the results you are trying to create? — David Browne - Microsoft, May 01 '20 at 18:51
i want to create a static view which is ideal for scrolling through cursors — , May 01 '20 at 18:58
Cursors are almost never necessary to solve a problem in T-SQL. They are certainly almost never the most efficient solution, as opposed to a set-based solution. For example, `SELECT 'Hello ' + name + ' ' + last_name FROM People` produces the same output, albeit as a result set and not as printed lines. — Jeroen Mostert, May 01 '20 at 19:02
As to your actual question, it's still unclear what you want. [Views](https://learn.microsoft.com/sql/t-sql/statements/create-view-transact-sql) have nothing to do with cursors. Cursors can be [declared `STATIC`](https://learn.microsoft.com/sql/t-sql/language-elements/declare-cursor-transact-sql) but that in turn has nothing to do with views. A better alternative to static cursors (if that is what you're after) are either intermediate results explicitly selected `INTO` a temp table, or a query with snapshot isolation. — Jeroen Mostert, May 01 '20 at 19:02
I know that is no even need it, but as an exercises i really need to put em together — , May 01 '20 at 19:05
A cursor is better for scans, when you want to do something, for example to run a stored procedure, based on a value. So you can select into a cursor your needed information, across multiple tables, and in your scan you can run a stored procedure which for example calculate stocks of products... Something like this. In other conditions, you can use select into temp tables, or into variable tables, depending on what is the purpose of selected data — Vali Maties, May 01 '20 at 19:07
Exercise or no, you need to be clear about your terms and what you're after. SQL has no concept of a "static view", or at least none that's in the docs that I know of. Cursors have many options that could get whatever it is you mean with "static view", but it's not clear without context. If you could explain in your question what, in your world, a static view *is* and what "adding" it to a cursor means, that would help. — Jeroen Mostert, May 01 '20 at 19:29
My bad, i mean normal view. With adding i mean to implement both, a view that can use the cursor — , May 01 '20 at 19:42
You can't use cursors inside views. You can use them in stored procedures or (multi-line) table-valued functions. The latter comes closest to resembling a view in terms of use. Be aware that presenting cursor-based processing this way is almost certainly the worst solution to whatever problem you have, as you invite loops-within-loops that the engine cannot optimize (unlike a proper set-based solution). The path to a long and fruitful career in SQL programming is to first forget cursors exist, then only remember that they do if there is no other solution to a problem. — Jeroen Mostert, May 01 '20 at 19:55
Well i hate myself so i decide to use cursors, i know that in cases like this is useless but i want to learn how to use properly and as an extra to use it with a view, to see performance and another stuffs — , May 01 '20 at 19:57
Views are comprised of a single `SELECT` statement and nothing else. Nothing involving loops or cursors can be offered up as a view. What you're literally asking for is not possible. You can, of course, have a cursor `SELECT` from a view, just as you can select from any regular table, but that requires no special consideration. You can also make the `SELECT` as interesting as you like, including doing the processing in the query (`DECLARE C CURSOR FOR SELECT 'Hello ' + first_name FROM People`), so you don't necessarily need to create a view first. — Jeroen Mostert, May 01 '20 at 20:01

score 0 · Answer 1 · answered May 01 '20 at 19:03

Supposing you want a view which must return a column with that "Hello"...

CREATE VIEW [dbo].[View_HelloPersons]
AS
SELECT       'Hello ' + Name + ' ' + Last_Name AS Something
FROM            dbo.People

-- you can access data from anywhere with
Select Something From View_HelloPersons

score 0 · Answer 2 · answered May 01 '20 at 19:19

0

You may be referring to a forward-only cursor - fast_forward is generally the fastest:

DECLARE name CURSOR FAST_FORWARD FOR

answered May 01 '20 at 19:19

Russell Fox

5,273
1
24
28

Cursors with Static Views - SQL Server

2 Answers2