IF NOT EXISTS and Create Function Statement
-
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".
-
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 GETMONTHENDGOCREATE FUNCTION GETMONTHEND(@DATE DATETIME)RETURNS DATETIMEASBEGINRETURN (DATEADD(MM, DATEDIFF(MM, 0, @DATE) + 1, 0) - 1)ENDhth,
-
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)
-
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