?
Solved

ms sql server 2005 - issues with creating a UDF

Posted on 2011-02-26
4
Medium Priority
?
282 Views
Last Modified: 2012-05-11
Wow..Ive spent hours trying to debug this and get make heads and tales...
USE [dfgsdf]
GO
/****** Object:  UserDefinedFunction [dbo].[InsertBr]    Script Date: 02/26/2011 19:09:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Next_Delivery_Date] (@go_date  smalldatetime, @go_hour nvarchar(10),@go_freq nchar(10) ) RETURNS varchar(max) AS BEGIN

	DECLARE @Block int,
		@NumBlocks int,
		@Result varchar(max)
-------------------------------------
----------------------------------------------------------------------------------------
-- UDF..........


DECLARE @CURR_DATE smalldatetime
SET  @CURR_DATE = ISNULL(@CURR_DATE, getDATE())

DECLARE @MM_DD_YY__DATE smalldatetime
SET  @MM_DD_YY__DATE = ISNULL(@CURR_DATE, convert(varchar,getdate(), 101))

DECLARE @NEXT_DELIVERY_DATE NVARCHAR(MAX)

--
--declare @go_date smalldatetime
--set @go_date = (select go_date from temp_campaign_calendar where tc_id = tc_id)
--
--declare @go_hour nvarchar(10)
--set @go_hour = (select go_hour from temp_campaign_calendar where tc_id = tc_id)
--
--declare @go_freq nchar(10)
--set @go_freq = (select go_freq from temp_campaign_calendar where tc_id = tc_id)

--------------------------------------------------------------------------------------

if @go_freq = 'HOURLY'
BEGIN
-- Next_delevery_date --- day : Time
-- Next_delevery_date --- "TODAY : LESS THAN 59 MINUTES"
--Next_delevery_date = 
--Next_delevery_date_go-date = today
--Next_delevery_date_go_hour = (top of the hour) - (currdate) .....

-- GET CURRENT DATE AT BUTTON PRESS...
--DECLARE @CURR_DATE smalldatetime
 --SET  @CURR_DATE = ISNULL(@CURR_DATE, getDATE())

--round it up...
 DECLARE @TILL_TOP_O_HR_DATE smalldatetime
 SET  @TILL_TOP_O_HR_DATE = select (60-RIGHT(convert(varchar(5),getdate(),114),2))

@NEXT_DELIVERY_DATE = 'TODAY : LESS THAN ' + @TILL_TOP_O_HR_DATE + ' MINUTES'

END

----------------------------------------------------------------------------------------------

if @go_freq = 'DAILY'
BEGIN


-- Next_delevery_date --- day : Time
-- Next_delevery_date --- "TODAY : LESS THAN 6:59 MINUTES"
--Next_delevery_date = 
--Next_delevery_date_go-date = today
--Next_delevery_date_go_hour = GO_HOUR - (currdate) .....

-- GET CURRENT DATE AT BUTTON PRESS...
--DECLARE @CURR_DATE smalldatetime
-- SET  @CURR_DATE = ISNULL(@CURR_DATE, getDATE())


declare @countdown nvarchar(10)
set  @countdown  = @GO_HOUR - @CURR_DATE

@NEXT_DELIVERY_DATE = 'TODAY : LESS THAN ' + @countdown + ' MINUTES'

END

----------------------------------------------------------------------------------------------

if @go_freq = 'WEEKLY'
BEGIN
-- Next_delevery_date --- day : Time
-- Next_delevery_date --- "THIS TUESDAY : LESS THAN 6:59 MINUTES"
--Next_delevery_date = 
--Next_delevery_date_go-date = today
--Next_delevery_date_go_hour = GO_HOUR - (currdate) .....

-- GET ACTUAL DAY ITS SUPPOSE TO GO OUT FROM DB...SELECT DATENAME(WEEKDAY, @CURR_DATE)
DECLARE @ACTUAL_DAY NVARCHAR(25) 
SET @ACTUAL_DAY = SELECT DATENAME(WEEKDAY, @GO_DATE) from temp_campaign_calendar where tc_id = tc_id

-- GET CURRENT DATE AT BUTTON PRESS...
--DECLARE @CURR_DATE smalldatetime
-- SET  @CURR_DATE = ISNULL(@CURR_DATE, getDATE())

-----------------------------
-- FIGURE OUT THE FUTURE WEEKDAY TO GO OUT...

declare @DateNext datetime
declare @Current datetime
declare @current_day nvarchar(10)

--set @Current = '2011-02-20 00:00:00'
set @Current = @go_date
set @current_day = DATENAME(WEEKDAY, @Current)

--set @DateNext = DATEADD(DAY, 7,  '2011-02-10 00:00:00')
set @DateNext = DATEADD(DAY, 7,  @curr_date)
SELECT DATENAME(WEEKDAY, @dateNext)

while @DateNext < @Current 
  set @DateNext =  DATEADD(DAY, 7,  @DateNext)


-- 2011-02-24 00:00:00.000
--select @dateNext
-- Thursday
--SELECT DATENAME(WEEKDAY, @dateNext)

----------------------------


-- SUBTRACT THE TWO..
DECLARE @Startdate DATETIME, @Enddate DATETIME
--SET @Startdate = '2011-01-02 11:35:26'
--SET @Enddate = '2011-01-06 03:15:31'
SET @Startdate = @CURR_DATE
SET @Enddate = @DateNext


-- Query by SqlServerCurry.com
-- Total seconds in a day
DECLARE @TotalSec int
SET @TotalSec = 24*60*60;


DECLARE @DAYS char(2)
DECLARE @HOURS char(2)
DECLARE @MIN char(2)
DECLARE @SEC char(2)

-- Convert DateDiff into seconds
DECLARE @DiffSecs int
SET @DiffSecs = DATEDIFF(SECOND, @Startdate, @Enddate)

--SELECT
-- CONVERT(char(2), (@DiffSecs/@TotalSec))as [Days],
-- CONVERT(char(2), ((@DiffSecs%@TotalSec)/3600)) as [Hours],
-- CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)/60)) as [Minutes],
-- CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)%60)) as [Seconds]

set @DAYS = CONVERT(char(2), (@DiffSecs/@TotalSec))
set @HOURS = CONVERT(char(2), ((@DiffSecs%@TotalSec)/3600)) 
set @MIN = CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)/60)) 
set @SEC = CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)%60))


SET @NEXT_DELIVERY_DATE = 'SCHED-' + @ACTUAL_DAY + ' : ' + @DAYS + 'Days ' + @HOURS + 'Hrs ' +
@MIN + 'Mins ' + @SEC + 'Secs LEFT TILL LAUNCH' 

END


----------------------------------------------------------------------------------------------

if @go_freq = 'MONTHLY'

BEGIN
BEGIN
-- Next_delevery_date --- day : Time
-- Next_delevery_date --- "THIS TUESDAY : LESS THAN 6:59 MINUTES"
--Next_delevery_date = 
--Next_delevery_date_go-date = today
--Next_delevery_date_go_hour = GO_HOUR - (currdate) .....

-- GET ACTUAL DAY ITS SUPPOSE TO GO OUT FROM DB...SELECT DATENAME(WEEKDAY, @CURR_DATE)
DECLARE @ACTUAL_DAY NVARCHAR(25) 
SET @ACTUAL_DAY = SELECT DATENAME(WEEKDAY, @GO_DATE) from temp_campaign_calendar where tc_id = tc_id

-- GET CURRENT DATE AT BUTTON PRESS...
--DECLARE @CURR_DATE smalldatetime
-- SET  @CURR_DATE = ISNULL(@CURR_DATE, getDATE())

-----------------------------
-- FIGURE OUT THE FUTURE WEEKDAY TO GO OUT...

declare @DateNext datetime
declare @Current datetime
declare @current_day nvarchar(10)

--set @Current = '2011-02-25 00:00:00'
set @Current = @go_date
set @current_day = DATENAME(WEEKDAY, @Current)
SELECT @current_day

--set @DateNext = DATEADD(MONTH, 1,  '2011-02-10 00:00:00')
set @DateNext = DATEADD(MONTH, 1,  @curr_date)
SELECT DATENAME(WEEKDAY, @dateNext)

while @DateNext < @Current 
  set @DateNext =  DATEADD(MONTH, 1,  @DateNext)


-- 2011-02-24 00:00:00.000
--select @dateNext
-- Thursday
--SELECT DATENAME(WEEKDAY, @dateNext)

----------------------------


-- SUBTRACT THE TWO..
DECLARE @Startdate DATETIME, @Enddate DATETIME
--SET @Startdate = '2011-01-02 11:35:26'
--SET @Enddate = '2011-01-06 03:15:31'
SET @Startdate = @CURR_DATE
SET @Enddate = @DateNext


-- Query by SqlServerCurry.com
-- Total seconds in a day
DECLARE @TotalSec int
SET @TotalSec = 24*60*60;


DECLARE @DAYS char(2)
DECLARE @HOURS char(2)
DECLARE @MIN char(2)
DECLARE @SEC char(2)

-- Convert DateDiff into seconds
DECLARE @DiffSecs int
SET @DiffSecs = DATEDIFF(SECOND, @Startdate, @Enddate)

--SELECT
-- CONVERT(char(2), (@DiffSecs/@TotalSec))as [Days],
-- CONVERT(char(2), ((@DiffSecs%@TotalSec)/3600)) as [Hours],
-- CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)/60)) as [Minutes],
-- CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)%60)) as [Seconds]

set @DAYS = CONVERT(char(2), (@DiffSecs/@TotalSec))
set @HOURS = CONVERT(char(2), ((@DiffSecs%@TotalSec)/3600)) 
set @MIN = CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)/60)) 
set @SEC = CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)%60))


SET @NEXT_DELIVERY_DATE = 'SCHED-' + @ACTUAL_DAY + ' : ' + @DAYS + 'Days ' + @HOURS + 'Hrs ' +
@MIN + 'Mins ' + @SEC + 'Secs LEFT TILL LAUNCH' 

END

---------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
-----------------------------------
	RETURN @NEXT_DELIVERY_DATE

END

Open in new window

0
Comment
Question by:GlobaLevel
  • 3
4 Comments
 
LVL 17

Expert Comment

by:OriNetworks
ID: 34990021
What error are you receiving?
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34990047
first problem: your set statements aren't correct

you either need to do:

SELECT  @TILL_TOP_O_HR_DATE = (60-RIGHT(convert(varchar(5),getdate(),114),2)),

or

SET  @TILL_TOP_O_HR_DATE = (60-RIGHT(convert(varchar(5),getdate(),114),2)),

second problem: you have some lines where you're trying to set variable values without using SET or SELECT (see line 54)
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34990055
third problem: you seem to be trying to re-declare variable names that you've already used previously in the UDF
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 2000 total points
ID: 34990063
the code obviously needs major cleanup, but at the very least, this should allow you to create your UDF - all i did was fix a bunch of syntactical errors and didn't look at the logic of it though
/****** Object:  UserDefinedFunction [dbo].[InsertBr]    Script Date: 02/26/2011 19:09:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Next_Delivery_Date] (@go_date  smalldatetime, @go_hour nvarchar(10),@go_freq nchar(10) ) RETURNS varchar(max) AS BEGIN

	DECLARE @Block int,
		@NumBlocks int,
		@Result varchar(max)
-------------------------------------
----------------------------------------------------------------------------------------
-- UDF..........


DECLARE @CURR_DATE smalldatetime
SET  @CURR_DATE = ISNULL(@CURR_DATE, getDATE())

DECLARE @MM_DD_YY__DATE smalldatetime
SET  @MM_DD_YY__DATE = ISNULL(@CURR_DATE, convert(varchar,getdate(), 101))

DECLARE @NEXT_DELIVERY_DATE NVARCHAR(MAX)

--
--declare @go_date smalldatetime
--set @go_date = (select go_date from temp_campaign_calendar where tc_id = tc_id)
--
--declare @go_hour nvarchar(10)
--set @go_hour = (select go_hour from temp_campaign_calendar where tc_id = tc_id)
--
--declare @go_freq nchar(10)
--set @go_freq = (select go_freq from temp_campaign_calendar where tc_id = tc_id)

--------------------------------------------------------------------------------------

if @go_freq = 'HOURLY'
BEGIN
-- Next_delevery_date --- day : Time
-- Next_delevery_date --- "TODAY : LESS THAN 59 MINUTES"
--Next_delevery_date = 
--Next_delevery_date_go-date = today
--Next_delevery_date_go_hour = (top of the hour) - (currdate) .....

-- GET CURRENT DATE AT BUTTON PRESS...
--DECLARE @CURR_DATE smalldatetime
 --SET  @CURR_DATE = ISNULL(@CURR_DATE, getDATE())

--round it up...
 DECLARE @TILL_TOP_O_HR_DATE smalldatetime
 SELECT  @TILL_TOP_O_HR_DATE = (60-RIGHT(convert(varchar(5),getdate(),114),2)),
		 @NEXT_DELIVERY_DATE = 'TODAY : LESS THAN ' + @TILL_TOP_O_HR_DATE + ' MINUTES'

END

----------------------------------------------------------------------------------------------

if @go_freq = 'DAILY'
BEGIN


-- Next_delevery_date --- day : Time
-- Next_delevery_date --- "TODAY : LESS THAN 6:59 MINUTES"
--Next_delevery_date = 
--Next_delevery_date_go-date = today
--Next_delevery_date_go_hour = GO_HOUR - (currdate) .....

-- GET CURRENT DATE AT BUTTON PRESS...
--DECLARE @CURR_DATE smalldatetime
-- SET  @CURR_DATE = ISNULL(@CURR_DATE, getDATE())


declare @countdown nvarchar(10)
set  @countdown  = @GO_HOUR - @CURR_DATE

SELECT @NEXT_DELIVERY_DATE = 'TODAY : LESS THAN ' + @countdown + ' MINUTES'

END

----------------------------------------------------------------------------------------------

if @go_freq = 'WEEKLY'
BEGIN
-- Next_delevery_date --- day : Time
-- Next_delevery_date --- "THIS TUESDAY : LESS THAN 6:59 MINUTES"
--Next_delevery_date = 
--Next_delevery_date_go-date = today
--Next_delevery_date_go_hour = GO_HOUR - (currdate) .....

-- GET ACTUAL DAY ITS SUPPOSE TO GO OUT FROM DB...SELECT DATENAME(WEEKDAY, @CURR_DATE)
DECLARE @ACTUAL_DAY NVARCHAR(25) 
SELECT @ACTUAL_DAY =  DATENAME(WEEKDAY, @GO_DATE) from temp_campaign_calendar where tc_id = tc_id

-- GET CURRENT DATE AT BUTTON PRESS...
--DECLARE @CURR_DATE smalldatetime
-- SET  @CURR_DATE = ISNULL(@CURR_DATE, getDATE())

-----------------------------
-- FIGURE OUT THE FUTURE WEEKDAY TO GO OUT...

declare @DateNext datetime
declare @Current datetime
declare @current_day nvarchar(10)

--set @Current = '2011-02-20 00:00:00'
set @Current = @go_date
set @current_day = DATENAME(WEEKDAY, @Current)

--set @DateNext = DATEADD(DAY, 7,  '2011-02-10 00:00:00')
set @DateNext = DATEADD(DAY, 7,  @curr_date)
--SELECT DATENAME(WEEKDAY, @dateNext)

while @DateNext < @Current 
  set @DateNext =  DATEADD(DAY, 7,  @DateNext)


-- 2011-02-24 00:00:00.000
--select @dateNext
-- Thursday
--SELECT DATENAME(WEEKDAY, @dateNext)

----------------------------


-- SUBTRACT THE TWO..
DECLARE @Startdate DATETIME, @Enddate DATETIME
--SET @Startdate = '2011-01-02 11:35:26'
--SET @Enddate = '2011-01-06 03:15:31'
SET @Startdate = @CURR_DATE
SET @Enddate = @DateNext


-- Query by SqlServerCurry.com
-- Total seconds in a day
DECLARE @TotalSec int
SET @TotalSec = 24*60*60;


DECLARE @DAYS char(2)
DECLARE @HOURS char(2)
DECLARE @MIN char(2)
DECLARE @SEC char(2)

-- Convert DateDiff into seconds
DECLARE @DiffSecs int
SET @DiffSecs = DATEDIFF(SECOND, @Startdate, @Enddate)

--SELECT
-- CONVERT(char(2), (@DiffSecs/@TotalSec))as [Days],
-- CONVERT(char(2), ((@DiffSecs%@TotalSec)/3600)) as [Hours],
-- CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)/60)) as [Minutes],
-- CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)%60)) as [Seconds]

set @DAYS = CONVERT(char(2), (@DiffSecs/@TotalSec))
set @HOURS = CONVERT(char(2), ((@DiffSecs%@TotalSec)/3600)) 
set @MIN = CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)/60)) 
set @SEC = CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)%60))


SET @NEXT_DELIVERY_DATE = 'SCHED-' + @ACTUAL_DAY + ' : ' + @DAYS + 'Days ' + @HOURS + 'Hrs ' +
@MIN + 'Mins ' + @SEC + 'Secs LEFT TILL LAUNCH' 

END


----------------------------------------------------------------------------------------------

if @go_freq = 'MONTHLY'

BEGIN
-- Next_delevery_date --- day : Time
-- Next_delevery_date --- "THIS TUESDAY : LESS THAN 6:59 MINUTES"
--Next_delevery_date = 
--Next_delevery_date_go-date = today
--Next_delevery_date_go_hour = GO_HOUR - (currdate) .....

-- GET ACTUAL DAY ITS SUPPOSE TO GO OUT FROM DB...SELECT DATENAME(WEEKDAY, @CURR_DATE)
--DECLARE @ACTUAL_DAY NVARCHAR(25) 
SELECT @ACTUAL_DAY =  DATENAME(WEEKDAY, @GO_DATE) from temp_campaign_calendar where tc_id = tc_id

-- GET CURRENT DATE AT BUTTON PRESS...
--DECLARE @CURR_DATE smalldatetime
-- SET  @CURR_DATE = ISNULL(@CURR_DATE, getDATE())

-----------------------------
-- FIGURE OUT THE FUTURE WEEKDAY TO GO OUT...

--declare @DateNext datetime
--declare @Current datetime
--declare @current_day nvarchar(10)

--set @Current = '2011-02-25 00:00:00'
set @Current = @go_date
set @current_day = DATENAME(WEEKDAY, @Current)
--SELECT @current_day

--set @DateNext = DATEADD(MONTH, 1,  '2011-02-10 00:00:00')
set @DateNext = DATEADD(MONTH, 1,  @curr_date)
--SELECT DATENAME(WEEKDAY, @dateNext)

while @DateNext < @Current 
  set @DateNext =  DATEADD(MONTH, 1,  @DateNext)


-- 2011-02-24 00:00:00.000
--select @dateNext
-- Thursday
--SELECT DATENAME(WEEKDAY, @dateNext)

----------------------------


-- SUBTRACT THE TWO..
--DECLARE @Startdate DATETIME, @Enddate DATETIME
--SET @Startdate = '2011-01-02 11:35:26'
--SET @Enddate = '2011-01-06 03:15:31'
SET @Startdate = @CURR_DATE
SET @Enddate = @DateNext


-- Query by SqlServerCurry.com
-- Total seconds in a day
--DECLARE @TotalSec int
SET @TotalSec = 24*60*60;


--DECLARE @DAYS char(2)
--DECLARE @HOURS char(2)
--DECLARE @MIN char(2)
--DECLARE @SEC char(2)

-- Convert DateDiff into seconds
--DECLARE @DiffSecs int
SET @DiffSecs = DATEDIFF(SECOND, @Startdate, @Enddate)

--SELECT
-- CONVERT(char(2), (@DiffSecs/@TotalSec))as [Days],
-- CONVERT(char(2), ((@DiffSecs%@TotalSec)/3600)) as [Hours],
-- CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)/60)) as [Minutes],
-- CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)%60)) as [Seconds]

set @DAYS = CONVERT(char(2), (@DiffSecs/@TotalSec))
set @HOURS = CONVERT(char(2), ((@DiffSecs%@TotalSec)/3600)) 
set @MIN = CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)/60)) 
set @SEC = CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)%60))


SET @NEXT_DELIVERY_DATE = 'SCHED-' + @ACTUAL_DAY + ' : ' + @DAYS + 'Days ' + @HOURS + 'Hrs ' +
@MIN + 'Mins ' + @SEC + 'Secs LEFT TILL LAUNCH' 

END

---------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
-----------------------------------
	RETURN @NEXT_DELIVERY_DATE

END

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question