Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

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
0
Jimbo99999
Asked:
Jimbo99999
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here we go:
CREATE PROCEDURE yourproc 
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @EngineHP varchar(100) 
  SELECT @EngineHP = EngineHP from Motor where EngineSize = '400'
  IF ROWCOUNT = 0 -- no rows found
    SELECT 'EngineSize not found' result
  ELSE 
    SELECT .... whatever you need ...
   
END

Open in new window

0
 
chapmandewCommented:
is this what you're asking?

IF EXIST(Select EngineHP from Motor where EngineSize = '400')
BEGIN
--processing here
END
0
 
chapmandewCommented:
angelIII, what happens if your query returns more than one result when trying to assign a value to the @EngineHP variable?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Joel CoehoornDirector of Information TechnologyCommented:
Before writing this as an if/else struct I would try to combine the two operations into one more complicated query.
0
 
Jimbo99999Author Commented:
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
0
 
chapmandewCommented:
IF there are any records returned from this:

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

Then you will get an error.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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)
0
 
chapmandewCommented:
whoops...I was thinking assignment from a subquery....
0

Featured Post

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!

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