Link to home
Start Free TrialLog in
Avatar of Jimbo99999
Jimbo99999Flag for United States of America

asked on

SQL Stored Procedure If Else

Good Day
Experts:

What is the best way to condition on a Select query is a Stored Procedure to determine whether to process code or not?

Let's say I have --> Select EngineHP from Motor where EngineSize = '400'

I would want to process some code in the S/P is the query returned a record with a value for EngineHP.
If no value found/retrieved issue to User "EngineSize not found"

I have tried  a CURSOR but could not get that to work.  

What would be my best option here?

Thanks,
jimbo99999
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
is this what you're asking?

IF EXIST(Select EngineHP from Motor where EngineSize = '400')
BEGIN
--processing here
END
angelIII, what happens if your query returns more than one result when trying to assign a value to the @EngineHP variable?
Before writing this as an if/else struct I would try to combine the two operations into one more complicated query.
Avatar of Jimbo99999

ASKER

EngineSize is part of the key so this is what I am looking for.  I was missing the ROWCOUNT interogation and result  after 'Engine not found'.

Thanks for the help...jimbo99999
IF there are any records returned from this:

SELECT @EngineHP = EngineHP from Motor where EngineSize = '400'

Then you will get an error.
>IF there are any records returned from this:
>SELECT @EngineHP = EngineHP from Motor where EngineSize = '400'
>Then you will get an error.

in ms sql server, if there are 0 or > 1 records, there will be NO error.
if there are 0 records, the value of @EngineHP will not change.
if there are > 1 records, the value will be any of the rows' values (usually, the "last" one returned)
whoops...I was thinking assignment from a subquery....