UDF to create a counter

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » UDF to create a counter

18 posts, Page 1 of 2

1 2 »»

UDF to create a counter

Rate Topic

Display Mode

Topic Options

Author

Message

downes

Posted Friday, January 25, 2008 6:50 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 28, 2010 10:56 AM
Points: 40, Visits: 100

I need a row counter.
I have a view that displays the following:

Name
Account
Zip

I need a UDF that will incremented number and return it to the view:

Name Account Zip counter
jone 97000 33805 1
smith 85000 33501 2
etc.. 3,4,5,...... Cool

Below is my attempt - wrong!!
alter PROCEDURE sp_GetCounter(@ccnt int) as
declare @counter int
set @counter = @counter+@ccnt
return @ccnt

Post #447477

Mayank Khatri

Posted Friday, January 25, 2008 7:01 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 1,776, Visits: 1,438

Below is my attempt - wrong!!
alter PROCEDURE sp_GetCounter(@ccnt int) as
declare @counter int
set @counter = @counter+@ccnt
return @ccnt
-------------------------------------------
Just a thought, why r u returning @ccnt when ur incrementing @counter in ur Proc.?

Post #447485

John Mitchell-245523

Posted Friday, January 25, 2008 7:01 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 4,418, Visits: 7,156

Debra

I think the simplest way to achieve what you want is with an identity column. If this is a new table, add the identity column before you insert any data. If you already have data in your table, create a new table with an identity column, and insert the data from your existing table into it.

John

Post #447484

downes

Posted Friday, January 25, 2008 7:07 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 28, 2010 10:56 AM
Points: 40, Visits: 100

John, thanks for the quick response.

I do not need to update a table on display data via a view.
More info:
The view will be used by a IVR.
The user will enter a date the IVR program will query the view
with user name and date.

The view returns, for example, 10 items with a matching date.
The IVR program needs a counter to loop through the rows.

Does that help?

Post #447487

GSquared

Posted Friday, January 25, 2008 7:28 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

There isn't an exact solution that I know of. Since a view can't pass parameters to a table-value function, you might have trouble doing this.

If you can use a table-value function instead of a view, here's a possible solution:

create function udf_NameAccountZip ()
returns @NAZ table (
Name varchar(25),
Account int,
Zip char(10),
RowNumber int identity primary key)
as
begin
 insert into @naz(name, account, zip)
 select name, account, zip
 from (... table(s) here...)
 where ...
 order by ...
return
end

You'll need to modify it to take input parameters.

If that won't work (if you can't change the front-end code from accessing a view to accessing a udf, for example), I can't think of a solution for SQL 2000.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #447494

downes

Posted Friday, January 25, 2008 8:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 28, 2010 10:56 AM
Points: 40, Visits: 100

I tried something like this....
cast(rand()*DATEPART(ms,getdate())*300 as int)

.. it creates a number however the numbers are identical.
I need something that creates a unique number. It does not have to be sequencial just unique per row. Cool

Post #447537

John Mitchell-245523

Posted Friday, January 25, 2008 8:34 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 4,418, Visits: 7,156

Debra

Have you tried NEWID()?

John

Post #447559

downes

Posted Friday, January 25, 2008 8:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 28, 2010 10:56 AM
Points: 40, Visits: 100

Has to be numeric....

Post #447566

John Mitchell-245523

Posted Friday, January 25, 2008 9:18 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 4,418, Visits: 7,156

Debra

If you don't have more than one update in any particualr second, you could do something like this:

select datediff(s,'01 Jan 1980',getdate())

If your updates are more frequent than that, you could try multiplying by 1000, adding the milliseconds, and casting as bigint.

Good luck
John

Post #447604

downes

Posted Friday, January 25, 2008 9:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 28, 2010 10:56 AM
Points: 40, Visits: 100

Got it!!!

1. create a view
CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

2. create a udf
CREATE FUNCTION RandNumber()
RETURNS float
AS
BEGIN
RETURN 7 + (SELECT RandNumber FROM vRandNumber) * 113.00
END
3. add it to view
SELECT name, date, replace(dbo.RandNumber(),'.','') 'cnt',.....

Got this from http://weblogs.sqlteam.com/jeffs/jeffs/archive/2004/11/22/2927.aspx

Post #447607

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions