Geek asks and answers

Can I create a global function in SQL Server?

Is it possible to create a function on SQL Server that I could use in any database on the server without adding a database prefix?

For example, using this function:

CREATE FUNCTION getDays (@date date)
RETURNS INT
AS
BEGIN

RETURN CASE WHEN MONTH(@date) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
            WHEN MONTH(@date) IN (4, 6, 9, 11) THEN 30
            ELSE CASE WHEN (YEAR(@date) % 4    = 0 AND
                            YEAR(@date) % 100 != 0) OR
                           (YEAR(@date) % 400  = 0)
                      THEN 29
                      ELSE 28
                 END
       END

END
+5
source share
2 answers

You can create a function in master (or some other persistent database), and then create a synonym in the model database:

USE model;
GO
CREATE SYNONYM dbo.getDays FOR master.dbo.getDays;

This will create a synonym for the function in any new databases, but for existing databases (or databases connected or restored in the future) you will need to copy the synonym. This will allow you to reference an object with a two-component name in any database, but only to store one copy of the code.

, :

  RETURN (SELECT DATEPART(DAY, DATEADD(DAY, -1, 
     DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(@date), @date)))));

, :

USE [master];
GO
DROP FUNCTION dbo.getDays;
GO
CREATE FUNCTION dbo.getDays
(
    @date DATE
)
RETURNS INT
AS
BEGIN
    RETURN (SELECT DATEPART(DAY, DATEADD(DAY, -1, 
         DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(@date), @date)))));
END
GO

, :

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10) 
+ 'USE ' + QUOTENAME(name) + ';

IF OBJECT_ID(''dbo.getDays'', ''FN'') IS NOT NULL
  DROP FUNCTION dbo.getDays;

IF OBJECT_ID(''dbo.getDays'', ''SN'') IS NOT NULL
  DROP SYNONYM dbo.getDays

CREATE SYNONYM dbo.getDays FOR master.dbo.getDays;'
 FROM sys.databases WHERE name <> 'master';

PRINT @sql;

EXEC sp_executesql @sql;
+14

master , . :

select <dbname>.<schema>.getDays(...)

Microsoft , ?

+3

More articles:


All Articles