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) output

AS ....

 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 advance

James

0
JamesNZ
10/10/2006 10:55:41 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

16 Replies
1095 Views

Similar Articles

[PageSpeed] 29

I believe you want to use 'EXEC'
0
abadincrotch
10/10/2006 11:40:13 AM

abadincrotch:
I believe you want to use 'EXEC'

You should use System stored procedure sp_executesql, it will take care of the dependcy chain for.  Try the link below for details.

http://msdn2.microsoft.com/en-us/library/ms188001.aspx


Kind regards,
Gift Peddie
0
Caddre
10/10/2006 12:52:17 PM
Caddre:

abadincrotch:
I believe you want to use 'EXEC'

You should use System stored procedure sp_executesql, it will take care of the dependcy chain for.  Try the link below for details.

http://msdn2.microsoft.com/en-us/library/ms188001.aspx

 

you'll still need to EXECUTE (EXEC) sp_executesql to begin with.

0
abadincrotch
10/10/2006 1:37:30 PM
Exec is not the reason it works sp_executesql takes care of the dependecy chain because all stored procedures are recorded in the sysdepends table, if you run both directly in SQL Server it will give the error the second stored proc is not in sysdepends.  So the job of sp_executesql is to register the second stored proc with sysdepends in the Master database.
Kind regards,
Gift Peddie
0
Caddre
10/10/2006 1:50:11 PM

Caddre:
Exec is not the reason it works sp_executesql takes care of the dependecy chain because all stored procedures are recorded in the sysdepends table, if you run both directly in SQL Server it will give the error the second stored proc is not in sysdepends.  So the job of sp_executesql is to register the second stored proc with sysdepends in the Master database.

 

I'm not sure you're understanding his question or my responses to begin with -- he needs to know how to execute a stored procedure, from within another stored procedure. this is done using the EXEC TSQL statement.

0
abadincrotch
10/10/2006 2:07:54 PM

(I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.)

That error is because of the broken dependency chain and one of sp_executesql job is to provide the dependency chain.


Kind regards,
Gift Peddie
0
Caddre
10/10/2006 2:17:59 PM
never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.
0
abadincrotch
10/10/2006 6:16:31 PM

abadincrotch:
never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.

Exec works some times but not always but sp_executesql is the main way to do it, now read what Microsoft says about Exec.

http://msdn2.microsoft.com/en-us/library/ms188332.aspx


Kind regards,
Gift Peddie
0
Caddre
10/10/2006 6:29:56 PM
Caddre:

abadincrotch:
never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.

Exec works some times but not always but sp_executesql is the main way to do it, now read what Microsoft says about Exec.

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

 

rather than waste my time re-reading msdn/sqlbol, if you have a point to make there, please make it -- that entry is lengthy.

0
abadincrotch
10/10/2006 6:38:38 PM
abadincrotch:
Caddre:

abadincrotch:
never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.

Exec works some times but not always but sp_executesql is the main way to do it, now read what Microsoft says about Exec.

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

 

rather than waste my time re-reading msdn/sqlbol, if you have a point to make there, please make it -- that entry is lengthy.

 

and like I said, you still need to CALL or EXEC sp_executesql to begin with, so your point is ... ?

0
abadincrotch
10/10/2006 6:39:22 PM

(I'm not sure you're understanding his question or my responses to begin with -- he needs to know how to execute a stored procedure, from within another stored procedure. this is done using the EXEC TSQL statement.)

You said I don't understand what the user asked for when I gave the correct solution, when you gave something that works some times and not always so you have a point I don't.

The Exec works some times and not always sp_executesql works always.


Kind regards,
Gift Peddie
0
Caddre
10/10/2006 6:49:22 PM

Thanks for your help People,

It worked!! Smile

 James

0
JamesNZ
10/11/2006 9:52:35 AM
JamesNZ:

Thanks for your help People,

It worked!! Smile

 James

 

out of curiosity, which?

0
abadincrotch
10/11/2006 1:02:38 PM
Caddre:

The Exec works some times and not always sp_executesql works always.

 

somehow I fail to see where it says EXEC doesn't always "work."

 yes, the secureables need to have permissions granted to the role/login executing the statement ... that's just plain common sense ... where does it say exec doesn't always work???

0
abadincrotch
10/11/2006 1:31:07 PM
0
Caddre
10/11/2006 2:14:08 PM

Hi abadincrotch ,

I tried EXEC first.

James

0
JamesNZ
10/15/2006 10:10:27 PM
Reply:

Similar Artilces:

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...

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. Thanks, Aaron 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 INSERT #tempTable EXEC s...

