Primary Key Generation

Question

Primary Key Generation

mike 57299

SSCrazy

Points: 2529
More actions
June 9, 2010 at 11:14 pm

#233625

The following code:
create FUNCTION [dbo].[ufn_GenPK]
( @table varchar(100))
RETURNS int
BEGIN
DECLARE @ReturnValueint = -1
DECLARE @junkchar(128)
SELECT @junk= gpk_pk from generatepk WHERE gpk_pk = @table
IF @@ROWCOUNT = 1
begin
update generatepk set gpk_currentnumber = gpk_currentnumber + 1 where gpk_pk = @table
select @returnvalue = generatepk where gpk_pk = @table
end
RETURN @ReturnValue
END
GO
Gets the following error:
Msg 443, Level 16, State 15, Procedure ufn_GenPK, Line 13
Invalid use of a side-effecting operator 'UPDATE' within a function.
How can I do this? I am trying to get the next pk value for a table from the correct record?
Thanks,
Mike

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

It's not going to happen using a function because a function isn't allowed to update or insert into "real" tables... period.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mike 57299 SSCrazy Points: 2529 More actions · Answer 2

Jeff,

What would you recommend to make sure I don't get duplicate primary keys? Should I do it as a standard proc?

Mike

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What's wrong with an identity column?

It'll save you a lot of grief in the long run. The manually updated key in a sequence table can be done, but there are far more ways to do it wrong (causing occasional duplicate values and/or major performance problems) than there are ways to do it right.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mike 57299 SSCrazy Points: 2529 More actions · Answer 4

legacy code issues. Need the primary key for child record(s).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Identity column with either scope_identity or the output clause?