SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UDF to create a counter


UDF to create a counter

Author
Message
downes
downes
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 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
Mayank Khatri
Mayank Khatri
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2934 Visits: 1760
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.?
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32703 Visits: 16635
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
downes
downes
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 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?
GSquared
GSquared
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54307 Visits: 9730
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
downes
downes
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 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
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32703 Visits: 16635
Debra

Have you tried NEWID()?

John
downes
downes
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 100
Has to be numeric....
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32703 Visits: 16635
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
downes
downes
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search