Using a SP within T-SQL

Last Post 24 Apr 2008 02:02 PM by Jack_Kaye. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Jack_Kaye
New Member
New Member

--
24 Apr 2008 01:30 PM
I have the need to create a Currency Conversion SP that I want to call multiple times within T-SQL to pull out the conversion rate.

Here is the SP that I created:

create procedure spNavCur
@StartDate varchar(10),
@CurType varchar(3)
as
select
top 1 [Exchange Rate Amount]
from
NAVLIVE.dbo.[Blanchard US$Currency Exchange Rate]
where
[Currency Code] = @CurType
and
(
[Starting Date] >= @StartDate
and
month(@StartDate) = month([Starting Date])
)
order by [Starting Date] DESC

Now within my T-SQL code I want to use the conversion rate that this SP returns.

'Seminars and Consulting' =
(
case
when nav.[G_L Account No_] in('5100','5110') then
case
when nav.Amount is null then 0
else nav.Amount*-1/spNavCur [Posting Date], 'CAN'
end
else 0
end
),

I receive the following error message "Incorrect syntax near 'Posting Date'.". Is there a special method for calling the SP when it is within code and not on it's own line?
SwePeso
New Member
New Member

--
24 Apr 2008 01:50 PM
Make the first SP as a function instead?
Jack_Kaye
New Member
New Member

--
24 Apr 2008 02:02 PM
Thank you...it did not work at first but once I placed a dbo.fnNavCur I was able to get it to work correctly.
SQLUSA
New Member
New Member

--
24 Apr 2008 11:30 PM
>dbo.fnNavCur

Nobody ever figured out why you have to prefix it with "dbo." Theories are abound though.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/order2005grandslam
You are not authorized to post a reply.

Acceptable Use Policy