check condition

Question

Post reply

check condition

kapil_kk

SSC-Insane

Points: 21316
More actions
April 8, 2013 at 4:05 am

#270679

Hi all, I have to check a condition that if I entered correct first and last voucherno then only it have to perform some operation like insertion.... But I used IN then it will solve my purpose as if I enter @firstvoucherno correct and @lastvoucherno incorrent then also it will insert. Inspite of IN if I use and it will not returnign anything from my table
example:
SELECT * FROM GV_Booklet gv
WHERE gv.BookletID = 'B00001'
AND gv.BookletID ='B00003'
Declare @FirstVoucherID ='B00001',
@lastvoucherID = 'B00004'
Here is part of my script:
IF EXISTS(SELECT * FROM GV_Voucher gv
JOIN GV_VoucherStatus gvs ON gv.VoucherStatusId = gvs.VoucherStatusId
WHERE gv.VoucherNo IN (@FirstVoucherID , @lastvoucherID)
AND gvs.VoucherStatus = 'Dispatched')
BEGIN
RAISERROR('Voucher No already in use',16,1)
END
ELSE
IF EXISTS (SELECT gv.VoucherNo FROM GV_Voucher gv
JOIN GV_VoucherStatus gvs ON gv.VoucherStatusId = gvs.VoucherStatusId
WHERE gv.VoucherNo IN (@FirstVoucherID, @lastvoucherID )
AND gvs.VoucherStatus = 'Active at HO')
BEGIN
INSERT INTO GV_StoreAllocation (StoreId, STNNo, FirstVoucherNo, LastVoucherNo, Quantity)
VALUES
(
@storecode,
@STNNo,
@FirstVoucherID,
@lastvoucherID,
@quantity
)
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Seraj Alam-256815 SSC Eights! Points: 804 More actions · Answer 1

Hi

correct below condition considering points aspects.

1. If you already have (a) a covering index/clustered index on gv.VoucherNo, gvs.VoucherStatus or (b) the table is very small then

use two exist() with and condition and not the in (1,2).

2. If you have large table, consider using count(*) to return >1 rows provided it will return only 2 rows.

IF EXISTS (SELECT gv.VoucherNo FROM GV_Voucher gv

JOIN GV_VoucherStatus gvs ON gv.VoucherStatusId = gvs.VoucherStatusId

WHERE gv.VoucherNo IN (@FirstVoucherID, @lastvoucherID )

AND gvs.VoucherStatus = 'Active at HO')

BEGIN

Seraj Alam

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

kapil_kk (4/8/2013)
Hi all, I have to check a condition that if I entered correct first and last voucherno then only it have to perform some operation like insertion.... But I used IN then it will solve my purpose as if I enter @firstvoucherno correct and @lastvoucherno incorrent then also it will insert. Inspite of IN if I use and it will not returnign anything from my table
example:
SELECT * FROM GV_Booklet gv
WHERE gv.BookletID = 'B00001'
AND gv.BookletID ='B00003'
...

This query won't return any rows, gv.BookletID can't have both values. Do you mean OR?

SELECT *

FROM GV_Booklet gv

WHERE gv.BookletID = 'B00001'

OR gv.BookletID = 'B00003'

^{“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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

ChrisM@Work (4/8/2013)
kapil_kk (4/8/2013)
Hi all, I have to check a condition that if I entered correct first and last voucherno then only it have to perform some operation like insertion.... But I used IN then it will solve my purpose as if I enter @firstvoucherno correct and @lastvoucherno incorrent then also it will insert. Inspite of IN if I use and it will not returnign anything from my table
example:
SELECT * FROM GV_Booklet gv
WHERE gv.BookletID = 'B00001'
AND gv.BookletID ='B00003'
...
This query won't return any rows, gv.BookletID can't have both values. Do you mean OR?
SELECT *
FROM GV_Booklet gv
WHERE gv.BookletID = 'B00001'
OR gv.BookletID = 'B00003'

OR and IN both will return same values in this case....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

Well now I am done with the result that was required 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/