t-sql

declare @ReqStatusDE int

if @ReqStatusDE in (select id from Requisition_Status_DE rsde where EntryCode in ('LockWait', 'Locked', 'Trans', 'LocNevTran'))


The select statement returns 5 rows.. What error will the above syntax give you?
LVL 1
cheryl9063Asked:
Who is Participating?
 
DcpKingCommented:
If you're getting

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

it's probably also giving you a line number, etc. It isn't always 100% accurate at to line number. In addition, it can be totally way out if the error is in dynamic SQL !  Take a look at the code around the indicated error line to see if you can find any line that says something like

<@variable>  =   (select <field> from <tablename>

or, in a query, a condition including

<fieldname>  =   (select <field> from <tablename>

If you do, check out what the right-hand-side will evaluate to. If it'll give multiple records then that may be your problem instruction.

hth

Mike
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>What error will the above syntax give you?
Likely 'Can't find table Requisition_Status_DE', as I don't have access to your data source.

What's your question?

Maybe...

declare @ReqStatusDE int

SELECT @ReqStatusDE = 42  -- or something

IF EXISTS (
   select id
   from Requisition_Status_DE rsde
   where EntryCode in ('LockWait', 'Locked', 'Trans', 'LocNevTran')
      and id = ReqStatusDE )
 
  begin
   -do whatever if it exists
  end
0
 
cheryl9063Author Commented:
Would it give you the error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. ?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
cheryl9063Author Commented:
A vendor did this and we are getting that error.. I think this is the part of the code that needs to be changed..
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Shouldn't, as it's not assigning a value, just detecting the existence of a value.

You can also try this...

Declare @the_count int
select @the_count = Count(id)
   from Requisition_Status_DE rsde
   where EntryCode in ('LockWait', 'Locked', 'Trans', 'LocNevTran')
      and id = ReqStatusDE

IF @the_count > 0
  begin
  -- do stuff
  end

If you don't need the exact count, testing for @@ROWCOUNT would work too
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
it wont throw any error because of the 'IN', if it was an '=' it would have.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.