stored procedure parameters 
Author Message
 stored procedure parameters

I asked this question earlier, but not receiving a response over
the weekend, I'm trying again.

     I would like to get information about stored procedure parameters
by querying the database.  I know how to get parameter names, types,
and lengths, but what I don't know is whether a parameter is an OUTPUT
parameter or whether it has a default value.  Is there anybody who
knows how to do this (short of parsing the sp_helptext output), or
knows that it can't be done?

Thanks,
William S. Heck



Fri, 10 Dec 1999 03:00:00 GMT
 stored procedure parameters


  I would like to get information about stored procedure
parameters

Quote:
>by querying the database.  I know how to get parameter names, types,
>and lengths, but what I don't know is whether a parameter is an OUTPUT
>parameter or whether it has a default value.  
>Thanks,
>William S. Heck

You probably want a short-term solution.  I do not have one.  A
longer term solution would be:  use a default value for
parameters.  This will allow you to provide help on how to use
a stored procedure.   For example,

AS

BEGIN
   PRINT "To run this procedure enter EXEC test value"
   PRINT "where the value is an integer between 1 and 10"
   RETURN
END
/*    Your code here    */
RETURN

This is not my idea but rather the idea of Jim and Mary
Panttaja and Judy Bowman.  See their "Sybase SQL Server
Survival Guide" book, well worth it in my opinion.

As to your idea for parsing output from sp_helptext, my opinion
is that the more you think about it, the less appealing it will
be.  As an old mainframer, I remind you that the blocking
factor of the output distorts the text.  So that scanning for a
certain value may fail when the value is truncated on one line
and continued on the next.

Tom McCready

202-707-1923



Fri, 10 Dec 1999 03:00:00 GMT
 stored procedure parameters


  I would like to get information about stored procedure
parameters

Quote:
>by querying the database.  I know how to get parameter names, types,
>and lengths, but what I don't know is whether a parameter is an OUTPUT
>parameter or whether it has a default value.  
>Thanks,
>William S. Heck

You probably want a short-term solution.  I do not have one.  A
longer term solution would be:  use a default value for
parameters.  This will allow you to provide help on how to use
a stored procedure.   For example,

AS

BEGIN
   PRINT "To run this procedure enter EXEC test value"
   PRINT "where the value is an integer between 1 and 10"
   RETURN
END
/*    Your code here    */
RETURN

This is not my idea but rather the idea of Jim and Mary
Panttaja and Judy Bowman.  See their "Sybase SQL Server
Survival Guide" book, well worth it in my opinion.

As to your idea for parsing output from sp_helptext, my opinion
is that the more you think about it, the less appealing it will
be.  As an old mainframer, I remind you that the blocking
factor of the output distorts the text.  So that scanning for a
certain value may fail when the value is truncated on one line
and continued on the next.

Tom McCready

202-707-1923



Fri, 10 Dec 1999 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. temporary tables in stored procedures/parameter views vs stored procedures

2. problems with stored procedure parameters

3. Passing a Table Name as a Stored Procedure Parameter

4. Stored procedure parameters

5. Can't Uase Expression in a Stored Procedure Parameter

6. stored procedure parameter

7. Stored Procedure Parameters

8. Lists in Stored Procedure Parameters

9. Stored Procedure Parameters

10. Stored Procedure Parameters

11. Stored Procedures Parameters

12. Stored Procedure Parameter not very efficient


 
Powered by phpBB® Forum Software