Dynamic counter 1

[bakershawnm]

Good morning all,

I am using SS2005 and I would like to do the equivalent of the MS ACCESS solution below from this KB article

http://support.microsoft.com/kb/94397.

Option Compare Database
Option Explicit
Global cntr

'*************************************************************
' Function: Qcntr()
'
' Purpose: This function will increment and return a dynamic
' counter. This function should be called from a query.
'*************************************************************

Function QCntr(x) As Long
cntr = cntr + 1
QCntr = cntr
End Function

'**************************************************************
' Function: SetToZero()
'
' Purpose: This function will reset the global Cntr to 0. This
' function should be called each time before running a query
' containing the Qcntr() function.
'**************************************************************

Function SetToZero()
cntr = 0
End Function

I tried setting up a table for the global pointer but TSql will not let me update a table from within a (scalar-valued) function. I have not tried any of the other function types because I do not know anything about them yet and was looking for an quicker solution.

Basically what I am trying to do is compile a set of records in a query or view and have it number the records always starting with 1 so that I can put that number into a field in the record. Then I insert the records into another table. The above function set works great for ACCESS but I don't want to use ACCESS for this particular solution.

Any help or suggestions will be welcome.

Thanks

 

[simian336]

I think what you want is to create a temporary table from a query then use a cursor (or query) to update the table.

The above function just returns an incrmented number which a cursor does easily...

(Standard warning that cursors are not optimal)

Simi

 

[bborissov]

Check ROW_NUMBER() in BOL

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[bakershawnm]

The ROW_NUMBER() is perfect.

Thanks very much and have a star.