Case statement help - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Case statement help

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-01-13 : 16:04:46

I have a function that I am trying to use a case statement for at the end.


CREATE FUNCTION ASP_DueDate
(
	@ProductionDate DateTime,
	@Market varchar,
             @AspNum int,
	@ProgramNum decimal
)
RETURNS DateTime
AS
BEGIN
	DECLARE @AddDays int,
		@FPODays int

	SELECT	@AddDays = addays
	FROM	wkrun09
	WHERE 	ProgramNo = @ProgramNum and fcustno = @Market

	SELECT	@FPODays = fpodays
	FROM	wkrun09
	WHERE 	ProgramNo = @ProgramNum and fcustno = @Market

Select CASE @AspNum
When 1 Then
	Return DateAdd (day, @AddDays, @ProductionDate)
When 2 Then
	Return ''
When 3 Then
	Return DateAdd (day, @FPODays, @ProductionDate)
When 4 Then
	Return ''
End
End

Basically I am set a few variables and then based on the case statement i want to do the return. There the Return "" is I want to return nothing. Can someone help me out with he formating to have the CASE statement within my function?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 16:20:54

Replace '' with NULL.
Or simply remove the WHEN2 and WHEN4 from case statement.

E 12°55'05.63"
N 56°04'39.26"

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-01-13 : 16:22:46

I tried both but still get incorrect syntax near Return, When, end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 16:30:00

[code]CREATE FUNCTION ASP_DueDate
(
@ProductionDate DateTime,
@Market varchar,
@AspNum int,
@ProgramNum decimal
)
RETURNS DateTime
AS
BEGIN
DECLARE @AddDays int,
@FPODays int

SELECT @AddDays = addays
FROM wkrun09
WHERE ProgramNo = @ProgramNum and fcustno = @Market

SELECT @FPODays = fpodays
FROM wkrun09
WHERE ProgramNo = @ProgramNum and fcustno = @Market

Return CASE @AspNum
When 1 Then
DateAdd (day, @AddDays, @ProductionDate)
When 3 Then
DateAdd (day, @FPODays, @ProductionDate)
else null
End
End[/code]

E 12°55'05.63"
N 56°04'39.26"

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-01-13 : 16:33:29

Perfect. Was I just missing and else clause?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 16:36:09

One select less

CREATE FUNCTION ASP_DueDate
(
	@ProductionDate DateTime,
	@Market varchar,
	@AspNum int,
	@ProgramNum decimal
)
RETURNS DateTime
AS
BEGIN
	DECLARE @AddDays int,
		@FPODays int

	SELECT	@AddDays = addays,
		@FPODays = fpodays
	FROM	wkrun09
	WHERE 	ProgramNo = @ProgramNum and fcustno = @Market

Return CASE @AspNum
When 1 Then
	DateAdd (day, @AddDays, @ProductionDate)
When 3 Then
	DateAdd (day, @FPODays, @ProductionDate)
else null
End
END

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 16:37:15

Also try this inline version, which should be faster

CREATE FUNCTION ASP_DueDate
(
	@ProductionDate DATETIME,
	@Market VARCHAR(20),
	@AspNum INT,
	@ProgramNum DECIMAL(38, 0)
)
RETURNS DATETIME
AS
BEGIN
	RETURN	(
			SELECT	TOP 1
				CASE @AspNum
					WHEN 1 THEN DATEADD(DAY, AdDays, @ProductionDate)
					WHEN 3 THEN DATEADD(DAY, fpoDays, @ProductionDate)
					ELSE NULL
				END
			FROM	wkRun09
			WHERE	ProgramNo = @ProgramNum
				AND fCustNo = @Market
		)
END

E 12°55'05.63"
N 56°04'39.26"

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-01-13 : 16:56:38

Perfect thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources