SQL Server is set-based, so you don't pass things one-by-one to a function!
You probably need something like (untested)
UPDATE LoadData
SET Sequence_Code = dbo.GetNextSequenceCode_TEST([ID])
WHERE [Status] = 'A' AND Sequence_Code IS NULL;
You also state in your function
Quote:
--Will generate And Update a Sequence Code in the same table
No it won't.
If you check the
documentation [
^] you will see
Quote:
UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.
Just make sure that you can work out what the next sequence number is meant to be and do the update the way I have shown above
UPDATE:
You might be tempted to something like this in your UDF
CREATE FUNCTION [GetNextSequenceCode_TEST] (@parm int)
RETURNS varchar(100)
AS
BEGIN
declare @lastseqcode varchar(100)
SELECT TOP 1 @lastseqcode = Sequence_Code FROM LoadData
WHERE ID < @parm AND Sequence_Code is not null
order by ID DESC
declare @intPart char(2) = SUBSTRING(@lastseqcode, 1, 2)
declare @charPart char(2) = SUBSTRING(@lastseqcode, 3, 2)
set @intPart = CAST(CAST(@intPart as int) + 1 AS varchar(2))
RETURN @intPart + @charPart
END
If you do, then you will discover that all of the Sequence_Code values get set to the same value (20AC in this example). That is because the UPDATE is a single transaction, so the "previous" update isn't "visible" to the function until the entire transaction has completed on the set of data.
It's a very bad idea to do it that way for another reason - what if another user is trying to run the same function against the same table - what will the "most recent" value returned to each one? You will have essentially rendered the database single-user only, in which case why bother using SQL Server?
The body of your function should calculate the "next" sequence based on the parameter passed. It's ok to have a @LastSeqAlphabet passed in - but how are you determining what the "last" sequence was ... the comments above apply, except the impact is even worse if you query for that outside the function (more time for someone else to get in there!)