[RESOLVED] Get Parameter Name

[shakti5385]

Hi all
How to get the parameter name of the stored procedure in the table.
Thanks

[techgnome]

table or database? Tables don't have stored procedures....
That aside.... it depends on the Database you are using (SQL Server - if so, which one; Access; Oracle, etc.) And it also depends on HOW you want to do it: VB6, VB.NET, ADO, SQL, ADO.NEt, SQLDMO, SMO.....

-tg

[shakti5385]

ALTER PROCEDURE [dbo].[Award_Category_Insert] 
-- VARIABLE DECALARTION--
@Award_Category_Name as nvarchar(100)
	
AS
BEGIN
	SET NOCOUNT ON
-- AWARD CATEGORY WILL BE INSERT--
	Insert into Award_Category_Master(Award_Category_Name, Is_Deleted ) Values (@Award_Category_Name,0)
END

Suppose we have about SP name is Award_Category_Insert and it has 1 parameter i.e. @Award_Category_Name

I Just want to pass the SP name in another SP and it will provide me the list of all it's parameter.

Thanks

[techgnome]

sp_help or sp_helptext

the first one will give the signature of the stored proc, the second will give you the actual stored proc itself.

usage:

sp_help Award_Category_Insert
sp_helptext Award_Category_Insert

-tg

[brucevde]

Starting with version 7.0, sql server included several built in views that can be used to query the database schema. The following requires at least sql 2000 however.

Select *
From information_schema.parameters
where specific_name = 'Award_Category_Insert'

You could also use the sp_sproc_columns system stored procedure

sp_sproc_columns 'Award_Category_Insert'

[shakti5385]

Thanks techgnome

[szlamany]

We use this stored procedure to return a list of parameters and other info from any other stored procedure

CREATE PROCEDURE GetSqlSP
	 @StoredProc varchar(50)
AS
Select Parameter_Name,Data_Type,Parameter_Mode,Character_Maximum_Length
From INFORMATION_SCHEMA.Parameters
Where SPECIFIC_NAME = @StoredProc 
Order by Ordinal_Position