USE NorthDynamic
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- Single-argument version of SanitizeObjectNameEx
-- =============================================
CREATE FUNCTION [dbo].[SanitizeObjectName]
(
       @full_name nvarchar(804)
)
RETURNS nvarchar(804)
AS
BEGIN
       DECLARE @full_nameS nvarchar(804)
       SET @full_nameS = [dbo].[SanitizeObjectNameEx](@full_name, DEFAULT, DEFAULT)
       RETURN @full_nameS
END
GO
 
-- =============================================
-- Given the name of an object (such as a table, view, or stored procedure), returns the formal, fully qualified name.
-- Thanks to Erland Sommarskog ( http://www.sommarskog.se/dynamic_sql.html#objnameparams )
-- =============================================
CREATE FUNCTION [dbo].[SanitizeObjectNameEx]
(
       @full_name       nvarchar(804),            -- Name of object, e.g. 'tablename', '#tablename', 'tempdb..#tablename', 'db.dbo.storedprocedure', etc. However, 'server.db.schema.table' is NOT allowed.
       @throwIfNotExist bit            = 'false', -- Whether or not to require that the object exists. If parameter value is 1 (or 'true') and the object doesn't exist, an error will be thrown.
       @schema_default  sysname        = 'dbo'    -- Schema name unless otherwise specified. Ignored if
                                                  --   object_id(@full_name) IS NOT NULL (because existing objects already have a specified schema),
                                                  --   parsename(@full_name, 2) IS NOT NULL (because the schema is specified within @full_name), or
                                                  --   @throwIfNotExist <> 'true' (because otherwise an error is thrown if the object doesn't already exist)
       /*
              Currently (SQL Server 2019), the length of an object name might be as long as [sysname].[sysname].[sysname]
              which would be 392 characters (1 + 128 + 3 + 128 + 3 + 128 + 1).
              Doubling that and adding 20 extra characters for forward compatibility and padding gives us 804
       */
)
RETURNS nvarchar(804)
AS
BEGIN
       DECLARE  @obj_id       int,          -- object_id(@full_name)
                @db_id        int,          -- database containing the object (or current db if object doesn't exist)
                @db_nameS     sysname,      -- name of database, sanitized
                @schema_name  sysname,      -- name of schema, NOT sanitized
                @bit          bit,          -- Just a dummy variable, needed when using the Throw function.
                @full_nameS   nvarchar(804) -- The return value! The sanitized, fully qualified name of the object.
 
       -- Supporting names on linked server would require a whole lot more work.
       IF parsename(@full_name, 4) IS NOT NULL
              SET @bit = [dbo].[Throw]('SanitizeObjectNameEx error: Tables on a different server are not supported. (''' + parsename(@full_name, 4) + ''' in ' + @full_name + ')')
 
       -- get @db_id
       IF (LEFT(@full_name,1) = '#')
              BEGIN
                     -- Special handling for a string that's assumed to be simply a temp table name
                     SET @full_name = 'tempdb..' + @full_name
                     SET @db_id = db_id('tempdb')
              END
       ELSE IF parsename(@full_name, 3) IS NOT NULL
              -- Db is explicitly mentioned in the name
              SELECT @db_id = db_id(parsename(@full_name, 3))
       ELSE
              -- Default to current db
              SELECT @db_id = db_id()
 
       SET @db_nameS = quotename(db_name(@db_id)) --sanitized database name
 
       SELECT @obj_id = object_id(@full_name) -- object id if exists
 
       IF @obj_id IS NOT NULL
              -- found the object! Return a sanitized version of the formal name.
              SELECT @full_nameS = @db_nameS + '.' +
                                   quotename(object_schema_name(@obj_id, @db_id)) + '.' +
                                   quotename(object_name(@obj_id, @db_id))
       ELSE IF @throwIfNotExist = 'false'
              BEGIN
                     -- Table didn't exist but caller explicitly said that was okay.
 
                     -- Get the schema name
                     SET @schema_name = parsename(@full_name, 2)
                     IF @schema_name IS NULL
                           SET @schema_name = @schema_default
 
                     SELECT @full_nameS = @db_nameS + '.' +
                                          quotename(@schema_name) + '.' +
                                          quotename(parsename(@full_name, 1))
              END
       ELSE -- object not found AND caller wants an error thrown if that's the case
              SET @bit = [dbo].[Throw]('SanitizeObjectNameEx error: No such object ''' + @full_name + '''')
 
       -- If we got here, success!
       RETURN @full_nameS
END
GO
 
/******
       Currently (SQL Server 2019) throwing nice errors within functions aren't allowed.
       We should all be embarrassed that if you want your functions to throw an error with a message, the current best practice is to craft an error string and try to convert it to a number.
       This creates a conversion error rather than a relevant type of error, but at least the error string is raised!
       Thanks to https://stackoverflow.com/a/9583534/7158380
       Example:
              DECLARE @bit bit
              SET @bit = [dbo].[Throw]('Error message to be thrown') -- throws error message "Conversion failed when converting the nvarchar value 'Error message to be thrown' to data type int."
******/
CREATE FUNCTION [dbo].[Throw]
(
    @error NVARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
    RETURN CAST(@error AS INT)
END
GO