Hi,
I have written a stored proc with some temporary tables and also used
a getdate() in my stored proc. When i try to call the sproc the error
is that we can only use extended sprocs or function inside a sproc.
Now if try to write the stored proc directly inside a fuction ie copy
paste after changing my temp tables to tables the problem is , i get a
error invalid use of getdate in sproc.What do i do to get something
for my results inside a table.
Thanks in advance.

Care to post your code here? If you are using GetDate() as a default value
for an input parameter you may see an error similar to the one you
mentioned. BTW, are you using an SP or UDF?

--
- Anith
( Please reply to newsgroups only )

My requirement is:

I want the final result in some table coz i can create only a view so
as to use the results of the sproc.

drop procedure Sproc_esqlReconcileWithBiz_WMS
go
create Procedure Sproc_esqlReconcileWithBiz_WMS
@InputDateStr as datetime
,@InputInterfaceDb as varchar(32)
,@InputInterfaceType as varchar(10)
as
begin
set nocount on

Declare @InputDate as numeric
Declare @lQryStr as nvarchar(2000)

DECLARE @lTimeZoneAdjustment AS BIGINT
SET @lTimeZoneAdjustment = (SELECT DateDiff(SECOND, DateAdd(SECOND,
0, '01/01/1970'), GETDATE()) - DateDiff(SECOND, DateAdd(SECOND, 0,
'01/01/1970'), GETUTCDATE()))

Set @InputDate = DateDiff(SECOND, DateAdd(SECOND, 0,
'01/01/1970'), cast(@InputDateStr as varchar(20)) ) -
@lTimeZoneAdjustment

Create Table #XIA (DEST varchar(15), BSQ varchar(32), MSG
varchar(2050) )

IF @InputInterfaceType = 'CMS'
Set @lQryStr = 'SOMETHING'
IF @InputInterfaceType = 'BMS'
Set @lQryStr = 'SOMETHING'

exec sp_executesql @lQryStr

UPDATE #XIA SET BSQ = Left(BSQ,3) + substring(BSQ, 5, (Select
case when charindex(char(13), BSQ)<=0 then 4 else charindex(char(13),
BSQ) - 5 end) ) where dest = '-'

Create Table #MismatchTrades ( Destination varchar(15),
BranchSeqNo varchar(32), TotTradesInOrs numeric, TotTradesInBiz
numeric )

Insert into #MismatchTrades
Select XIATrades.Dest AS 'ExecutionDestination',
XIATrades.ExRefNumber as 'Exch Ref #', XIATrades.Cnt as 'XIA Trade
Count', BIZTrades.cnt as 'BIZ Trade Count'
from
(
SOMETHING
)

Select xia.Dest as 'ExecutionDestination', xia.MSG,
missed.BranchSeqNo as 'Exch Ref #', missed.TotTradesInOrs as 'No of
Trades in XIA', missed.TotTradesInBiz as 'No of Trades in BIZ'
from #XIA xia JOIN #MismatchTrades missed
ON xia.BSQ = missed.BranchSeqNo AND xia.Dest =
missed.Destination
order by xia.BSQ

end


The final select statement results i want to get in a function. How do
i get it then.
Thanks for al the help.I will try to follow the link u have sent me.
RVG
[quoted text, click to view]