How to use a UDF function in Stored Proc

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

General SQL Server Forums

New to SQL Server Programming

How to use a UDF function in Stored Proc

Author

Topic

tvb2727
Starting Member

35 Posts

Posted - 2012-03-11 : 01:22:52

How Can I use a udf function in my store procedure. I ran the one below and it completed successfully. Then when I try to use it in my Stored procedure it shows the following error:

Msg 195, Level 15, State 10, Procedure sp_set_sch_grid_2, Line 17
'main_dow' is not a recognized built-in function name.

GO
if object_id('main_dow') is not null
drop function main_dow
GO

CREATE FUNCTION main_dow(@dtDate DATETIME)
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
WHEN 1 THEN 'Sunday, '
WHEN 2 THEN 'Monday, '
WHEN 3 THEN 'Tuesday, '
WHEN 4 THEN 'Wednesday, '
WHEN 5 THEN 'Thursday, '
WHEN 6 THEN 'Friday, '
WHEN 7 THEN 'Saturday, '
END
RETURN (@rtDayofWeek)
END
GO

When I try to call it:

CAST(main_dow(START_DATE) AS main_dow) + ' ' + CAST(START_DATE AS nvarchar) AS weekday_date

I'm trying to show the [day of week, Date]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-11 : 03:38:21

[code]
CAST(dbo.main_dow(START_DATE) AS main_dow) + ' ' + CAST(START_DATE AS nvarchar) AS weekday_date
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-11 : 11:33:43

why do you need a UDF for this? Unless its for illustration purpose, this is absolutely not need.
You can simply do

DATENAME(dw,START_DATE) instead to get same result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources