[omniman]

I have a situation where I want to call a stored procedure (from another stored procedure) depending on a value in the first procedure. My first SP is:

CREATE PROCEDURE [dbo].[getAdminPending] AS
 
declare @rPrice numeric 
SELECT     search_tracker.file_number, client.client_name, client_pricing.price, search_tracker.search_id,
case search_tracker.recording when 'Y' then exec @rPrice = getRecordingPrice search_tracker.client_id, search_tracker.state else 'NO' end AS recording_price
FROM         search_tracker INNER JOIN
                      client ON client.client_id = search_tracker.client_id INNER JOIN
                      client_pricing ON client_pricing.order_type_id = search_tracker.order_type_id AND client_pricing.state = search_tracker.state AND 
                      client_pricing.client_id = search_tracker.client_id
WHERE     (search_tracker.status_id = 2) OR
                      (search_tracker.status_id = 4)
ORDER BY client.client_name
GO 

getRecordingPrice stored procedure looks like:

CREATE PROCEDURE [dbo].[getRecordingPrice] 
    @clientID int,
    @clientState varchar(2)
AS
declare @recordingAmt numeric
SELECT price 
FROM client_pricing
WHERE client_pricing.client_id = @clientID AND client_pricing.state = @clientState AND client_pricing.order_type_id = 7
RETURN @recordingAmt
GO 

I get the following errors:
---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 156: Incorrect syntax near the keyword 'exec'.
Line 5: Incorrect syntax near '.'.
---------------------------

Any help would be great.

[mattyblah]

use a user defined function instead of stored procedure.

[omniman]

Worked great; thanks.