SQL Server Stored Procedures and cursors

 

If exists (

Select *

From DBO. sysobjects

Where id = object_id (n' [DBO]. [sp_check_qq] ')

And objectproperty (ID, n' isprocedure ') = 1

)

Drop procedure [DBO]. [sp_check_qq]

Go

 

Create procedure DBO. sp_check_qq

-- Used to check whether all QQ numbers are numbers without Variables

As

-- Mainly refers to exercise cursor

-- Declare the cursor

Declare @ Q varchar (10)

Declare @ ID integer

Declare @ s varchar (8000)

/*

* Define a cursor

*/

Declare cur_ii cursor

Select ii_id, QQ from II

/*

* Open a cursor

*/

 

Open cur_ii

/*

* Extract records

*/

 

Fetch next from cur_ii into @ ID, @ Q

Set @ s =''

/*

* Loop and extract records

*/

While (@ fetch_status = 0)

Begin

If DBO. is_all_number (@ q) = 0

Set @ s = @ s + ',' + Cast (@ ID as varchar (10 ))

Fetch next from cur_ii

Into @ ID, @ Q

End

/*

* Close the cursor

*/

Close cur_ii

/*

* Delete A cursor reference

*/

Deallocate cur_ii

Set @ s = right (@ s, Len (@ s)-1)

Select @ s

/*

Exec sp_check_qq

*/

Go