?
Solved

Sybase Stored procedue doubt

Posted on 2012-04-05
2
Medium Priority
?
821 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
[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
2 Comments
 
LVL 14

Accepted Solution

by:
Jan Franek earned 2000 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, we’ll look at how to deploy ProxySQL.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses

752 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