Use getdate function in a own Function

[Rajesh Garg]

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.
RVG

[Anith Sen]

Re: Use getdate function in a own Function

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 )

[Erland Sommarskog]

Re: Use getdate function in a own Function

[posted and mailed, please reply in news]

Rajesh Garg (raj_chins@redi ffmail.com) writes:[color=blue]
> 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.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Rajesh Garg]

Re: Use getdate function in a own Function

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_esqlRecon cileWithBiz_WMS
go
create Procedure Sproc_esqlRecon cileWithBiz_WMS
@InputDateStr as datetime
,@InputInterfac eDb as varchar(32)
,@InputInterfac eType as varchar(10)
as
begin
set nocount on

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

DECLARE @lTimeZoneAdjus tment AS BIGINT
SET @lTimeZoneAdjus tment = (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(@InputDate Str as varchar(20)) ) -
@lTimeZoneAdjus tment

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

IF @InputInterface Type = 'CMS'
Set @lQryStr = 'SOMETHING'
IF @InputInterface Type = '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 'ExecutionDesti nation',
XIATrades.ExRef Number as 'Exch Ref #', XIATrades.Cnt as 'XIA Trade
Count', BIZTrades.cnt as 'BIZ Trade Count'
from
(
SOMETHING
)

Select xia.Dest as 'ExecutionDesti nation', xia.MSG,
missed.BranchSe qNo as 'Exch Ref #', missed.TotTrade sInOrs as 'No of
Trades in XIA', missed.TotTrade sInBiz as 'No of Trades in BIZ'
from #XIA xia JOIN #MismatchTrades missed
ON xia.BSQ = missed.BranchSe qNo AND xia.Dest =
missed.Destinat ion
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:<Xns93DE27 9D4BA3Yazorman@ 127.0.0.1>...[color=blue]
> [posted and mailed, please reply in news]
>
> Rajesh Garg (raj_chins@redi ffmail.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]

[Erland Sommarskog]

Re: Use getdate function in a own Function

Rajesh Garg (raj_chins@redi ffmail.com) writes:[color=blue]
> I want the final result in some table coz i can create only a view so
> as to use the results of the sproc.[/color]

I'm afraid that this a lost battle. You make references to getdate()
and getdate(), and while the result is deterministic in some sense, SQL
Server does not realize this. You could fix this by passing the time-zone
adjustment as a parameter, but then you also invoke dynamic SQL, which
you cannot do in a function.

The best I could think if is to save the result of the SP in a
table, but then you would need some refresh command.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Rajesh Garg]

Re: Use getdate function in a own Function

I had to look do a complete turnaround to find a possible way.
Thanks for your help anyways
Cheers
RVG

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns93DEEB 71AAC71Yazorman @127.0.0.1>...[color=blue]
> Rajesh Garg (raj_chins@redi ffmail.com) writes:[color=green]
> > I want the final result in some table coz i can create only a view so
> > as to use the results of the sproc.[/color]
>
> I'm afraid that this a lost battle. You make references to getdate()
> and getdate(), and while the result is deterministic in some sense, SQL
> Server does not realize this. You could fix this by passing the time-zone
> adjustment as a parameter, but then you also invoke dynamic SQL, which
> you cannot do in a function.
>
> The best I could think if is to save the result of the SP in a
> table, but then you would need some refresh command.[/color]