Click here to Skip to main content
15,664,506 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
This is my table "LoadData"

Sequence_Code |     ID | Current_Year | Record_Date| Status     |
 :------------ | -----: | -----------: | :----------|          --|
 17AA          | 310001 |         2017 | 2017-01-01 | S
 18AB          | 310002 |         2018 | 2018-02-22 | A
 19AC          | 310003 |         2019 | 2019-02-10 | S
 NULL          | 310004 |         2019 | 2019-02-20 | A
 NULL          | 310005 |         2020 | 2019-03-20 | A
 NULL          | 310006 |         2020 | 2019-04-20 | A

How can we pass one by one 'ID' of only those records where Sequence_Code is NULL and Status is A

My function is "dbo.GetNextSequenceCode_TEST"

CREATE FUNCTION [dbo].[GetNextSequenceCode_TEST] (@LastSeqAlphabet varchar(50))
  RETURNS varchar(100)
	    --Will generate And Update a Sequence Code in the same table

Please Update the Input Parametes of the Function,If Required.

What I have tried:

I could not try,because i have no clue to do so
Updated 6-Jan-21 6:46am

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
--Will generate And Update a Sequence Code in the same table
No it won't.

If you check the documentation [^] you will see
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

You might be tempted to something like this in your UDF
CREATE FUNCTION [GetNextSequenceCode_TEST] (@parm int)
  RETURNS varchar(100)
	-- Get most recent Sequence_Code  
	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

	-- work out what the "next" one should be 
	declare @intPart char(2) = SUBSTRING(@lastseqcode, 1, 2)
	declare @charPart char(2) = SUBSTRING(@lastseqcode, 3, 2)

	-- I can't be bothered to do anything fancy here
	set @intPart = CAST(CAST(@intPart as int) + 1 AS varchar(2))
	RETURN @intPart + @charPart
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!)
Share this answer
Member 15036870 6-Jan-21 11:25am    
@CHill60 I agree that the function will not update, I meant that My function is generating the value,which i am able to update in the table in the same way as you have suggested here.. my concern is for multiple ID's, that how can we pass Multiple ID's having status='A' and Sequence_Code IS NULL, one by one to the function,So it keep on generating the value and update statement will update it.
CHill60 6-Jan-21 12:10pm    
You don't pass them one by one. You call the function for each row that fits the filter - in exactly the way I have shown. The key is in how you generate the "next" sequence based on the ID of the row being processed
Maciej Los 6-Jan-21 12:47pm    
Short And To The Point!
Member 15036870 8-Jan-21 10:07am    
Thanks @CHILL60 for all efforts you put to explaining me..I got the way.
CHill60 8-Jan-21 16:45pm    
Great news!
Try some tutorials and you will get many clues, for example: SQL CASE Statement[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900