Call Store Procedure Within a VIEW? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Call Store Procedure Within a VIEW?

Author

Topic

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2006-01-03 : 22:15:58

1. How can I call a store procedure withing a view?
2. how can I retrieve the current date or time inside the "User Defined Function" within SQL Server? I tried GETDATE(), but it is not a recognize function.

Pls help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-03 : 22:31:44

1. you can't
2. also can't. However you can pass in the current datetime to your function

-----------------
[KH]
2006 a new beginning

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 00:08:29

Yes, if you use Stored Procedure instead of View

Madhivanan

Failing to plan is Planning to fail

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2006-01-04 : 00:12:26

You cannot use a system defined function or stored procedure inside a function.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-04 : 11:15:56

You can't use GETDATE() in a function, but...

This is a trick to work around not being able to use GETDATE() in a User Defined Function by putting GETDATE() in a view and using the view in the function.

This code shows how it works:


create view V_GETDATE
as
select
	[GETDATE] = getdate()
go
create function USE_GETDATE ()
returns datetime
as
begin
declare @dt datetime
select @dt = [getdate] from V_GETDATE
return @dt
end
go
select [GETDATE IN FUNCTIION] = dbo.USE_GETDATE()
go
drop function USE_GETDATE
go
drop view V_GETDATE

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-04 : 19:31:39

Nice trick MVJ

-----------------
[KH]
2006 a new beginning

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-05 : 11:07:47

Yeah, very ingenious

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources