Regarding StoredProcedure in MsSqlserver 2005

Yes. You can use a stored procedure inside another stored procedure.
Check this link.

http://www.aspfree.com/c/a/ASP.NET-Code/Call-Stored-procedure-from-within-another-stored-procedure-return-values/[^]

OP: plz Dont think wrong i cant Rediret to ur link some secutity issues.So can u send me an Example.

This quick how-to shows an example of having two stored procedures that one stored procedure executes the other stored procedure passing values and returning a value to the calling parent stored proc. This would be handy passing in a value to dynamically populate a lookup table and stored that Id as a foreign key in a datatable. . This example asssumes you would have a parent table and a child/lookup table related back to the parent table.

--This stored procedure is called by a business component or webpage 
--passing into two parameters, executes sp_ChildStoredProc and passes on value. 
--The @Id OUTPUT returns the value.  

CREATE PROCEDURE sp_ParentStoredProc
(
@col1 varchar(20)
@ValuePassed varchar(50),
)
AS

declare @Id int
EXEC sp_ChildStoredProc @ValuePassed, @Id OUTPUT

INSERT INTO SomeTable
(col1, col2)
VALUES
(@col1, @Id)
RETURN
GO 

This stored procedure is executed in the sp_ParentStoredProc above and passes'a varchar variable piece of data. The spChildStoredProc will insert this piece of data'into a lookup table if it doesn't exist, return the Identity column using Scope_Identity() function or'will return the identity value to the parent stored proc if the piece of data passed in already exists in the lookup table.

CREATE PROCEDURE sp_ChildStoredProc

(
@ValuePassedIn varchar(50),
@Id smallint OUTPUT
)
 AS

If exists (SELECT column1 FROM LookUpTable WHERE column1=@ValuePassedIn)
BEGIN
SET @Id = (SELECT column1 
   FROM LookUpTable 
   WHERE column1=@ValuePassedIn)
END 
ELSE
BEGIN
INSERT INTO LookUpTable(column1) 
VALUES(@ValuePassedIn)
    
    SET @Id = Scope_Identity()
END
GO

Yes: you can create Stroed Procedures within other stored procedures, and execute them as well. There is some info here: How to Share Data Between Stored Procedures[^]

Be aware that is may not speed anything up to create procedures inside other procedures - there will be a creation overhead within the server which you may not have accounted for!

You can nest[^] stored procedure calls - that is, call one from another upto 32 levels.

However, there is no need to be able to create one stored procedure inside another so that requirement does not make much sense.