August 29, 2014 at 7:58 am
I know ran across this some time ago when researching some other topic, but I would like to add an option to our stored procedures (which have multiple parameters) so a user could do something like:
EXEC dbo.usp_MyStoredProcedure -Help or EXEC dbo.usp_MyStoredProcedure -?
The result would just list the parameters in the stored procedure and the options available for each parameter/what the parameter was use for.
Here is an example of one stored procedure in use:
CREATE PROCEDURE [dbo].[usp_MyStoredProcedure]
( @BCP INT = 0 ---- 0 [Default]: No BCP Import, 1: Insert TempBCP Data
, @Debug INT = 0 ---- 0 [Default]: Run process, 1: Create temp tables, run queries
, @StartDate smalldatetime = '1/1/1900' ---- Enter StartDate; [Default]: otherwise use Max of Invoice date
, @Date INT = 0 ---- 0 [Default]: Use std process (get last Saturday date from @StartDate), 1: Use @StartDate as is
)
WITH EXECUTE AS 'ADBAccount'
AS
BEGIN
....
END
Anyone have suggestions, recommendations or examples as to how to add "Stored Procedure Parameter Help" options?
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
August 29, 2014 at 8:33 am
sp_help[spname] already do that job. you have any specific reason to do that in the sp with an additional parameter.
August 29, 2014 at 8:53 am
Thanks for the reply. However, what I am looking for is running this:
EXEC dbo.usp_MyStoredProcedure -?
The result would just list the parameters in the stored procedure and acceptable input(s) for the procedure (sort of like the BOL documentation for procedures):
Procedure usp_MyStoredProcedure Parameters:
@BCP: 0 [Default]: No BCP Import, 1: Insert TempBCP Data
@Debug: 0 [Default]: Run process, 1: Create temp tables, run queries
@StartDate: 1/1/1900 [Default]: use Max of Invoice date; Enter StartDate as alternative
@Date: 0 [Default]: Use std process (get last Saturday date from @StartDate), 1: Use @StartDate as is
sp_help 'dbo.usp_MyStoredProcedure" lists the parameters, data type(s), parameter positions, etc. but now how the parameters are used.
Sorry I was not more specific.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply