September 18, 2008 at 8:07 am
USE PRACTICE
go
IF NOT EXISTS
(
SELECT *
FROM SYS.OBJECTS
WHERE TYPE = 'FN'
AND
NAME = 'GETMONTHEND'
)
CREATE FUNCTION GETMONTHEND(@DATE DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN (DATEADD(MM, DATEDIFF(MM, 0, @DATE) + 1, 0) - 1)
END
The error is:
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'FUNCTION'.
Msg 137, Level 15, State 2, Line 17
Must declare the scalar variable "@DATE".
September 18, 2008 at 9:25 am
The CREATE statement should be the first line in a batch.
You may wish to try:
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'GETMONTHEND' AND xtype IN ('FN', 'IF', 'TF'))
DROP FUNCTION GETMONTHEND
GO
CREATE FUNCTION GETMONTHEND(@DATE DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN (DATEADD(MM, DATEDIFF(MM, 0, @DATE) + 1, 0) - 1)
END
hth,
September 18, 2008 at 9:39 am
IF NOT EXISTS
(
SELECT *
FROM SYS.OBJECTS
WHERE TYPE = 'FN'
AND
NAME = 'GETMONTHEND'
)
EXEC sp_executesql @Statement = N'CREATE FUNCTION GETMONTHEND(@DATE DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN (DATEADD(MM, DATEDIFF(MM, 0, @DATE) + 1, 0) - 1)
END';
Cannot tell you WHY the @DATE is not accepted when wrapped by the IF statement. I would like to know the answer to that also! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
September 18, 2008 at 9:43 am
DB_Newbie,
With hesitation....
I'd say that the @date isn't recognised as being declared because the parser doesn't understand the CREATE statement context and therefore the variable doesn't exist.
Possibly.... :crazy:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply