I have a C# WinForms
application that manages stored procedures used by different services. What the users see is something like that:
exec stored_procedure_name param1, param2, param3
And since param1 doesn't mean anything to them (they can't see the stored procedure), I would like to present to them small descriptions of the parameters which are normally saved in the header of the stored procedure.
A typical stored procedure would like:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stored_procedure_name]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[stored_procedure_name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*************************************************************************************
[Procedure Info]
Author = myName
Description = this stored procedure returns results.
**************************************************************************************
[Parameters Info]
@param1 = this is parameter one
@param2 = this is parameter two
@param3 = this is parameter three
**************************************************************************************
[Changes]
2015-06-17 The stored procedure is optimized.
*/
CREATE PROCEDURE [dbo].[stored_procedure_name]
@param1 int,
@param2 nvarchar(20),
@param3 nvarchar(10)
AS
BEGIN
-- SP code here
END
GO
From the above, I want to get the descriptions in the Parameters info. I know that I can use the following SQL to retrieve the stored procedure as text/table:
CREATE TABLE #tmpHeader
(
TEXT NVARCHAR(1000)
)
INSERT INTO #tmpHeader
EXEC sp_helptext 'stored_procedure_name';
SELECT * FROM #tmpHeader
DROP TABLE #tmpHeader
Any ideas or suggestions how to proceed from here in order to get the content of the parameters info? I am also open to any other suggestions.