Calling a stored procedure inside another stored procedure (or "nested stored procedures")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to&n...how to call stored procedure from another stored procedure?
is it possible to call a stored procedure from another stored procedure?-keeara g------------------
Inside your first Stored Procedure:
exec <name of SP to run> <Eventual Parameters this SP requires>
Here is a link to all you want to know about executing SPs from other SPs. You can even Execute SPs on other SQL Servers.RegardsAndre Colbiornsen ---------------------------------Seventh DayRåbygatan 1A,SE-223 61 LundSwedenMob.: +46-(0)708-97 78 79Mail: firstname.lastname@example.org--------------------------------...Calling stored procedure from another stored procedure
Is it possible to call one sp from another sp?I've been hunting around for an example to do this and just can't seem to find one.Anyone have a link for this or a sample?Thanks all,Zath
Yes, you can. Just use EXEC usp_secondStoredProc @params inside your first SP.Nick...Calling a stored procedure from another stored procedure
I am now using Interbase again for a project and I am trying to do this:
/* Procedure Text */
- Verify if protocol exists and fetch protocol id
- add activity
- add document
- add activity protocol
reeturn document id
select id from protocols where protocols.protname = :protocol
/* protocol exists? Gooooood! */
if ( :protid is not null ) then
execute addactivity(:userid,'New document') returning_values :actid;
execute adddocument( :docname,:docpath...Call stored procedure from another stored procedure?
How do I call this stored procedure:
CREATE Procedure hentSpecialPris
@pris decimal(7,2) OUTPUT
DECLARE @antal int
SET @antal = 0
SET @antal = (SELECT COUNT(*) FROM rabat WHERE varenr=@varenr and cvr = @cvr)
IF @antal < 1
SET @pris = (SELECT pris FROM vare WHERE varenr=@varenr)
SET @pris = (SELECT pris FROM rabat WHERE varenr=@varenr and cvr = @cvr)
from another stored procedure?? I want to get the output value of the stored procedure?
...calling another stored procedure within a stored procedure?
Hi, I have a while loop stored procedure, I need to send email for each item in the loop using a sendemail stored procedure. I have two question ..
1) I use EXEC PRODUCTION.DBO.SENDMAIL 'email@example.com', 'Start', 'Job Start' in side the while loop stored procedure, but i didn't get any email or error msg. Why?
2) I try to move a file .. how do I find out if the moving is completed successfully?
-- MOVE FILES SET @CMD = 'MOVE /Y ' + '"' + @ORIGINAL_FILE + '"' + ' "' + @MOVE_FILE + '"' EXEC master.dbo.xp_cmdshell @CMD
Thanks for your reply in advance.
1. I hav...calling a store procedure inside another stored procedure
CREATE PROCEDURE `sp_test`.`proc_tot_record`(FK_sites_COL INT )
SELECT COUNT(*) FROM tbl_virtual_drive WHERE tbl_virtual_drive.FK_sites = FK_sites_COL;
CREATE PROCEDURE `test`.`paging_with_param`(FK_Sites_COL INT, Start_Limit_COL INT, End_Limit_COL INT)
BEGINPREPARE STMT FROM "SELECTtbl_virtual_drive.PK_virtual_drive,tbl_virtual_drive.file_name,tbl_virtual_drive.file_extension,tbl_virtual_drive.FK_sitesFROMtbl_virtual_driveWHEREtbl_virtual_drive.FK_sites = ?ORDER BYtbl_virtual_drive.file_nameLIMIT ?,?";EXECUTE STMT USING @FK_Sites, @Start_Limit, @End_Limit...Is there a utility to see which stored procedures call another stored procedure?
I'm looking for a dependency diagram / pretty print utility for stored
procedures / functions in SQL Anywhere.
Thank You in advance
I don't know of any pretty print utility, but the built in function
traceback may be of help to you.
International and Sustaining Engineering
Adaptive Server Anywhere
Chris McCormack wrote:
> I'm looking for a dependency diagram / pretty print utility for stored
> procedures / functions in SQL Anywhere.
> Thank You in advance
...call one stored procedure from inside another stored procedure
How do you call one stored procedure from inside another stored procedure and return a value?Does this hinder performance?Which is better, do it as above, or have a .NET method calling the stored procedure A, getting the value it returns and then passing it to stored procedure B?
You can use "EXEC your stored procedure name" in a stored procedure to call another stored procedure and this way will be faster than call A and then B from web app.This posting is provided "AS IS" with no warranties, and confers no rights.
Cheers JimmyM, thanks for that.
How can I pass parameters from o...calling a stored procedure from a stored procedure
I'm on ASA 8.02 Build 4411 writing sprocs in WATCOM SQL.
I have a sproc which breaks a given string-parameter into pieces. It's
result set contains the pieces, e.g. last line of sproc: "select item from
This sproc works fine.
Now I need to call this procedure from another procedure which has a
variable result set.
I tried the following:
set ll_retval = 0;
while ll_retval <> 100 and not ll_retval < 0 loop
set li_zaehler = li_zaehler + 1;
fetch next sp_breakstrin...Calling a Stored Procedure from a Stored Procedure
Can you do this? I have a sp that returns a list of eligible accounts and then I use that list in other sp but I don't want to keep copying the code, I would like to be able to use it as a sub query in other sp but I don't know if this is possible or what the syntax would be.
You cannot do that directly. You can:
1. Use User defined functions which can return a resultset
2. Use a local #temp table which can hold the resultset of
the called stored procedure and use this #temp table
in your subsequent processing
EXEC s...Re: How a stored procedure call another stored procedure and then process the results of latter
How a stored procedure can call another stored procedure and process
the result set returned from the second stored procedure in Sybase ?
This is typically done by having the first stored procedure create a
that the second stored procedure populates before it returns.
> How a stored procedure can call another stored procedure and process
> the result set returned from the second stored procedure in Sybase ?
As you said, first stored procedure create a temporar...Calling Stored Procedure fromanother Stored Procedure
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
I believe you want to use 'EXEC'
abad...Create a Stored procedure and call that stored procedure in Script
How can i create a stored procedure in the database and call that in my
Sychronization Script ?
Is there a way to call the stored procedure that is located in Consolidated
Script and Remote database seperately ?
The custdb sample that shipped with ASA does this all the time.
CREATE PROCEDURE ULResolveOrderConflict()
-- approval overrides denial
IF 'Approved' = (SELECT status FROM ULNewOrder) THEN
UPDATE ULOrder o SET o.status = n.status, o.notes = n.notes
FROM ULNewOrder n WHERE o.order_id = n.order_id;