sql server programming: When programming logics require "if then do something" and in the "do
something" there could be another if then.. and so on, making the codes very
difficult to read.

It is a general programming skill to combat this by having one main function
to call other subroutines. In traditional progamming I simply put other
subroutines at the end of the "module" or, in other words, put in the same
piece of code.

I found I can't use the same skill for stored procedures. Do I miss something?

For example, this is my present codes in stored proc sp1 is:
Declare @xxx cursor for ...
Declare @yyy int..
....
while @@status = 0 begin
if @yyy = 1 exec sp2
if @yyy = 2 exec sp3
end

Can I add the codes in stored proc sp2, sp3 onto two subroutines within sp1?
I hope my question makes sense to you. Many thanks.

You will have to create the sp2 and sp3 procs separately. They can then be
called from any piece of code - including another stored proc.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


[quoted text, click to view]
When programming logics require "if then do something" and in the "do
something" there could be another if then.. and so on, making the codes very
difficult to read.

It is a general programming skill to combat this by having one main function
to call other subroutines. In traditional progamming I simply put other
subroutines at the end of the "module" or, in other words, put in the same
piece of code.

I found I can't use the same skill for stored procedures. Do I miss
something?

For example, this is my present codes in stored proc sp1 is:
Declare @xxx cursor for ...
Declare @yyy int..
....
while @@status = 0 begin
if @yyy = 1 exec sp2
if @yyy = 2 exec sp3
end

Can I add the codes in stored proc sp2, sp3 onto two subroutines within sp1?
I hope my question makes sense to you. Many thanks.

Without seeing your actual source code, DDL, etc., I'll take a W.A.G. at it.
Try passing the value of @@status back from your sub-procedures to the main
procedure via an OUTPUT parameter.

[quoted text, click to view]

BTW, you are attempting to pass a cursor to the sub-SP's? There may be a
set-based solution to do what you're attempting which might just be more
efficient. Hard to tell without knowing exactly what you're doing.

[quoted text, click to view]

ykffc (ykffc@discussions.microsoft.com) writes:
[quoted text, click to view]

I don't understand what you mean with "add the codes in stored proc...".

In any case, SQL is not like C# or some other traditional language.
Modularisation may still be needed, but the thinking must be different.
I see in your example that you have a cursor. Iterative solutions
is something you rarely need, and most of all they give bad performance.

But without knowing you actual business problem, and seeing your actual
procedures, it's difficult to be that more precise.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at