Re: Establishing if procedure parameters are mandatory


"ThanksButNo" <no.no.thanks@xxxxxxxxx> wrote in message
news:1192074460.877648.20300@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Oct 5, 3:40 am, Mintcake <t...@xxxxxxxxxxxxxx> wrote:
Is there anywhere in the system catalogues (or elsewhere) that you can
look to see if a stored procedure parameter declaration has a default
parameter value specified. I don't need to know what the default is
(though that might be useful too) just want to know if the parameter
must be specified in the call.

Try this:

select c.text
from syscomments c,
sysobjects o,
sysusers u
where 1=1
and c.id = o.id
and o.uid = u.uid
and o.name = '{name of procedure}'
and u.name = '{name of owner}'
order by
c.colid

That gives you the full text of the stored procedure in question.

I'm not familiar enough with how stored procedures to tell you what to
look for, but if you look through there, it ought to be fairly
obvious.

If you're looking for something automatic
(e.g., IF HasDefaults(proc) THEN)
then I'm afraid I can't help.

:-(


Quibble - "where 1=1" is obviously unnecessary. And sysusers can be
eliminated via "and o.uid = user_id ('{name of owner}')". Very large procs
should be ordered by, if I remember correctly, colid2, colid.

The expected source of the absence/presence of a default would be
syscolumns.cdefault. Unfortunately the value is always zero for a stored
procedure argument whereas it will be non-zero for a table column that has a
default. I suspect that it always zero for a view column as well.


.

Relevant Pages

  • Re: Cant edit form data from stored procedure
    ... If you want to display zero when the field is null, you can apply a number format to the control displaying the value. ... If you want to change the value that is stored to zero, then you will have to use some VBA code on the form to change the null to zero. ... I have narrowed the problem down to how the stored procedure returns the data which I find very odd. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Retrieve Unique Table Value
    ... The @@Error will be zero, ... if so then this stored procedure ... >>it will get a different reg number, ... >>Programmer Analyst III ...
    (microsoft.public.sqlserver.programming)
  • RE: Problem with Output param for stored procedure
    ... As I understand your Query Analyzer results you pass zero and it returns 1. ... > I'm trying to get an error code back from a sqlserver stored procedure; ... > using an Output param to do it. ...
    (microsoft.public.sqlserver.programming)
  • Re: Catch duplicate primary key exeption
    ... If you are doing the insert via a stored procedure, ... "countfrom mytable" and see if that is greater than zero. ... similar exception. ... Prev by Date: ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Establishing if procedure parameters are mandatory
    ... look to see if a stored procedure parameter declaration has a default ... That gives you the full text of the stored procedure in question. ... look for, but if you look through there, it ought to be fairly ... then I'm afraid I can't help. ...
    (comp.databases.sybase)