ChrisM@Work (5/8/2013)

---

Debugging a script like this is quite tricky without any data to test against. Try running this:

--ALTER Procedure [dbo].[BS_Voucher_CreateVoucher]

DECLARE

@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;

SELECT VoucherNumber = @VoucherNumber, retval = @retval

What values are returned?