SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Thursday, June 29, 2006

SELECT...CASE in stored procedures

SQL Apprentice Question
I m writing some stored procedures.

Pls tell me, how can we use IF..ELSE, nested IF..ELSE in stored
procedure. means i want to pass a int parameter to stored procedure &
according to this value, we will execute my queries.

eg if i=1 then insert, if i=2 then update or if i=3 then update. Also i
can choose different SELECT statement according to i.

Now how can i use the SELECT....CASE in stored procedure. means same
above thing i want to do with SELECT....CASE. For example, for case 1,
i want to write insert statement, for case 2 update statement & for
case 3 delete statement.

pls give details

Celko Answers
>> if i=1 then insert, if i=2 then update or if i=3 then update. Also I can choose different SELECT statement according to i. <<

Have you ever had a course in basic software engineering? Look up
OF THING!! It screams that you have no business programming! What are
they teaching in colleges these days?

This is how well a module does one and only one thing; that it is
logically coherent. The modules should have strong cohesion. You
ought to name the module in the format "[verb][object]", where the
"[object]" is a specific logical unit in the data model. There are
several types of cohesion. We rank them going from the worst form of
cohesion to the best

1) Coincidental
2) Logical
3) Temporal
4) Procedural
5) Communicational
6) Informational
7) Functional

If modules have to be used in a certain order, then they are strongly
coupled. If they can be executed independently of each other and put
together like Lego blocks, then they are loosely or weakly coupled.
There are several kinds of coupling, which are ranked from worse to

1) Content
2) Common
3) Control
4) Stamp
5) Data

This is covered briefly in a chapter on writing stored procedures in my
book on SQL PROGRAMMING STYLE. In the meantime, you can read DeMarco,
Yourdon, Constantine, Myers or several other of the pioneers.

This is FAR more basic than SQL programming. This is what you are
supposed to know before you write any code in any language. Please go
back to school and get some foundations.

No comments: