Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Isolation Level Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 7:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
The only issue I have in my script is how to handle transaction in this script.....

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,
@max int,
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int

SET @vouchertype = (SELECT VoucherType FROM GV_VoucherType where VoucherTypeID = @vouchertypeID)
SET @vouchertypenew = RTRIM(LTRIM(@vouchertype));
SET @retval = LEFT(@vouchertypenew,1);
SET @voucherstatus = (SELECT gvs.VoucherStatusId FROM GV_VoucherStatus gvs WHERE vs.VoucherStatus = 'New')

WHILE CHARINDEX(' ',@vouchertypenew,1)>0
BEGIN
SET @vouchertypenew = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype) - CHARINDEX(' ',@vouchertype,1)));
SET @retval += LEFT(@vouchertypenew,1);
END

WHILE @count <= @quantity
BEGIN
SET @max = (select ISNULL(vouchervalue,0) from gv_vouchervalue WHERE voucherabbreviation = 'V'+@retval )
SET @transactionID = (select ISNULL(vouchervalue,0) from gv_vouchervalue where voucherabbreviation = 'TRNID')

If EXISTS (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
BEGIN

INSERT INTO GV_Voucher
VALUES
(
@vouchertypeID,
'V'+@retval + Replace( Str( @max+1, 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
)
SET @count = @count + 1

Update gv_vouchervalue
SET vouchervalue = @max + 1
WHERE voucherabbreviation = 'V'+@retval
END

END

UPDATE gv_vouchervalue
SET VoucherValue = @transactionID + 1
WHERE VoucherAbbreviation = 'TRNID'

END



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1450126
Posted Tuesday, May 7, 2013 7:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 7,120, Visits: 13,491
Yes you're right:

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
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int

SELECT @vouchertype = VoucherType FROM GV_VoucherType WHERE VoucherTypeID = @vouchertypeID
IF @vouchertype IS NULL
RETURN -1

SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New'
SELECT @transactionID = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'TRNID'

SET @vouchertypenew = RTRIM(LTRIM(@vouchertype));
SET @retval = LEFT(@vouchertypenew,1);
WHILE CHARINDEX(' ',@vouchertypenew,1)>0
BEGIN
SET @vouchertypenew = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype) - CHARINDEX(' ',@vouchertype,1)));
SET @retval += LEFT(@vouchertypenew,1);
END

SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'V'+@retval



WHILE @count <= @quantity
BEGIN

