Jimbo99999
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
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.
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)
>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....
IF EXIST(Select EngineHP from Motor where EngineSize = '400')
BEGIN
--processing here
END