SQL Server cursor, stored procedure, and data block execution

The data block cursor example is as follows:

Begindeclare @ item_code varchar (32) -- defines the variable declare @ item_name varchar (32) Declare @ invest_money_sum float -- defines the cursor declare my_cursor cursor forselect item_code, item_name, invest_money_sum from zftz_project_sheji_result -- open the cursor open my_cursorfetch next from my_cursor into @ item_code, @ item_name, @ invest_money_sumwhile (@ fetch_status = 0) beginupdate cursor set mainamount = @ invest_money_sum where code = @ item_code fetch next from my_cursor into @ item_code, @ item_name, @ invest_money_sumend -- close the cursor close your my_cursor -- unmount the cursor end

The stored procedure instance is as follows:

Drop procedure test_test; gocreate procedure test_test @ parm1 varchar (20), @ parm2 varchar (20) asbegin transaction declare @ item_code varchar (32) -- Define the variable declare @ item_name varchar (32) declare @ invest_money_sum float -- defines the cursor declare my_cursor cursor forselect item_code, item_name, invest_money_sum from cursor -- open the cursor open my_cursorfetch next from my_cursor into @ item_code, @ item, @ invest_money_sumwhile (@ fetch_status = 0) beginupdate into set mainamount = @ invest_money_sum where code = @ item_code fetch next from my_cursor into @ item_code, @ item_name, @ invest_money_sumendset @ parm2 = @ parm1 + ', '+ @ parm2print @ parm2 -- close the cursor close my_cursordeallocate my_cursor -- unmount the cursor -- you can add return values and add transaction control if (@ error> 0) beginrollback transelect 0 returnend elsebegincommit transelect 1returnend -- endexec test_test 'AB', 'cd' -- execute the Stored Procedure

Query all data tables in the sqlserver database:

Select name from sysobjects where xtype = 'U' ---- query all data tables in the database