Get Parameter Name

Question

Get Parameter Name

Shakti Singh Dulawat

SSC-Addicted

Points: 483
More actions
December 14, 2007 at 7:15 am

#377555

Hi all
How to get the parameter name of the stored procedure in the table.
Thanks
Warm Regards,
Shakti Singh Dulawat
Before printing, think about the environment
Do the impossible, and go home early.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

Not sure what you mean about the stored procedure in the table.

You don't need the name of the stored procedure to call it, you can send in parameters in order. I thought there was an ADO method that would return them.

Shakti Singh Dulawat SSC-Addicted Points: 483 More actions · Answer 2

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

Warm Regards,
Shakti Singh Dulawat
Before printing, think about the environment
Do the impossible, and go home early.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE Specific_Name = 'Award_Category_Insert'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shakti Singh Dulawat SSC-Addicted Points: 483 More actions · Answer 4

Thanks , I got another way so I am posting it here.

I got the solution

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

Warm Regards,
Shakti Singh Dulawat
Before printing, think about the environment
Do the impossible, and go home early.

karthik M One Orange Chip Points: 29139 More actions · Answer 5

and you also get it from the syscomments table.

select text

from syscomments

where id = object_id('procedurename')

karthik

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

This will retrieve a bit more info about SP parameters

select 'SP name' = o.name, ParamName= c.name, DataType = t.name,

c.Length, Direction = case c.isoutparam

when 0 then 'adParamInput'

when 1 then 'adParamInputOutput'

End ,

c.colorder

from syscolumns c, sysobjects o, systypes t

where c.[id] = o.id and t.xusertype = c.xusertype and o.type = 'P' and o.status >= 0

order by o.name, colorder

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

bhuvan.ram Newbie Points: 6 More actions · Answer 7

Thanks JEFF..Its very Informative..thanks a lot about "retrieving all the parameter names from the procedure

bhuvan.ram Newbie Points: 6 More actions · Answer 8

Jeff Moden (12/14/2007)
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE Specific_Name = 'Award_Category_Insert'

Excellent...thanks alot

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

bhuvan.ram (12/1/2010)
Jeff Moden (12/14/2007)
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE Specific_Name = 'Award_Category_Insert'
Excellent...thanks alot

Thanks for the feedback. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)