UDF inside of stored procedure

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)

UDF inside of stored procedure

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-03-24 : 14:31:01

I am trying to create a sp which is using a UDF that returns the grodss_revene of previous month.

For example,
if I pass month = 2 and 2006
select FC, grossrevenue from revenue
where month = @month and year = @year

the result should return the gross revenue of 1, 2006

if I pass month = 1 and 2006
the result should return the gross revenue of 12, 2005.

How can I create a UDF that does this and put it into SP? can you show me some examples?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-24 : 15:38:23

Use this logic:

DECLARE @month int, @prev_month int, @year int, @prev_year int

SET @month = 1
SET @year = 2006

SET @prev_month = MONTH(DATEADD(month, @month -2, DATEADD(year, @year - 1900, 0)))
SET @prev_year = YEAR(DATEADD(month, @month -2, DATEADD(year, @year - 1900, 0)))

SELECT @prev_month
SELECT @prev_year

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-24 : 15:42:21

If you simply need to see examples of creating a udf and SP and how to call them:
(plenty of sample code in Books Online)


use pubs

------------------------------------------------------------------
--create your function
if object_id('dbo.fn_RevForMMMYY') > 0
	drop function dbo.fn_RevForMMMYY
go

create function dbo.fn_RevForMMMYY (@Month int, @year int)
returns money
as
begin
	declare @rev money

	--set your value based on the input parameters
	select @rev = 26.50

	return @rev
end
go
------------------------------------------------------------------
--create a stored procedure that uses the function above
if object_id('dbo.myProc') > 0
	drop procedure dbo.myProc
go

create procedure dbo.myProc
as

declare @rev money
set @rev = dbo.fn_RevForMMMYY(2,2006)

select @rev [revenueFromSP]

go
------------------------------------------------------------------
--call your SP

exec dbo.myProc

--or call the function directly:
select dbo.fn_RevForMMMYY(2,2006) [revenueFromUDF]

go

------------------------------------------------------------------
--clean up

if object_id('dbo.myProc') > 0
	drop procedure dbo.myProc
if object_id('dbo.fn_RevForMMMYY') > 0
	drop function dbo.fn_RevForMMMYY

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources