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
Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns93DE279D4BA3Yazorman@127.0.0.1>...[color=blue]
> [posted and mailed, please reply in news]
>
> Rajesh Garg (raj_chins@rediffmail.com) writes:[color=green]
> > 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.[/color]
>
> You say that you are writing a stored procedure, but to me it sounds
> like you are working with a user-defined function, UDF.
>
> In a UDF you are fairly limited in what you can do, as you have noted
> from the error message. The gist is that you may not change the state
> of the database, and the function must be deterministic. That is, it
> must always return the same result give a certain a set of data in
> the database.
>
> What your real problem is, is a little unclear to me, but it sounds
> like you might get some hints from an article of mine. See
>
http://www.algonet.se/~sommar/share_data.html.[/color]
Use getdate function in a own Function 
