Solved

ms sql server 2005 - issues with creating a UDF

Posted on 2011-02-26
4
222 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
Comment Utility
What error are you receiving?
0
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now