Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jpiscit1
Posting Yak Master
130 Posts |
 Posted - 2004-10-06 : 10:21:26
|
| Im building a stored procedure that needs to test conditions and return results based on the test condition.For example:Table 1 has a field called Fiber1_Wet_Weight_SP which is simply a numeric value.I want to test if its value is greater than zero (tells me whether or not it was used) and then return a result set of various RELATED fields if yes. If no, return nothing. I could use a IF statement for this but I am dealing with 15 other fields I need to test. The prodcedure would get rather lengthy. What is the most efficient way to handle this? A case/select ? Does anyone have an example? Thanks!! |
|
|
Bitz
Starting Member
19 Posts |
Posted - 2004-10-06 : 12:53:39
|
| You are on the right track; it almost sounds like this is a style thing. :)With the assumption that you plan on returning only ONE result set, a bunch of IF statements would work.SELECT ... CASE ... END works great too. It all depends on the tables you are querying.You may also consider using UDFs for simple tests, especially if they are reusable. For example, you could write something like UDF_IsFiberWetWeightUsed which tests whether or not it was used and returns the appropriate results.Hope this helps. If you want to get into specifics, maybe I can help further. |
 |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-07 : 04:12:39
|
| wait a minute....are you saying "if the column Fiber1_Wet_Weight_SP in a record = 1 in table1 then return the record with various related columns in that table and related tables" ?how is this diefferent from "...where TABLE1.Fiber1_Wet_Weight_SP =1" ?Hemanth GorijalaBI Architect / DBA... |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2004-10-07 : 10:11:38
|
| Actually what I was saying is "if the column Fiber1_Wet_Weight_SP in a record > 1 in table1 (meaning there was a setpoint and that ingredient was being used) then return the record with various related columns in that table and related tables" I mangaged to use IF/BEGIN statements to work through this. I was more interested in which commands run quicker IF/BEGIN/END or SELECT/CASE..... But it seems to run very fast with the IF's... Thanks. |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2004-10-07 : 10:12:43
|
| I meant... "if the column Fiber1_Wet_Weight_SP in a record > 0 in table1 (meaning there was a setpoint and that ingredient was being used) then return the record with various related columns in that table and related tables" Sorry |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-07 : 11:37:23
|
| do you know about JOINS ? you might need to read up on them in books on-line, or get a good beginner book on SQL.- Jeff |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-08 : 00:37:14
|
quote:
Originally posted by jpiscit1
I meant... "if the column Fiber1_Wet_Weight_SP in a record > 0 in table1 (meaning there was a setpoint and that ingredient was being used) then return the record with various related columns in that table and related tables"
post your code..Hemanth GorijalaBI Architect / DBA... |
|
|
|
|
|
|
Test Conditions