Solved

ms sql server 2005 - issues with creating a UDF

Posted on 2011-02-26
4
235 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 500 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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 Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

785 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