Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

UDF to create a counter Expand / Collapse
Author
Message
Posted Friday, January 25, 2008 6:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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,......

Below is my attempt - wrong!!
alter PROCEDURE sp_GetCounter(@ccnt int) as
declare @counter int
set @counter = @counter+@ccnt
return @ccnt
Post #447477
Posted Friday, January 25, 2008 7:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:42 AM
Points: 1,800, Visits: 1,555
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
Posted Friday, January 25, 2008 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 5,480, Visits: 10,303
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
Posted Friday, January 25, 2008 7:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Friday, January 25, 2008 7:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 5, 2014 10:31 AM
Points: 13,872, Visits: 9,599
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
Posted Friday, January 25, 2008 8:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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.
Post #447537
Posted Friday, January 25, 2008 8:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 5,480, Visits: 10,303
Debra

Have you tried NEWID()?

John
Post #447559
Posted Friday, January 25, 2008 8:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 28, 2010 10:56 AM
Points: 40, Visits: 100
Has to be numeric....
Post #447566
Posted Friday, January 25, 2008 9:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 5,480, Visits: 10,303
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
Posted Friday, January 25, 2008 9:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase 12»»

Permissions Expand / Collapse