Difference between Stored Procs and Macros

General
Enthusiast

Difference between Stored Procs and Macros

Hi,

This question comes from my last post about the Teradata programming 'language'.

I want to write Loops and do calcs the same way as I do using VBA.

I would like to use Stored Procs and or Macros

Can someone explain the difference between the 2 & what's the best way forward to write my programs?

Thanks 


Accepted Solutions
Junior Contributor

Re: Difference between Stored Procs and Macros

Cursors are Selects which are processed row by row in a loop to do some calculation.

 

There's almost no need for it because there's CASE and OLAP functions, again, read George Colemab 's blog, e.g. Learning to Think in Set SQL

1 ACCEPTED SOLUTION
8 REPLIES
Junior Contributor

Re: Difference between Stored Procs and Macros

Macros are very old (30+ years?) and support only parameterized statements, i.e. no loops, if/then/else, error handling, etc.

 

Stored Procedures are newer (20 years) and support Standard SQL procedural language, loops, if/the/else, error handling, etc.

 

In short: Macros are used to pass parameters to (a group of) DML statements, SPs for everything else.

 

DBA usually don't like to grant you the right to create SPs becuse you can do bad things like cursors & loops, you must be a kind of trusted user who is know not to write bad SQL. But the right to create macros is granted as a default to every user.

Enthusiast

Re: Difference between Stored Procs and Macros

Thanks dnoeth,

So if these methods are old, what's the future for Loops etc that maybe I should investigate?

To avoid running Loops in Teradata, I've connected to Teradata using VBA in an Excel workbook where I extract data I need to use in Loops & calcs.

Is this the best way to go or does Teradata have another option?

Thanks

 

 

 

 

Junior Contributor

Re: Difference between Stored Procs and Macros

SPs are not old, they're mature :-)

 

Of course you can use an SP to process loops, etc., but performance might be less than expected as SQL is for processing sets of data once. Users not proficient in SQL usually know how to process data step-by-step or row-by-row, but in most cases there's a set-based solution, too.

George Coleman has some good blogs about this topic

 

Maybe you can share what you're calculating in those loops?

Enthusiast

Re: Difference between Stored Procs and Macros

I don't have any specifics to explain as this is more about what's best to use rather than how do I do this with this data.

I have an Excel file which connects to the company Teradata front end, so I'm going to write any Loops there in VBA - does that seem like a better option? (also keeping the DBAs happy!!  :  >).

Can you tell me what specifically is bad about running Loops in Teradata SPs? I'm curious to know.

Thanks 

Teradata Employee

Re: Difference between Stored Procs and Macros

Inside the DB, it's way better to do a join rather than a loop. Performance, readability and so on.

 

If you have outside data, like in an Excel sheet, it's better to load the needed data inside the DB and then do a join.

Highlighted
Junior Contributor

Re: Difference between Stored Procs and Macros

There are (rare) cases where you need loops.

So loops are not bad per se, but they're sequential in nature and repeatedly processing subsets of data is usually less efficient than processing all data once.

 

When you're proficient in VBA and performance is ok, you can still use it, of course.

If you can process it in Excel it can't be a large amount of data at least :-)

 

Enthusiast

Re: Difference between Stored Procs and Macros

Thanks dnoeth,

Can you tell me what a Cursor is and why they maybe shouldn't be used?

I haven't heard of cursors much.

Thanks again.

Junior Contributor

Re: Difference between Stored Procs and Macros

Cursors are Selects which are processed row by row in a loop to do some calculation.

 

There's almost no need for it because there's CASE and OLAP functions, again, read George Colemab 's blog, e.g. Learning to Think in Set SQL