In SysObjects, both functions and stored procedures are listed with type='P'. I've also checked SysProcedure table but couldn't find a column to differentiate between these two types.

Is there anyway to separately list functions and sp's without parsing their definitions?

Version: ASA 9

asked 30 May '11, 11:17

henginy's gravatar image

henginy
406151827
accept rate: 0%

edited 15 Mar '13, 21:44

Mark%20Culp's gravatar image

Mark Culp
24.8k9139296


I am sure there are several different answers to your question, so here is my suggestion. I believe the following query will list all functions within the database:

select p.proc_name from sysprocedure p,sysprocparm pp where p.proc_id = pp.proc_id and pp.parm_type=4

As I said, there are probably several different answers to your question and I am sure others will provide their own solution.

permanent link

answered 30 May '11, 11:29

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

2

Interesting approach...

So the following would list all procedures:

select p.proc_name from sysprocedure p
except
select p.proc_name from sysprocedure p,sysprocparm pp
   where p.proc_id = pp.proc_id and pp.parm_type = 4
(30 May '11, 11:37) Volker Barth
Replies hidden
Comment Text Removed

The following might be more efficient:

select distinct p.proc_name from sysprocedure p,sysprocparm pp where p.proc_id = pp.proc_id and pp.parm_type <> 4

(30 May '11, 11:55) Karim Khamis

Yes, both queries work, thanks. I guess sysprocparm.parm_type=4 means the "procedure" has a return parameter.

(30 May '11, 11:56) henginy
2

...but wouldn't it omit procedures that neither have any parameters nor a result clause, something like

create proc MyProc() begin ... end;
(30 May '11, 12:08) Volker Barth

Yes, you are absolutely correct Volker. Bad oversight on my part.

Karim

(30 May '11, 12:23) Karim Khamis

AFAIK, there's no official column in the system catalog to distinguish procedures and functions, and historically, they could be used somewhat interchangeable.

This question leads to a workaround.

EDIT:

Well, obviously Karim's suggestion is the better one - almost an "official solution":)

permanent link

answered 30 May '11, 11:30

Volker%20Barth's gravatar image

Volker Barth
39.7k357545815
accept rate: 34%

edited 31 May '11, 08:42

Thanks, interesting link.

(30 May '11, 11:57) henginy

For v17 (starting with build 17.0.4.2000), you can now filter by the newly added "is_deterministic" column (which requires a database upgrade or rebuild):

select * from sysprocedure
where is_deterministic is not null

returns stored functions and

select * from sysprocedure
where is_deterministic is null

returns stored procedures.

permanent link

answered 31 Mar '17, 03:13

Volker%20Barth's gravatar image

Volker Barth
39.7k357545815
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×125
×107
×32
×28

question asked: 30 May '11, 11:17

question was seen: 3,470 times

last updated: 31 Mar '17, 03:13