calling a stored procedure from a stored procedure
Hi Group! 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 mytable;". This sproc works fine. Now I need to call this procedure from another procedure which has a variable result set. I tried the following: <snip... set ll_retval = 0; call sp_breakstring(as_bzp,','); BreakLoop: while ll_retval <> 100 and not ll_retval < 0 loop set li_zaehler = li_zaehler + 1; fetch next sp_breakstrin...

Call as stored procedure from another stored procedure
I am using a stored procedure (SP_A) to insert rows to a table. This is done by the cursor within the procedure. Value for one of the column is provided by SP_B. My problem is that when i call SP_B in SP_A it does not return any value (rather i do not know how to capture it from SP_A). If executed individually it returns a value. Please help with example if something specific needs to be taken care. Suresh wrote: > > Please help with example if something specific needs to be > taken care. Please always post the version and build number of ASA that you are using (e....

Calling a stored procedure from another stored procedure
Hello, I am now using Interbase again for a project and I am trying to do this: begin /* Procedure Text */ /* Steps: - 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 into :protid; /* protocol exists? Gooooood! */ if ( :protid is not null ) then begin execute addactivity(:userid,'New document') returning_values :actid; execute adddocument( :docname,:docpath...

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...

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: info@seventhday.se--------------------------------...

Call stored procedure from another stored procedure?
How do I call this stored procedure: CREATE Procedure hentSpecialPris ( @varenr int, @cvr int, @pris decimal(7,2) OUTPUT ) AS DECLARE @antal int SET @antal = 0 SET @antal = (SELECT COUNT(*) FROM rabat WHERE varenr=@varenr and cvr = @cvr) IF @antal < 1 BEGIN SET @pris = (SELECT pris FROM vare WHERE varenr=@varenr) END ELSE BEGIN SET @pris = (SELECT pris FROM rabat WHERE varenr=@varenr and cvr = @cvr) END GO from another stored procedure?? I want to get the output value of the stored procedure? ...

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 ? Please help. The custdb sample that shipped with ASA does this all the time. CREATE PROCEDURE ULResolveOrderConflict() BEGIN -- 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; END IF; ...

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@hotmail.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 ) BEGIN SELECT COUNT(*) FROM tbl_virtual_drive WHERE tbl_virtual_drive.FK_sites = FK_sites_COL; END 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...

SQL Stored Procedure Issue
This is the Stored Procedure below ->  SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO /****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO /****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/3/2007 4:50:23 PM ******/ /****** Object:  Stored Proc...

SQL Stored Procedure to MySQL Stored Procedure Conversion
Hi, I am trying to convert a stored procedure written for sql to one that will work in mysql. I understand that I have to set the variables as IN , but I don't know what to do with the rest of the code. The following is the sql stored procedure that I am trying to convert to msql stored procedure: CREATE PROCEDURE Register_User (@userName Varchar(50), (@PassWord Varchar(50), (@FirstName Varchar(50), (@LastName Varchar(50) ) AS IF EXISTS(SELECT u_ID FROM User_ID Where u_UserName=@UserName) RETURN -1 ELSE INSERT User_ID( u_User...

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...

Web resources about - Calling Stored Procedure fromanother Stored Procedure - asp.net.sql-datasource

An Ancient Inscription to, and Epigram on Apollonius of Tyana
This article has been extracted from: An Epigram on Apollonius of Tyana C. P. Jones The Journal of Hellenic Studies, Vol. 100, Centennary Issue. ...

A matter of degrees
Despite fears of expensive US-style degrees becoming the norm, Melbourne University believes its overhaul is the way of the future. -

Meteorology, by Aristotle : Book I
WE have already discussed the first causes of nature, and all natural motion, also the stars orderedin the motion of the heavens, and the physical ...

ParlInfo - `Office of profit under the crown' and membership of the Commonwealth Parliament
Search engine for Australian parliamentary documents and media.

Ontario Highway 403 History - The King's Highways of Ontario
King's Highway 403 is a major freeway route through Southern Ontario, connecting Mississauga to Woodstock, viaHamilton and Brantford. Highway ...

Algae Control in Large Reservoir - Client Comments
Algae control and treatment in large reservoir - new technology and products September 21, 2006 - After one month of using a liquid form fromanother ...


McGraw-Hill Tumbles Most Since ’87 as U.S. Prepares S&P Lawsuit
McGraw-Hill Cos. tumbled the most in 25 years as the parent of Standard & Poor’s said it expects to be sued by the U.S. over inflated mortgage-bond ...

Bloomberg Law - Document - United States v. Pleau, 680 F.3d 1 (1st Cir. 2012), Court Opinion
UNITED STATES of America, Appellee, v. Jason W. PLEAU, Defendant, Appellant.Lincoln D. Chafee, in his capacity as Governor of the State of Rhode ...

FCC: Connect America Is Connecting 400,000 Americans
Announces launch of phase one funding for broadband subsidy migration

Resources last updated: 1/14/2016 6:02:51 AM