Sql trigger assign value to parameter

Transact-SQL
1

I am trying to pass values to parameters:
@code1
@code 2
to find value of the parameters I would use Select statement such
SELECT StockCode
FROM dbo.LotTran
WHERE (Job = 'F0001727') AND (TrnType = 'I') AND (StockCode LIKE '5%')

the SELECT STATEMENT WILL always give me a list of 2 stock codes;
image

I need to assign @code1 to first listed stock code and @code2 to second listed stock code

I would appreciate help with this issue.

2

You can use ROW_NUMBER to tackle this challenge. As I don't have your database and you didn't provide a specific example I've created the following example:

DECLARE @Code1 INT; 
DECLARE @Code2 INT;

DROP TABLE IF EXISTS #tblStock;

SELECT 500008 AS StockCode
INTO #tblStock
UNION
SELECT 500019 AS StockCode;

;WITH Stock AS
(
SELECT 
	StockCode,
	ROW_NUMBER() OVER (ORDER BY StockCode) AS RowNumber
FROM
 #tblStock
)
SELECT @Code1=CASE WHEN RoWNumber=1 THEN StockCode ELSE  @Code1 END,  @Code2=CASE WHEN RoWNumber=2 THEN StockCode ELSE @Code2 END
FROM Stock

SELECT 'Code1: ', @Code1;
SELECT 'Code2: ', @Code2;
3 
declare @Code1 int, @code2 int
declare @T as table(StockCode  int)
--
insert into @T (StockCode) values (500008), (500019)
--
set @Code1 = (select top 1 StockCode from @T order by StockCode)
set @Code2 = (select top 1 StockCode from @T order by StockCode desc)
--
select @Code1, @code2
4

My stock code values will change based on the query.
FROM dbo.LotTran
WHERE (Job = 'F0001727') AND (TrnType = 'I') AND (StockCode LIKE '5%')
when the job number changes the stock codes will differ. There will be 2 stock codes always.

5

Sure, obviously the codes will change, but if there are exactly 2 codes then the answer you were given is correct.
It's not clear what you mean.

6

If you give use an example so we can use your data we can help you in detail.

; WITH Stock AS 
(
SELECT StockCode,
	ROW_NUMBER() OVER (ORDER BY StockCode) AS RowNumber
FROM dbo.LotTran
WHERE (Job = 'F0001727') AND (TrnType = 'I') AND (StockCode LIKE '5%')
)
SELECT
@Code1=CASE WHEN RoWNumber=1 THEN StockCode ELSE  @Code1 END,  @Code2=CASE WHEN RoWNumber=2 THEN StockCode ELSE @Code2 END
FROM Stock;