Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ms sql server 2005 - issues with creating a UDF

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

839 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