Thursday, August 20, 2009

Call a stores procedure inside antother store procedure

If you only want to perform some specific operations by your second SP and do not require values back from the SP then simply do:

Exec secondSPName @anyparams

Else, if you need values returned by your second SP inside your first one, then create a temporary table variable with equal numbers of columns and with same definition of column return by second SP. Then you can get these values in first SP as:

Insert into @tep_table
Exec secondSPName @anyparams

To pass parameter to second sp, do this:

Declare @id ID_Column_datatype
Set @id=(Select id from table_1 Where yourconditions)

Exec secondSPName @id

Suppose your second sp returns Id and Name where type of id is int and name is of varchar(64) type.

now, if you want to select these values in first sp then create a temporary table variable and insert values into it:

Declare @tep_table table
Id int,
Name varchar(64)
Insert into @tep_table
Exec secondSP

Select * From @tep_table

This will return you the values returned by second SP.


Door tak aaknho me Neendo ke silsile bhi nahi
Shikaste khwabo ke ab mujh me honsle bhi nahi

Nahi Nahi ye khaber dushmano ne di hogi
Voh aaye aaker chale bhi gaye aur mile bhi nahi

Abhi se mere rafugar ke hath thakne lage
Abhi to zakhm mere zigar ke sile bhi nahi

Ye kaun log andheryo ki baat kerte hain
Abhi to chand teri yaad ke dhale bhi nahi

No comments: