trick question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Nitin Rana (Nitin_Rana_at_ibi.com)
Date: 07/22/04 

Date: Thu, 22 Jul 2004 14:30:44 -0700

This is not at all a tricky question.
All you need to do is loop through ID in sysobjects table
where type = 'P' and pass that ID to this code. If that
proc has any input parameters then it will display Proc
name, parameter name and other things. Run it and you
should be good to go.

Enjoy

-Nitin

@ProcId refers to the ID of a stored procedure in my user
DB, You need to loop through it.

declare @procId int
Select @procId = 161904144
  if exists (select id from syscolumns where id =
@ProcId)
  begin
   -- INFO ON PROC PARAMS
   print ' '
   select
    'Proc Name' = Object_Name(@ProcId),
    'Parameter_name' = name,
    'Type' = type_name(xusertype),
                'Length' = length,
                'Prec' = case when type_name(xtype)
= 'uniqueidentifier' then xprec
          else OdbcPrec(xtype, length, xprec) end,
                'Scale' = OdbcScale(xtype,xscale),
                'Param_order' = colid,
    'Collation' = collation
   from syscolumns where id = @ProcID
        END
 

>-----Original Message-----
>how to find out if any of procedures contain optional
parameter?
> logic is , if there is a "=" sign before first "AS",
then I think there
>will be at least one optional parameter.
>
>WELL, this query will not solved my problem. is there a
way?
>
>
>
>SELECT ROUTINE_NAME
>
> FROM INFORMATION_SCHEMA.ROUTINES
>
> WHERE UPPER(ROUTINE_DEFINITION) LIKE '%=%AS%'
>
>
>
>.
>

Relevant Pages

  • Re: more CommEvents than expected
    ... He resets proc to 0 at the beginning of the loop and only sets it to 1 when ... times even if WaitCommEvent is not called in the for loop. ... 1> clear dwEvtMask every time when you get into the new loop, ...
    (microsoft.public.win32.programmer.kernel)
  • Re: Deleting in a cursor loop
    ... cursor for loop and an iterative commit within the loop every 300,000 ... This is the proc: ... CURSOR delrowcur IS ... IF (RCOUNT>= 300000) THEN ...
    (comp.databases.oracle.server)
  • Re: tcl lists
    ... i need to be able to set ALL the elements of the list in the same loop. ... other lists with different numbers or arguments which can be passed to ... the runVpn proc. ... proc SetOpt { ...
    (comp.lang.tcl)
  • Re: Deleting in a cursor loop
    ... cursor for loop and an iterative commit within the loop every 300,000 ... CURSOR delrowcur IS ... into this table while also purging from it with this proc, ...
    (comp.databases.oracle.server)
  • Re: trick question
    ... will still produce a proc name and that wont be true. ... Create Procedure Test ... >> All you need to do is loop through ID in sysobjects ... >> declare @procId int ...
    (microsoft.public.sqlserver.programming)