Thanks. Try this. With multiple dml statements it still requires a transaction and error handling:

ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]

@vouchertypeID int = 1,

@denomination int = 100,

@quantity int = 10,

@amountvalue int = 100,

@userid varchar(50) = 1,

@validateDays int = 40

AS

--BEGIN

SET NOCOUNT ON;

DECLARE

--@vouchertype varchar(20),

@transactionID varchar(20),

--@count int =1,

@VoucherNumber int, -- was @max-2

@vouchertypenew varchar(20),

@retval varchar(20),

@voucherstatus int

-- table variables receive OUTPUT

DECLARE @VoucherNumberTable TABLE (vouchervalue INT);

DECLARE @transactionIDTable TABLE (transactionID INT);

--------------------------------------------------------------------------------------

-- Load some variables. Exit immediately if vouchertype is invalid

--------------------------------------------------------------------------------------

SELECT

@vouchertypenew = SUBSTRING(TrimmedVoucherType,1+CHARINDEX(' ',TrimmedVoucherType,1),20),

@retval = LEFT(TrimmedVoucherType,1)

+ ISNULL(SUBSTRING(TrimmedVoucherType,1+NULLIF(CHARINDEX(' ',TrimmedVoucherType,1),0),1),'')

FROM GV_VoucherType v

CROSS APPLY (SELECT TrimmedVoucherType = LTRIM(RTRIM(v.vouchertype))) x

WHERE v.VoucherTypeID = @vouchertypeID;

IF @vouchertypenew IS NULL

RETURN -1

SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New';

--------------------------------------------------------------------------------------

-- Read existing @VoucherNumber, set next @VoucherNumber

--------------------------------------------------------------------------------------

-- save off the current value to @VoucherNumber

-- and update to the next available value

UPDATE gv_vouchervalue

SET vouchervalue = vouchervalue + @quantity

OUTPUT deleted.vouchervalue INTO @VoucherNumberTable

WHERE voucherabbreviation = 'V' + @retval;

SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM @VoucherNumberTable;

--------------------------------------------------------------------------------------

-- Read existing @transactionID, set next @transactionID

--------------------------------------------------------------------------------------

-- save off the current value to @transactionID

-- and update to the next available value

UPDATE gv_vouchervalue

SET VoucherValue = VoucherValue + 1

OUTPUT deleted.vouchervalue INTO @transactionIDTable

WHERE VoucherAbbreviation = 'TRNID'

SELECT @transactionID = ISNULL(vouchervalue,0) FROM @transactionIDTable;

--------------------------------------------------------------------------------------

-- Insert required number of rows into GV_Voucher

--------------------------------------------------------------------------------------

-- Inline tally table generates (@quantity) rows

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),

E2(N) AS (SELECT 1 FROM E1 a, E1 b),

iTally(N) AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E2 a, E2 b)

-- USE A COLUMN LIST!!

-- If the column order is changed or a column is added or removed,

-- an insert without a column list will fail.

-- A column list is free documentation. What does

-- 'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0') go into?

INSERT INTO GV_Voucher

SELECT TOP(@quantity)

@vouchertypeID,

'V'+@retval + Replace( Str( @VoucherNumber + t.n, 7), ' ', '0'),

@denomination,

DATEADD(MM,6,GETDATE()),

GETDATE(),

GETDATE(),

@voucherstatus,

'TRN' + REPLACE( Str( @transactionID + 1, 7), ' ', '0'),

@quantity,

CASE @vouchertypenew

WHEN 'Discount' THEN @amountvalue

ELSE 0

END,

@userid,

@userid,

@validateDays,

0

FROM iTally t;

--UPDATE gv_vouchervalue

--SET vouchervalue = @VoucherNumber + @count

--WHERE voucherabbreviation = 'V'+@retval

--UPDATE gv_vouchervalue

--SET VoucherValue = @transactionID + 1

--WHERE VoucherAbbreviation = 'TRNID'

--END

RETURN 0