Solved

Sybase Stored procedue doubt

Posted on 2012-04-05
2
812 Views
Last Modified: 2012-08-13
Hi Experts...
I have around say 50 variables in a sybase stored procedure whose value is calculated within the stored procedure by a mathematical procedure.
I have a condition such that if the value of the 50 variables is  lesser than 10 then the value should be made as 0.
I could do as follows:
if @val1 < 10
         select  @val1=0
if @val2 < 10
         select @val2=0 ...........and so on till 50 values.

Is there a way in sybase to change all these values to 0 if the values of the variables  are less than 10 or keep them as it is if they have a value greater than 10.
Please help...
0
Comment
Question by:gaugeta
2 Comments
 
LVL 14

Accepted Solution

by:
Jan_Franek earned 500 total points
ID: 37812433
AFAIK there is no such way.

You may create subprocedure with 1 parameter to check and modify one value:

create procedure my_sub_procedure ( @val int output )
as
if @val < 10
  select @val = 0
go

and then replace all your if's in main procedure:

exec my_sub_procedure @val1 output
exec my_sub_procedure @val2 output
etc.

This is not much better than original. But if you decide to move your limit from 10 to 20,  you just have to change one short subprocedure.

Just to be sure - do you really need procedure ? SQL is set oriented and is much more effective when dealing with whole table, than dealing with one row at a time.
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37843274
I'd put the 50 variables into a temp table, one row per variable, then run a single update to apply your logic.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

828 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