Single Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Single Function

Author

Topic

mfazio
Starting Member

17 Posts

Posted - 2006-12-09 : 23:41:49

Im looking to implement in a single function which will iterate an integer field by one, and return its final value. Can this be done on a single line?

Such that it is in the following form

return(field = field+1)

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-10 : 01:37:40

Why don't you just use the built-in IDENTITY option of a column rather than maintaining it yourself? And why does it need to be a single line of code?

Tara Kizer

mfazio
Starting Member

17 Posts

Posted - 2006-12-10 : 02:07:41

Its not for primary key purpose. Im trying to implement a hit counter for multiple tracker objects.

I.E

Trackers

ID [PK]
Hits

Each time the stored procedure is called on a specific tracker by it's ID, i would like the hits value to increase by one and then for the value to be returned.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-10 : 03:09:17

IDENTITYs don't have to be used for primary keys.

What will the stored procedure do? Is it for INSERTs, UPDATEs, or just SELECTs?

Tara Kizer

mfazio
Starting Member

17 Posts

Posted - 2006-12-10 : 03:31:59

UPDATE & SELECT i guess...

1) Increment the value of the "hits" field by one
2) Return the updated value

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-10 : 13:34:02

You can't do it in one statement. You first must put the current value in a variable:

SELECT @var1 = Hits
FROM yourTable
WHERE ID = @ID

Then update it:

UPDATE yourTable
SET Hits = @var1 + 1

Then you can return @var1 + 1 as that's the new value.

Tara Kizer

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-10 : 16:56:49

Not true... it can be done in a single statement...

UPDATE @Hits = Hits = Hits+1
FROM yourtable

No transaction to get in the way, either.

--Jeff Moden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-10 : 18:05:37

I always forget about that way. I've never used it in my code, so I never can remember the syntax.

Tara Kizer

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-10 : 18:37:01

keep in mind however that this does not guarantee any kind of order not even if you have a clustered index.
so doing this on production is a bit risky.

There was an article or thread on this somewhere but i can't find it... anyone remembers the location?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-10 : 18:38:37

also take a look here
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 1

you can also see how to deal with groups of data.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

mfazio
Starting Member

17 Posts

Posted - 2006-12-10 : 23:33:27

thanks tkizer, spirit1, and jeff :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources