Use instances of cursors (SQL Server version)

Cursor, if it was before to tell me this concept, my brain has two ideas: 1, you cow; 2, I will not

Not not a reason, not an excuse, so we have to learn, I attribute like to see Code, do not like reading people, so, the text is not attractive to me, gossip less ah, to provide you with an example of my writing, first let everyone use up, and know how to use Ah, want to go deep, do not ask me ah, to read the book

cursors, as Java staff, the best understanding is the list in Java, but the database is a little lazy, not too much management to him, to open, close, release resources

Demo1:

Declare cursor  for    -- -DECLARE CURSOR mycur Select  from T_stu  Open Mycur---    opening cursor --action content close mycur    -- -Close Cursors deallocate mycur---   Releasing Resources

The top of the demo is just to tell you some basic instructions of the cursor, in fact, there is no use, in the function, the use of cursors in the stored procedure is really useful class, next, see Demo2:

The main function of this function is to pass in the table name, return all the columns of the table, and spell the string back.

Create function [dbo].[Getcols]( @table_name  as varchar( -) )RETURNS varchar( -) asBEGIN  DECLARE @colname VARCHAR( -)  DECLARE @typename VARCHAR( -)  DECLARE @result varchar( -)    Set @result = "'  DECLAREMycurCURSOR  for     ---Define Cursors    SelectC.name asColumnname,ty.name asTypeName fromsys.columns CInner JoinSys.tables T onT.object_id=C.object_id        Inner JoinSys.types Ty onty.system_type_id=c.system_type_idwhereT.name= @table_name  andTy.name!='sysname'        ---Incoming parameter @table_name        Order  byt.name,c.column_idOPENMycur---Open Cursor    FETCH NEXT  fromMycur into        ----Get the next piece of data    @colname,@typename                ----Save the acquired data to a variable         while @ @FETCH_STATUS = 0         ----Returns the state of the last fetch command, 0: Success-1: Failed-2: The fetched row does not exist        BEGIN            if @result = NULL or @result = "'            begin               SET @result = @colname            End            Else            begin               SET @result = @result + ',' + @colname                End        FETCH NEXT  fromMycur into    ---Get the next piece of data              @colname,@typename         END    CLOSEMycur---Close Cursors        deallocateMycur----Release resources  RETURN @resultEND

Use instances of cursors (SQL Server version)