Various ways to create procedures

[patrickbachmann]

Hi folks,

OPTION1: I'm working on a procedure and I'm doing it via CREATE PROCEDURE syntax using SQL editor.  When I do it this way my procedure is saved under my schema.  However if I want to make changes to this procedure after initial creation, it seems I have to delete original and then tweak my SQL in the editor and execute it again.  It seems to be read only each time. 

OPTION2: As an alternative I can also write a procedure in content using procedure editor (with interface for creating inputs and outputs) by right-clicking on a package and choosing NEW PROCEDURE.  This way allows me to repeatedly EDIT my procedure however I don't see how to type declarations above the BEGIN.  For example my procedure could be declaring CURSOR prior to the BEGIN step when written via SQL editor but I don't see how to accomplish this when using procedure editor.

So I have two questions;

1) Is there an easier and less cumbersome way to repeatedly edit my procedure using option1?

2) Is it possible to have declarations before BEGIN using option 2?

Thanks,

-Patrick

[thomas_jung · Accepted solution]

You really shouldn't use the procedure editor you describe in option two via the content folder. It has since been replaced by a new editor for procedures via projects. You create a .procedure file (or in SPS7 .hdbprocedure).  These have the benefits for editing as you describe in option two but use a source code based editor allowing to declare what you want before the BEGIN statement as you would in the SQL Editor.  Furthermore this option allows better lifecycle management over option one as you transport your procedures via a delivery unit.  Option two via the newer editor I describe also now lets you specify whatever Schema you want to create the procedure within.

[patrickbachmann · Accepted solution]

Hi Thomas,

That's interesting, you would think the second option would be hidden.  So if I do it this new way via projects as you suggest will I be able to consume the procedure using a calculation view like I can do with option2?  Also is it possible to have declarations like this directly in a calculation view to avoid having to call a separate procedure?

Thanks,

-Patrick

[thomas_jung · Accepted solution]

>you would think the second option would be hidden

For backwards compatibility reasons it hasn't been hidden yet but probably will in the near future.  We want to give everyone a few SPs to switch over to using the newer tool/approach. 

> So if I do it this new way via projects as you suggest will I be able to consume the procedure using a calculation view like I can do with option2?

Certainly.   Although completely unrelated you can create and edit all view types via a project now - that is a requirement for using such procedures.

>Also is it possible to have declarations like this directly in a calculation view to avoid having to call a separate procedure?

What kinds of declarations?  The CURSOR?  Not sure as I don't focus on the Calculation view. However I can say its by intention that only a subset of SQLScript functionality is available there. That's why you have the full procedure capabilities for everything else.

[patrickbachmann · Accepted solution]

Ok thanks again Thomas.  Will close this thread shortly.

-Patrick

[patrickbachmann · Accepted solution]

Thomas,

I created a project and then a SQLScript Procedure inside the project and I really like this editor since it has the syntax checker and colors etc.  But strangely I get message NO CONNECTION TO DATABASE and I don't see option to choose a connection like I would in SQL editor. 

-Patrick

[thomas_jung · Accepted solution]

Did you share the project to the repository?  That's the only reason it would show that.  By sharing the project you make the connect to the database automatically.

[patrickbachmann · Accepted solution]

Ahhh that was exactly it!  Thanks Thomas!

[patrickbachmann · Accepted solution]

Hey Thomas, final question... how do I call the procedure?  ie: it is now inside project and inside repository.  Is there some path I have to specify now in the call?  It's called XYZ.procedure.  I tried;

CALL XYZ(?,?)

CALL XYZ.procedure(?,?)

CALL myRepository.myProject.XYZ(?,?)

CALL myRepository.myProject.XYZ.procedure(?,?)

I get invalid name of function.

-Patrick

[thomas_jung · Accepted solution]

It should be <SCHEMA>.<package path>::procedure name.  It should be the name as you see it in the procedure editor if you use .hdbprocedure. 

https://dl.dropboxusercontent.com/u/643382/Procedures.png

If you use .procedure, the package name is not shown in the editor and the name would have be adjusted accordingly.  You can also look in the catalog->schema and find your procedure to see the name.  If you don't specific a schema, remember that they go into _SYS_BIC.

[patrickbachmann · Accepted solution]

Nevermind, I figured it out...

CALL myProject.XYZ(?,?)

:O)

[patrickbachmann · Accepted solution]

Thanks Thomas, that's how I found it in the catalog.