--If EXISTS (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
--BEGIN

INSERT INTO GV_Voucher
VALUES
(
@vouchertypeID,
'V'+@retval + Replace( Str( @VoucherNumber + @count, 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
)
SET @count = @count + 1

--END

END

UPDATE gv_vouchervalue
SET vouchervalue = @VoucherNumber + @count
WHERE voucherabbreviation = 'V'+@retval

UPDATE gv_vouchervalue
SET VoucherValue = @transactionID + 1
WHERE VoucherAbbreviation = 'TRNID'

--END

RETURN 0

Can you post some values of VoucherType FROM GV_VoucherType?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1450129
Posted Tuesday, May 7, 2013 8:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
ChrisM@Work (5/7/2013)
Yes you're right:

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
@vouchertypenew varchar(20),
@retval varchar(20),
@voucherstatus int

SELECT @vouchertype = VoucherType FROM GV_VoucherType WHERE VoucherTypeID = @vouchertypeID
IF @vouchertype IS NULL
RETURN -1

SELECT @voucherstatus = VoucherStatusId FROM GV_VoucherStatus WHERE VoucherStatus = 'New'
SELECT @transactionID = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'TRNID'

SET @vouchertypenew = RTRIM(LTRIM(@vouchertype));
SET @retval = LEFT(@vouchertypenew,1);
WHILE CHARINDEX(' ',@vouchertypenew,1)>0
BEGIN
SET @vouchertypenew = LTRIM(RIGHT(@vouchertype,LEN(@vouchertype) - CHARINDEX(' ',@vouchertype,1)));
SET @retval += LEFT(@vouchertypenew,1);
END

SELECT @VoucherNumber = ISNULL(vouchervalue,0) FROM gv_vouchervalue WHERE voucherabbreviation = 'V'+@retval



WHILE @count <= @quantity
BEGIN

--If EXISTS (SELECT VoucherType FROM GV_VoucherType WHERE VoucherType = @vouchertype)
--BEGIN

INSERT INTO GV_Voucher
VALUES
(
@vouchertypeID,
'V'+@retval + Replace( Str( @VoucherNumber + @count, 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
)
SET @count = @count + 1

--END

END

UPDATE gv_vouchervalue
SET vouchervalue = @VoucherNumber + @count
WHERE voucherabbreviation = 'V'+@retval

UPDATE gv_vouchervalue
SET VoucherValue = @transactionID + 1
WHERE VoucherAbbreviation = 'TRNID'

--END

RETURN 0

Can you post some values of VoucherType FROM GV_VoucherType?

VoucherTypeID Name
1 ValueBased
2 value Discount
3 PercentageDiscount



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1450172
Posted Tuesday, May 7, 2013 8:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 7,120, Visits: 13,491
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
@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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1450201
Posted Tuesday, May 7, 2013 11:47 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
ChrisM@Work (5/7/2013)
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
@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


Thanks a lot Chris.. but I have some queries..
If I once run this script then it will generate rows but if again I run this script then it trow an error:
Violation of UNIQUE KEY constraint 'UN_GVVoucher_VoucherNo'. Cannot insert duplicate key in object 'dbo.GV_Voucher'. The duplicate key value is (VVB0000001).

Also, I am not clear what this will do please explain.. I also want to learn error handling and transaction for this script:
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)



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1450407
Posted Wednesday, May 8, 2013 12:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 7,120, Visits: 13,491
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
@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?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1450419
Posted Wednesday, May 8, 2013 2:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
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
@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?

These values were returned:
VoucherId VoucherTypeId VoucherNo Denomination ExpiryDate CreatedDate ModifyDate VoucherStatusId TransactionID Quantity AmountValue CreatedBy ModifiedBy Validatedays IsDeleted
54 1 VVB0000001 100 2013-11-08 14:07:27.257 2013-05-08 2013-05-08 14:07:27.257 1 TRN0000032 10 0 1 1 40 0
55 1 VVB0000002 100 2013-11-08 14:07:27.257 2013-05-08 2013-05-08 14:07:27.257 1 TRN0000032 10 0 1 1 40 0
56 1 VVB0000003 100 2013-11-08 14:07:27.257 2013-05-08 2013-05-08 14:07:27.257 1 TRN0000032 10 0 1 1 40 0
57 1 VVB0000004 100 2013-11-08 14:07:27.257 2013-05-08 2013-05-08 14:07:27.257 1 TRN0000032 10 0 1 1 40 0
58 1 VVB0000005 100 2013-11-08 14:07:27.257 2013-05-08 2013-05-08 14:07:27.257 1 TRN0000032 10 0 1 1 40 0
59 1 VVB0000006 100 2013-11-08 14:07:27.257 2013-05-08 2013-05-08 14:07:27.257 1 TRN0000032 10 0 1 1 40 0
60 1 VVB0000007 100 2013-11-08 14:07:27.257 2013-05-08 2013-05-08 14:07:27.257 1 TRN0000032 10 0 1 1 40 0
61 1 VVB0000008 100 2013-11-08 14:07:27.257 2013-05-08 2013-05-08 14:07:27.257 1 TRN0000032 10 0 1 1 40 0
62 1 VVB0000009 100 2013-11-08 14:07:27.257 2013-05-08 2013-05-08 14:07:27.257 1 TRN0000032 10 0 1 1 40 0
63 1 VVB0000010 100 2013-11-08 14:07:27.257 2013-05-08 2013-05-08 14:07:27.257 1 TRN0000032 10 0 1 1 40 0



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1450449
Posted Wednesday, May 8, 2013 2:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
Also, I am not clear what this will do please explain.. I also want to learn error handling and transaction for this script:
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)



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1450456
Posted Wednesday, May 8, 2013 2:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 7,120, Visits: 13,491
The last SELECT in that script is

SELECT VoucherNumber = @VoucherNumber, retval = @retval

What values are returned?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1450457
Posted Wednesday, May 8, 2013 3:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
ChrisM@Work (5/8/2013)
The last SELECT in that script is

SELECT VoucherNumber = @VoucherNumber, retval = @retval

What values are returned?


VoucherNumber retval
0 VB



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1450459
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse