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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy