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 <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...
[posted and mailed, please reply in news]
Rajesh Garg (ra*******@rediffmail.com) writes: 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.
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.
