Solved

SQL Stored Procedure If Else

Posted on 2008-06-16
8
206 Views
Last Modified: 2010-04-21
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
Comment
Question by:Jimbo99999
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21793332
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21793335
is this what you're asking?

IF EXIST(Select EngineHP from Motor where EngineSize = '400')
BEGIN
--processing here
END
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21793343
angelIII, what happens if your query returns more than one result when trying to assign a value to the @EngineHP variable?
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21793492
Before writing this as an if/else struct I would try to combine the two operations into one more complicated query.
0
 

Author Closing Comment

by:Jimbo99999
ID: 31467581
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21794195
IF there are any records returned from this:

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

Then you will get an error.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21794262
>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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21794310
whoops...I was thinking assignment from a subquery....
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question