A slight mod to Sean's code to return the single value to a variable:

CREATE PROCEDURE dbo.GetSTNValue

(

@pFromStore INT

,@pToStore INT

,@pSTN VARCHAR(10) OUTPUT

) AS

BEGIN

DECLARE @count INT = 0

,@AWBNo VARCHAR(10) = 0

SET @AWBNo = (

SELECT ISNULL(gs.AWBNo, 0)

FROM GV_STNDetails gs

WHERE gs.From_StoreCode = @pFromStore

AND gs.TO_StoreCode = @pToStore

)

SET @count = (

SELECT gsv.CountValue

FROM GV_STNCountValues gsv

WHERE gsv.StoreCode = @pFromStore

)

IF @AWBNo = 0

BEGIN

IF NOT EXISTS (

SELECT ISNULL(gs.STNNO, 0)

FROM GV_STNDetails gs

WHERE gs.From_StoreCode = @pFromStore

AND gs.TO_StoreCode = @pToStore

)

BEGIN

SET @pSTN = CONVERT(VARCHAR, REPLACE(STR(@pFromStore, 4), ' ', 0)) + CONVERT(VARCHAR, REPLACE(STR(@count + 1, 5), ' ', '0'))

UPDATE GV_STNCountValues

SET CountValue = CountValue + 1

WHERE StoreCode = @pFromStore

END

ELSE

BEGIN

SET @pSTN = (

SELECT gs.STNNO

FROM GV_STNDetails gs

WHERE gs.From_StoreCode = @pFromStore

AND gs.TO_StoreCode = @pToStore

)

END

END

ELSE

BEGIN

DECLARE @oldSTN VARCHAR(10)

SET @oldSTN = (

SELECT gs.STNNO

FROM GV_STNDetails gs

WHERE gs.From_StoreCode = @pFromStore

AND gs.TO_StoreCode = @pToStore

)

SET @pSTN = CONVERT(VARCHAR, REPLACE(STR(@pFromStore, 4), ' ', 0)) + CONVERT(VARCHAR, RIGHT(@pSTN, 6) + 1)

END

END

GO

/*

How called:

declare @newSTN varchar(10), @FromStore int, @ToStore int;

set @FromStore = 1;

set @ToStore = 2;

exec dbo.GetSTNValue @pFromStore = @FromStore, @pToStore = @ToStore, @pSTN = @newSTN output;

select @newSTN -- verify that the value was returned

*/