Operator BETWEEN in SQL Server

Question

I was writing sub query in stored procedure to get the values between Orders stored as varchar datatype. When i run the query it shows:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I googled a bit and found the problem because of returning more than 1 value in a sub query.

But in my case i need all the values which lies between the given input. Can anybody tell me what way i can achieve this ..

Code:

SELECT ROW_NUMBER()OVER(
                        ORDER BY po.id) AS SNo ,
       pd.Copies AS Quantity,
       pd.EstUnitPrice AS UniPrice,
       pd.Copies*pd.EstUnitPrice AS Total,

  (SELECT value
   FROM BibContents
   WHERE bibid=pd.BibId
     AND sfld='a'
     AND tagno='245') AS Title,

  (SELECT value
   FROM BibContents
   WHERE bibid=pd.BibId
     AND sfld='a'
     AND tagno='020') AS 'ISSN/ISBN',

  (SELECT value
   FROM BibContents
   WHERE bibid=pd.BibId
     AND sfld='a'
     AND tagno='100')AS Author
FROM  [VibrantMas].[dbo].[PoDetails] AS pd
      INNER JOIN Porders AS po ON po.Id=pd.PoId
WHERE po.No BETWEEN '000021' AND '000024'

tell you what, how about you format your TSQL nicely... – Mitch Wheat Feb 20 '13 at 06:39 — Mitch Wheat, Feb 20 '13 at 06:39
Sorry for wrong formatting the query. – UniqueChar Feb 20 '13 at 06:44 — UniqueChar, Feb 20 '13 at 06:44

score 2 · Accepted Answer · edited May 23 '17 at 12:12

It's not the BETWEEN that is throwing the error but one of the subselects on BibContents.

You have two options

take the easy route and change each SELECT value with SELECT TOP 1 value
get to the root cause of why one of those subselects is returning multiple records.
- If it shouldn't return multiple records, you should add a unique constraint to BibContents preventing this from happening in the first place.
- If multiple records can occur for a given PoDetails, you have to decide wich one you'd like to return.
- If you want all of them returned, you'll have to change the subselects to proper joins.

My advice would be to save yourself from a maintenance nightmare and solve the root cause.

Working out another scheme for BibContents wouldn't hurt either. It looks like you've adopted the EAV model theme wich I think is a bad idea.

@Dhevy - What solution did you end up using? – Lieven Keersmaekers Feb 20 '13 at 07:49 — Lieven Keersmaekers, Feb 20 '13 at 07:49
Returning single result from PoDetails. – UniqueChar Jun 04 '13 at 05:09 — UniqueChar, Jun 04 '13 at 05:09

Operator BETWEEN in SQL Server

1 Answers1