Check Different Select query result
-
Dear all, Please suggest me to write shortest and effective query in Storeprocedure. To assign value to @Color, there will be different select queries with different conditions, My query sample will be as the following,
Select @result=count(*) from table where condition1
IF @result = 20
Begin
@Color='Red Color'
End
Else
Begin
select @result=count(*) from table where condition2
IF @result=20
Begin
@Color='Blue Color'
End
Else
Begin
select @result=count(*) from table where condition3
IF @result>0
Begin
@Color='Blue Color'
End
Else
Begin
Select @result=count(*) from table where Condition4
IF @result>0
Begin
@Color='Blue Color'
End
Else
Begin
Select @result=count(*) from where Condition5
IF @result=20
Begin
@Color='Green Color'
End
Else
Begin
Select @result=count(*) from where Condition6
IF @result>0
Begin
@Color='Yellow Color'
End
Else
Begin
@Color='Orange Color'
End
End
End
End
End
EndThanks and best regards
What's the best way to combine these statements depends totally on the conditions you're going to use. For example if the conditions fetch different subsets from the tables and you have written the if statements in such order where they most likely are true, then combining the different selects could lower the performance.
The need to optimize rises from a bad design.My articles[^]
-
You should let us know what database you are using. Look into
CASEif you are using SQL ServerNever underestimate the power of human stupidity RAH
Thank you for your reply. So far what I've found CASE are use like below
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
ENDUPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END
)But, I have no idea how to use in my query. Could you please give me some samples by using my scenarios? Thanks and best regards
-
What's the best way to combine these statements depends totally on the conditions you're going to use. For example if the conditions fetch different subsets from the tables and you have written the if statements in such order where they most likely are true, then combining the different selects could lower the performance.
The need to optimize rises from a bad design.My articles[^]
Thank you for you reply Sorry, I didn't get what you mentioned. This are some of my many different conditions
WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)= 0 And isNull(TDR_D40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H20,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_H45,0)=0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)
WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)>0 Or isNull(TDR_H20,0)>0 Or isNull(TDR_D40,0)>0 Or isNull(TDR_H40,0)>0 Or isNull(TDR_D45,0)>0 Or isNull(TDR_H45,0)>0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)
WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)=0 AND isNull(TDR_H20,0)=0 And isNull(TDR_D40,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H45,0)=0) AND ((isNull(FL_D20,0)>0 OR isNull(FL_D40,0) >0 OR isNull(FL_D45,0) >0 OR isNull(FL_H20,0) >0 OR isNull(FL_H40,0) >0 OR isNull(FL_H45,0) >0) OR (isNull(CLL_D20,0) > 0 OR isNull(CLL_D40,0) > 0 OR isNull(CLL_D45,0) > 0 OR isNull(CLL_H20,0) > 0 OR isNull(CLL_H40,0) > 0 OR isNull(CLL_H45,0)>0))
-
Thank you for your reply. So far what I've found CASE are use like below
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
ENDUPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END
)But, I have no idea how to use in my query. Could you please give me some samples by using my scenarios? Thanks and best regards
Using your second format you basically take your where clauses and place them between
WHENandTHENUPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN whereclause1 THEN VacationHours + 40
WHEN whereclause2 THEN VacationHours + 10
WHEN whereclause3 THEN VacationHours + 19
WHEN whereclause4 THEN VacationHours + 11
END
)The complexity of the where clauses it going to drive you nuts, sequencing them correctly will be a pain and debugging is going to be bloody horrible. Good luck
Never underestimate the power of human stupidity RAH
-
Thank you for you reply Sorry, I didn't get what you mentioned. This are some of my many different conditions
WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)= 0 And isNull(TDR_D40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H20,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_H45,0)=0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)
WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)>0 Or isNull(TDR_H20,0)>0 Or isNull(TDR_D40,0)>0 Or isNull(TDR_H40,0)>0 Or isNull(TDR_D45,0)>0 Or isNull(TDR_H45,0)>0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)
WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)=0 AND isNull(TDR_H20,0)=0 And isNull(TDR_D40,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H45,0)=0) AND ((isNull(FL_D20,0)>0 OR isNull(FL_D40,0) >0 OR isNull(FL_D45,0) >0 OR isNull(FL_H20,0) >0 OR isNull(FL_H40,0) >0 OR isNull(FL_H45,0) >0) OR (isNull(CLL_D20,0) > 0 OR isNull(CLL_D40,0) > 0 OR isNull(CLL_D45,0) > 0 OR isNull(CLL_H20,0) > 0 OR isNull(CLL_H40,0) > 0 OR isNull(CLL_H45,0)>0))
Ok, If I take an example. Perhaps the easiest way to combine different statements would be to use
union. For example if you have following logic (pseudo):select @result = count(*) from table where column1 < 100
if @result > 10 then color = blue
else
select @result = count(*) from table where column1 > 100
if @result = 20 then color = yellowNow you could combine these to something like:
cursor = select count(*) from table where column1 < 100
union all
select count(*) from table where column1 > 100
if first_row_in_cursor > 10 then color = blue
else if second_row_in_cursor = 20 then color = yellowHowever, as you see, both select statements would get the count from different rows. In example 1 the second query isn't executed at all if count > 10. In the second example all the selects are executed even if the results in different portions aren't needed. This can be very time (and resource) consuming. This was a trivial case and most likely isn't exactly the same as in your situation but the point I'm trying to make is that is it really beneficial to combine all the statements. Another point of view is that the statement may become very large and hard to maintain if it contains too much logic. Another (perhaps an easy ) option is to use scalar queries. This might work more easily for your case. The previous example could be something like:
select
@result1 = (select count(*) from table where column1 < 100)
@ersult2 = (select count(*) from table where column1 > 100);if @result1 > 10 then color = blue
else if @result2 = 20 then color = yellowHowever the same performance problem as described earlier may arise.
The need to optimize rises from a bad design.My articles[^]
modified on Friday, August 12, 2011 6:43 AM
-
Using your second format you basically take your where clauses and place them between
WHENandTHENUPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN whereclause1 THEN VacationHours + 40
WHEN whereclause2 THEN VacationHours + 10
WHEN whereclause3 THEN VacationHours + 19
WHEN whereclause4 THEN VacationHours + 11
END
)The complexity of the where clauses it going to drive you nuts, sequencing them correctly will be a pain and debugging is going to be bloody horrible. Good luck
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
The complexity of the where clauses it going to drive you nuts, sequencing them correctly will be a pain and debugging is going to be bloody horrible
This is very true if the where clauses are even a bit complex...
The need to optimize rises from a bad design.My articles[^]
-
Mycroft Holmes wrote:
The complexity of the where clauses it going to drive you nuts, sequencing them correctly will be a pain and debugging is going to be bloody horrible
This is very true if the where clauses are even a bit complex...
The need to optimize rises from a bad design.My articles[^]
-
Using your second format you basically take your where clauses and place them between
WHENandTHENUPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN whereclause1 THEN VacationHours + 40
WHEN whereclause2 THEN VacationHours + 10
WHEN whereclause3 THEN VacationHours + 19
WHEN whereclause4 THEN VacationHours + 11
END
)The complexity of the where clauses it going to drive you nuts, sequencing them correctly will be a pain and debugging is going to be bloody horrible. Good luck
Never underestimate the power of human stupidity RAH
-
Instead of trying to combine everything to a giant statement, could you go the other way: brake it into pieces. Don't know the whole situation, but could you for example create small, separate functions that fetch the desired info and in the 'main' logic use these functions (or procedures if you like). When you create the parameterized functions I would guess that at some point you notice the similarities between conditions if there are any so perhaps the amount of functions won't be as many as you now have different selects. Also during the process you may find different kind of options to build the desired output that are now hard to see. In any case, best of luck!
The need to optimize rises from a bad design.My articles[^]
-
Ok, If I take an example. Perhaps the easiest way to combine different statements would be to use
union. For example if you have following logic (pseudo):select @result = count(*) from table where column1 < 100
if @result > 10 then color = blue
else
select @result = count(*) from table where column1 > 100
if @result = 20 then color = yellowNow you could combine these to something like:
cursor = select count(*) from table where column1 < 100
union all
select count(*) from table where column1 > 100
if first_row_in_cursor > 10 then color = blue
else if second_row_in_cursor = 20 then color = yellowHowever, as you see, both select statements would get the count from different rows. In example 1 the second query isn't executed at all if count > 10. In the second example all the selects are executed even if the results in different portions aren't needed. This can be very time (and resource) consuming. This was a trivial case and most likely isn't exactly the same as in your situation but the point I'm trying to make is that is it really beneficial to combine all the statements. Another point of view is that the statement may become very large and hard to maintain if it contains too much logic. Another (perhaps an easy ) option is to use scalar queries. This might work more easily for your case. The previous example could be something like:
select
@result1 = (select count(*) from table where column1 < 100)
@ersult2 = (select count(*) from table where column1 > 100);if @result1 > 10 then color = blue
else if @result2 = 20 then color = yellowHowever the same performance problem as described earlier may arise.
The need to optimize rises from a bad design.My articles[^]
modified on Friday, August 12, 2011 6:43 AM
-
Instead of trying to combine everything to a giant statement, could you go the other way: brake it into pieces. Don't know the whole situation, but could you for example create small, separate functions that fetch the desired info and in the 'main' logic use these functions (or procedures if you like). When you create the parameterized functions I would guess that at some point you notice the similarities between conditions if there are any so perhaps the amount of functions won't be as many as you now have different selects. Also during the process you may find different kind of options to build the desired output that are now hard to see. In any case, best of luck!
The need to optimize rises from a bad design.My articles[^]
-
I hope this is a great idea. But to be honest I've never been used function and also not familiar. I'm gonna learn it on my Weekend. Any article or site you want to recommend?:) Thanks again and have a nice weekend.
naunt wrote:
Any article or site you want to recommend?
I think there are lots of articles and sites about both so I have no specific suggestions. With a quick look I found these: - http://databases.about.com/od/sqlserver/a/procs_vs_functs.htm[^] - http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server[^] And of course go through msdn pages: - Stored procedures[^] - Returning Data from a Stored Procedure[^] - Create procedure[^] - Create function[^]
naunt wrote:
Thanks again and have a nice weekend.
You too :)
The need to optimize rises from a bad design.My articles[^]
-
Dear all, Please suggest me to write shortest and effective query in Storeprocedure. To assign value to @Color, there will be different select queries with different conditions, My query sample will be as the following,
Select @result=count(*) from table where condition1
IF @result = 20
Begin
@Color='Red Color'
End
Else
Begin
select @result=count(*) from table where condition2
IF @result=20
Begin
@Color='Blue Color'
End
Else
Begin
select @result=count(*) from table where condition3
IF @result>0
Begin
@Color='Blue Color'
End
Else
Begin
Select @result=count(*) from table where Condition4
IF @result>0
Begin
@Color='Blue Color'
End
Else
Begin
Select @result=count(*) from where Condition5
IF @result=20
Begin
@Color='Green Color'
End
Else
Begin
Select @result=count(*) from where Condition6
IF @result>0
Begin
@Color='Yellow Color'
End
Else
Begin
@Color='Orange Color'
End
End
End
End
End
EndThanks and best regards
I don't know your data but you could try to get all your counts in one go using a CASE WHEN. The COUNT aggregates only non null values.
SELECT
@result1 = COUNT(CASE WHEN (isNull(TDR_D20,0)= 0 And isNull(TDR_D40,0)=0 ...)
THEN 1 ELSE NULL END),
@result2 = COUNT(CASE WHEN (isNull(TDR_D20,0)>0 Or isNull(TDR_H20,0)>0 ...)
THEN 1 ELSE NULL END),
@result3 = COUNT(CASE WHEN (isNull(TDR_D20,0)=0 AND isNull(TDR_H20,0)=0 ...))
THEN 1 ELSE NULL END)
FROM table
WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POLThis would be efficient as you would only read your table once this way. Then the logic you can probably remove quite a bit of the begin end and it becomes (sometime) clearer.
IF @result1 = 20 SET @Color='Red Color'
Else IF @result2=20 SET @Color='Blue Color'
Else IF @result3>0 SET @Color='Blue Color'
Else IF @result4>0 SET @Color='Blue Color'
Else IF @result5=20 SET @Color='Green Color'
Else IF @result6>0 SET @Color='Yellow Color'
Else @Color='Orange Color'