January 6, 2010 at 9:11 am
CREATE FUNCTION GetTextId
(@intLookupTextId int)
RETURNS int AS
BEGIN
SELECT @intLookupTextId = max(Id) from schema.tablename
SET @intLookupTextId = @intLookupTextId + 1
RETURN @intLookupTextId
end
I have to insert data into the table from scratch. Intially i am setting the inttextlookupid = 1 in my insert stmts.
if this udf is correct, how would i call this during my inserts so that the id gets incremented by 1
January 6, 2010 at 1:22 pm
Firstly, you don't need the parameter because this can be defined as a local variable:
CREATE FUNCTION GetTextId ()
RETURNS int AS
BEGIN
Declare @intLookupTextId int
SELECT @intLookupTextId = max(Id) from schema.tablename
SET @intLookupTextId = @intLookupTextId + 1
RETURN @intLookupTextId
end
To use this in an Insert statement, simply do:
Insert Into schema.tablename (ID) Values ([dbo].[GetTextID]())
January 6, 2010 at 2:15 pm
Instead of trying to roll your own - and ending up with all sorts of problems - why don't you just use IDENTITY instead?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2010 at 2:27 pm
reason not using identity is because the id's can be repeated but will be differentiated by languagetype.
January 6, 2010 at 2:33 pm
Another quick note. To make sure that the function works, even if there are no rows in the table yet, change the Select statement to:
SELECT @intLookupTextId = IsNull(max(Id),0) from schema.tablename
This way, @intLookupTextId always has a value return.
Nigel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply