Difference between a macro and a stored procedure? 1

[CapsuleCorpJX]

Is there anything a stored procedure can do that a macro can't?

 

[dnoeth]

- define and set variables
- IF/THEN/ELSE
- loop using WHILE/REPEAT
- dynamic SQL
- cursors
- error handling

Dieter

 

[shridana]

I have a question here regarding the macro and SP.

I know that a macro can reference a global temporary table and volatile table.

Can a stored proc also do that? If not, then that will also be a difference between the two.

 

[dnoeth]

Of course it can.

But there's one thing only a macro can do:
return answer sets

To do that within a SP you have to insert/select the data into a global temp table and after CALLing the SP select from that table.

Dieter

 

[shridana]

Another way by which your stored procedure can return an ouput is using a cursor. Cursors are used only for multiple row outputs.