getDate() in exec SProc syntax

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

getDate() in exec SProc syntax

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-10-12 : 10:26:10

What is the right way to put a getdate() function in the exec SProcABC syntax in QA?

I have the SProcABC as
CREATE PROCEDURE [SProcABC ]
@EnteredDate as datetime
as
select...go

And Exec SProcABC '2005-10-12' returns the right data, but none of following worked.

Exec SProcABC GetDate()
Exec SProcABC 'GetDate()'
Exec SProcABC '"'GetDate()'"'
Exec SProcABC '"'+GetDate()+'"'

What am I missing here?

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-12 : 10:27:39

Try this

Declare @date DateTime
set @date=GetDate()
Exec SProcABC @Date

Madhivanan

Failing to plan is Planning to fail

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-10-12 : 10:32:08

Aha, it worked!
So GetDate is not "self declared" here?

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-12 : 10:33:28

Yes. You need to use DateTime Variable for that

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-12 : 12:21:44

Functions (internal or user defined) are not allowed as input parameters for stored procedures.

You must first save the function data into a local variable, and use that for the stored procedure parameter.

quote:
Originally posted by Hommer

Aha, it worked!
So GetDate is not "self declared" here?

Thanks!

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-12 : 12:49:47

I'm looking, but is it because it's non deterministic and the optimizer won't know what to do with it?

Hmmmmm.....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-12 : 16:54:55

If you look at this test result, I don't think non deterministic is the issue. It appears that a stored procedure will only take a variable or a hard coded value, and not result of a function or a calculation.

create proc P_MY_PROC
( @MY_PARAM int = null )
as
select [x] = @MY_PARAM
go
exec P_MY_PROC @MY_PARAM = 3
go
print 'Bad 1'
go
exec P_MY_PROC @MY_PARAM = convert(int,'2')
go
print 'Bad 2'
go
exec P_MY_PROC @MY_PARAM = 2+3
go
drop proc P_MY_PROC


x           
----------- 
3

(1 row(s) affected)

Bad 1
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'convert'.
Bad 2
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '+'.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources