SQL Server Performance Forum – Threads Archive
Calling stored procedure from another stored procHi, I want to know how to call a stored proc inside a stored proc. I am working with Sql Server 2005.
Here is the situation
i have a table accounts for which i have written a stored proc which takes accountid as input parameter and the output would be all records with that accountid.
something like this
accoutid name car
——– —- —–
12 Zrro honda
12 Bran mercedes then i have a stored proc which takes the cars as input parameter and out puts its details something like this
car id servicedate
— — ————
honda 2 12/12/2007 so I want to call this second proc inside the first proc, I want to use the ouput each row in the first proc and pass it as a parameter in to second proc and get only the servicedate and the outputshould be something like this
accoutid name car servicedate
——– —- —– ————
12 Zrro honda 12/12/2007
I cannot rewrite the first proc to get everthing in the procedure because both are procedures are too complicated(I have just given a example here) Any suggestions?
I’d suggest creating a temp table to temporarily hold the data for each stored procedure and then join on your car. However there might be some relational problems there. Usually you want to join from and id to an id, not a name of something to another name of something. For example: Create #tmpTbl1(
car varchar(50)) Insert into #tmpTbl1
exec(sp_storedProcedure1) Create #tmpTbl2(
servicedate datetime) Insert into #tmpTbl2
exec(sp_storedProcedure2) select tbl2.accountid,tbl1.name,tbl1.car,tbl2.servicedate
from #tmpTbl1 tbl1
inner join #tmpTbl2 tbl2 on tbl1.car = tbl2.car
I am trying to use the above , but i want to know how to pass the variables to the stored proc.
exec (‘sp_StoredProcedure1’) this takes @AccountId as parameter
how to pass it
any answers???[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]
Ohh, in that case… I’d probably go with a Cursor then. I have a procedure I’ve done that is very much similar to this. Use the one stored procedure that does not take parameter for the other to build the cursor. So again, you could create a temp table. Execute the contents of that stored procedure into it. Then do the cursor on it. INSERT INTO #tmpTable
EXEC(sp_standAloneStoredProcedure) DECLARE @accountid int DECLARE MyCursor CURSOR FOR
SELECT accountid from #tmpTable OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @accountid WHILE @@FETCH_STATUS = 0
BEGIN EXEC sp_StoredProcedure @accountid FETCH NEXT FROM MyCursor INTO @accountid
END CLOSE MyCursor
You may not even need to create a temp table to build the cursor. You might be able to do it straight from a stored procedure. I’ve never actually tried that. The temp table would definitely work though.
I find this construct dangerous. Remember, Cursors are evil, and must die <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />No, seriously, I think you would want to look at incorporating your functionality in an other way. The calling of sp’s were really not meant for this, hence, it is difficult to do in t-sql. Perhaps take the code that is contained in the original SP, and make a new one, that will contain your accountID in a join instead.<br /><br />regs,<br /><br />Eventloop<br /><br /><br />"Follow the join tree" – Dan Tow
Sorry if none of the regular posters has responded before now, but anyway … Seeing that you are writing code in this format … exec (‘sp_StoredProcedure1’) … I would assume that you are not entirely comfortable with T-SQL syntax. It also seems like you are thinking as a procedural programmer, and do not have a clear idea about query syntax. The concept you seem to be looking for is a JOIN, which is where you select columns from more than one table. Instead of having just your first table in the FROM clause of the query, you have two which you "join" on the referring columns: SELECT account.AccountId, account.Name, account.Car, MAX(car.ServiceDate)
FROM account INNER JOIN car ON account.CarId = car.CarId
GROUP BY account.AccountId, account.Name, account.Car
ORDER BY account.AccountId, account.Car