Cursors Plus scalar functions

One, today, encountered a situation, need to loop out the value in the stored procedure, and for the next statement, the contact with the downstream subscript: the cursor provides a flexible way to manipulate the data retrieved from the table, in essence, A cursor is actually a mechanism that extracts one record at a time from a result set that includes multiple data records. Cursors are always associated with a selection statement because the cursor consists of a result set (which can be 0, one, or multiple records retrieved by a related selection statement) and a cursor position in the result set that points to a particular record.

The use of cursors is divided into four basic steps: Declaring a cursor, opening a cursor, extracting data, and closing a cursor.

DECLARE curse_prcitemid Cursor for--declares the cursor (CUROSR as the keyword)            SELECT DISTINCT ItemId from   dbo. Channelsupplydetails WHERE  sp = @Bracode and ItemId <> @ItemID            Open Curse_prcitemid--open cursor             while @@ Fetch_status = 0-Returns the state of the  last cursor executed by the FETCH statement, rather than the state of any cursors that are currently open for the connection.   begin   FETCH NEXT from Curse_prcitemid to @temp-extract data  SELECT @value1 = Isnull (Sum (Employid), 0) from   dbo. Inputstore WHERE  itemid = @temp and Employid <> 0   if @value1 > @Count  beginselect  @[email Protected]close curse_prcitemid--close cursor break   end  Else  beginselect @LastId =0  End   End

Two. Scalar-valued functions

A scalar function returns a scalar value that determines the type. Its return value type is a data type other than text, NTEXT, IMAGE, CURSOR, TIMESTAMP, and table type.

Only [dbo] is required for use. [DAYKC] (parameters) just fine.

Create  FUNCTION [dbo].[ DAYKC]--[dbo] This is not rare otherwise it will not be recognized (@ItemId  varchar) RETURNS int  --Define return type begin    declare @Count int    Select @Count =isnull (sum (STO), 0) from  Channel as a,chansup as B where a.id=b.id     return @CountEND
GO

　　

Cursors Plus scalar functions