spid or session id for current processing running a stored procedure

I am trying to limit only one stored procedure can be ran at any given time in SQL server 2005 or 2008. I am thinking if I can track the spid that is running that sp, I should be able to accomplish that. First, I like to know if I can find out what sp is current running and what spid they are using. Secondly, if there is another way to limit one sp running at any given time, please let me know as well.
wingman

Hi
You can detect which spid is running which stored procedure. My question would be why would you?
Its like force only one user at time to be able to use SQL.
Can you give us more information why you need this.
Regards
Richard....

We have a sp that is resource intensive and we won't want anyone to run that sp more than one at any given time. We are controlling this with our web application so if the second user tries to run that sp, the web application will queue it up until the first one is done.
wingman

You could use the old trick with a global temp table ...
--at the start of your sp ...
DECLARE @oid INT
SELECT @oid = object_id('tempdb..##tokenforsp')
IF @oid IS NULL
create table ##tokenforsp (token int)
ELSE
RETURN
--body of your sp ...
DROP TABLE ##tokenforsp
--end of sp

Is there another way without modifying existing sp? Can I query the dynamic view for it?

Yes, that sp has been optimized and statistics are updated every day. We are not trying to avoid other processes. We are trying to have some control on one speical sp. We like to detect and queue that speical sp from our web application if there is the same sp is currently running already. Is there a way?

Sorry, it does have some confidential info there and it would take me time to recreate it anmiously. Indexing and execution plan look good as well.

In BOL, there is no such sp_setapplock. Where can I find some documentation about this sp?
All I want to know is that if there is a way for me to know if one particular stored procedure is currently running in SQL server at the time of interest.

Application locks (as suggested by Frank) simply lock a part of T-SQL code, and not locking data, so no two concurrent users can run the same code in the same time. There are 3 stored procedure that are used for this:

1. sp_getapplock : Places a lock on an application resource.
2. sp_releaseapplock: Releases a lock on an application resource.
3. APPLOCK_TEST: Returns information about application lock

you can find an example in BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e43d4917-77f1-45cc-b231-68ba7fee3385.htm