• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

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?
0
cheryl9063
Asked:
cheryl9063
2 Solutions
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now