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

I believe you want to use 'EXEC'|||

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

|||

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.

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

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.

|||

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

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

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

|||

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.

|||

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

|||

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

|||

Thanks for your help People,

It worked!!

James

|||

JamesNZ:

Thanks for your help People,

It worked!!

James

out of curiosity, which?

|||

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?