Use instances of cursors (SQL Server version)

Source: Internet
Author: User
Tags string back

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.