April 3, 2013 at 7:03 am
Hi, I have written a script in which I am performing various scenario.. First I will validate if a voucher is already in use or not then if a voucher is not in use then I will insert into a table..after inserting data into table I have to change the status of those voucher in some another table..
Is is correct way to write a script or it will have performance issues later. please suggest
DECLARE @storecode int,
@STNNo varchar(20),
@vochertype int,
@Isbooklet int,
@denomination int,
@quantity int,
@FirstVoucherID varchar(10),
@lastvoucherID varchar(10)
IF EXISTS(SELECT * FROM GV_Voucher gv
JOIN GV_VoucherStatus gvs ON gv.VoucherStatusId = gvs.VoucherStatusId
WHERE gv.VoucherNo = @FirstVoucherID AND gv.VoucherNo = @lastvoucherID
AND gvs.VoucherStatus <> 'NEW')
BEGIN
PRINT 'Voucher No already in use'
END
ELSE
BEGIN
INSERT INTO GV_StoreAllocation (StoreId, VoucherId, STNNo, GRNNo, VoucherStatusId, DispatchDate, ReceivedDate)
VALUES
(
@storecode,
@STNNo,
@FirstVoucherID,
@lastvoucherID,
@quantity
)
END
DECLARE @voucherlist TABLE
(
VoucherNo varchar(10)
)
INSERT INTO @voucherlist
SELECT
gv.VoucherNo
FROM GV_Voucher gv WHERE gv.VoucherNo BETWEEN @FirstVoucherID AND @lastvoucherID
UPDATE GV
SET GV.VoucherStatusId = gvs.VoucherStatusId
FROM GV_Voucher GV
JOIN @voucherlist vc ON vc.VoucherNo = GV.VoucherNo
JOIN GV_VoucherStatus gvs ON gvs.VoucherStatusId = GV.VoucherStatusId
WHERE gvs.VoucherStatus = 'Dispatched'
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 3, 2013 at 7:40 am
Use MERGE with an output clause. Dwain Camps has an excellent article on the front page today which describes a potential pratfall issue with MERGE. It's perfectly adequate to get you on the right track and includes some data samples to code against.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply