dbo.fn_IsEmptyField

USE [insert_database_name];
GO
IF EXISTS ( SELECT *
            FROM   dbo.sysobjects
            WHERE  id = object_id (N'dbo.fn_IsEmptyField')
                   AND type IN ( N'FN', N'IF', N'TF') )
BEGIN
   DROP FUNCTION dbo.fn_IsEmptyField
END
GO
CREATE FUNCTION dbo.fn_IsEmptyField
( @InputObject AS SQL_VARIANT )
RETURNS BIT
AS
/*
*****************************************************************************
Created By: Barry Blessing
-------------------------------------------------------------------------------
Date Created: 12/01/2016
-------------------------------------------------------------------------------
Purpose: This function evaluates input for NULL-ness or empty-state,
i.e. String.Empty or NULL or ' ', etc.
Database: CMU
-------------------------------------------------------------------------------
Parameters: @@InputObject: Type SQL_VARIANT can represent any SQL or .NET type.
-------------------------------------------------------------------------------
Performance Statistics
*******************************************************************************
Date Ran: 12/01/2016

Test Statements ran:

-- THESE ARE NULL/EMPTY
SELECT dbo.fn_IsEmptyField(NULL) AS 'RETURNED', 1 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(NULL) AS 'RETURNED', 1 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(' ') AS 'RETURNED', 1 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('') AS 'RETURNED', 1 as 'SHOULD BE'

-- THESE ARE ***NOT*** NULL/EMPTY
SELECT dbo.fn_IsEmptyField('John Q Public Co.') AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField('0') AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('1') AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField(0) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(1) AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField('False') AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('True') AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField(GETDATE()) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('12/2/2016') AS 'RETURNED', 0 as 'SHOULD BE'

SELECT dbo.fn_IsEmptyField(12.32) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(123456.65) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('12,3456.65') AS 'RETURNED', 0 as 'SHOULD BE'

Run time in seconds: 0
*******************************************************************************
Maintenance Log
*******************************************************************************
Date Ver PCM Author Comments
-------------------------------------------------------------------------------
12/01/2016 1.00 xxxxxx Barry Blessing Created in advance of need.
*******************************************************************************
*/
BEGIN

DECLARE @string AS VARCHAR(8000) = CAST(@InputObject AS VARCHAR(8000));
DECLARE @length AS BIGINT = LEN(LTRIM(RTRIM(@string)));
DECLARE @return AS BIT;

If @InputObject IS NULL OR @InputObject = NULL RETURN 1; -- IsNull

SET @return = CASE WHEN @length > 0 AND NOT @string IS NULL
                        AND NOT UPPER(LTRIM(RTRIM(@string))) = 'NULL' THEN 0
                   ELSE 1
END;
RETURN @return;
END
GO

EXEC dbo.sp_UpdateFunctionDescription
     @Function = 'fn_IsEmptyField',
     @Description = 'Returns Boolean if String-value of input object is neither equivalent to NULL or String.Empty.'
GO
Advertisements

dbo.fn_IsEmptyField

 

USE TEST;
GO
IF EXISTS ( SELECT *
            FROM   dbo.sysobjects
            WHERE  id = object_id (N'dbo.fn_IsEmptyField')
AND type IN ( N'FN', N'IF', N'TF') )
BEGIN
    DROP FUNCTION dbo.fn_IsEmptyField
END
GO
CREATE FUNCTION dbo.fn_IsEmptyField
( @InputObject AS SQL_VARIANT )
RETURNS BIT
AS
/*
========================================================================
-- THESE ARE NULL/EMPTY
SELECT dbo.fn_IsEmptyField(NULL) AS 'RETURNED', 1 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(NULL) AS 'RETURNED', 1 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(' ') AS 'RETURNED', 1 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('') AS 'RETURNED', 1 as 'SHOULD BE'

-- THESE ARE ***NOT*** NULL/EMPTY
SELECT dbo.fn_IsEmptyField('John Q Public Co.') AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('0') AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('1') AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField(123456.65) AS 'RETURNED', 0 as 'SHOULD BE'
SELECT dbo.fn_IsEmptyField('12,3456.65') AS 'RETURNED', 0 as 'SHOULD BE'
========================================================================
*/
BEGIN
DECLARE @string AS VARCHAR(8000) = CAST(@InputObject AS VARCHAR(8000));
DECLARE @length AS BIGINT = LEN(LTRIM(RTRIM(@string)));
DECLARE @return AS BIT;

If @InputObject IS NULL OR @InputObject = NULL RETURN 1; -- IsNull

SET @return = CASE WHEN @length > 0 AND NOT @string IS NULL
AND NOT UPPER(LTRIM(RTRIM(@string))) = 'NULL'
THEN 0
                        ELSE 1 -- String.Empty or IsNull
                   END;
RETURN @return;
END
GO

EXEC dbo.sp_UpdateFunctionDescription @Function = 'fn_IsEmptyField',
@Description = 'Returns Boolean if String-value of input object is neither equivalent to NULL or String.Empty.'
GO

 

SQL Server – error 64 : Server-name no longer available

I ran across a crazy issue this am that I’ve never seen before. I was setting up SQL Server Express 2008R2 on a smaller server at home, the likes of which I have done many times before with no issues. Then I did the following, per my usual config routine:

  • Opened ports 1433 and 1434 on this server for “listening” for SQL requests
  • Opened a hole in the firewall for SQLBrowser.exe
  • Enabled TCP/IP listening for those same ports in SQLServerManager10.msc (running on Windows 10 Professional operating system).
  • Enabled remote connections to the Server process
  • Set up a login account for Administrator with full rights to the SQL server processes

So then I went to test the setup from my laptop, fired up SQL Server Management Studio, and connected to the SQL Server.  Connection made – check.  Opened up a known scalar function to modify – check.  Run the alter script – check.  Run it again to double check the persistence of the network connection – ERROR 64.

SQL_Server_Cannot_Connect

I had never seen that one before, but I knew it was network or network protocol related.

I remoted into this particular server and started double-checking all settings that would seem to have any relevance to that issue.

After A LOT of Googling and matching snippets of information together, I finally found resolution with the following regedit modification for the local registry.

  • HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters
  • Create a DWORD “Sesstimeout” key with a decimal value of 360.

reg_Edit

It seems the session-layer of the connection was not persistent enough for remote connections.  Why doesn’t Microsoft take care of this by default with a config script? We might never live long enough to know…

Problem solved, for now.  If I see it again, I will update this post.

A couple of gotchas with the ClosedXML library

OpenXML is the file-structure standard upon which modern MS Office documents are created.  ClosedXML is an open-source .NET code library built to more easily implement that standard and allow .NET developers an abstraction against that standard for which they can more easily and efficiently code.

I like the OpenXML standard for Microsoft Office kinds of documents.  However, the open-source library ClosedXML that supports development of spreadsheets to the OpenXML standard needs a little tweaking and more developer contributions.

OPENXML ClosedXML_small3

 

 

A clarification is in order in case you’re confused (and rightly so):  OpenXML is the file-structure standard upon which modern MS Office documents are created.  ClosedXML is an open-source .NET code library (on GitHub) built to more easily implement that standard and allow .NET developers an abstraction against that standard for which they can more easily and efficiently code.  ClosedXML – probably not the best name for the library/project, but it is what was chosen.

A couple of things:

  • Error Handling:  If you generate a formula-string for a cell, and then ascribe the formula-string to the value of that cell, but somehow leave the style of the cell to .Number (because the formula will generate a number), like:
    • worksheet.Cell(rowCount, columnCount - 3).DataType = XLCellValues.Number;
  • …then ClosedXML will not be able to handle it and stack-overflow. Of course, the error handling library/code you implement should can handle this and let you know about it, but that’s not my point.
  • Reference Documentation / Intellisense:  It’s practically non-existent, except a few articles and lots of forum discussion on GitHub.  I find that plain inadequate, especially on the Intellisense side of things.

Another random note on ClosedXML:  If you put a formula-string into a cell, separate Excel function parameters (if applicable to your situation) using commas, not semi-colons (like would be standard in a normal Excel function call). You’ll thank me later. You’re welcome in advance.

So I am putting my money where my mouth is and starting to contribute to work on the ClosedXML library, because I use it fairly frequently and want to make it better, especially so I can use it with less frustration.

Is EPPlus (a competing OpenXML library for spreadsheets) any better on these fronts?  I used it a long time ago, but don’t know the progression of the library.  Start a discussion in the comments and let me know.

LAG/LEAD Demo for SQL Server

-- This demo can only be run on SQL SERVER 2012 or higher because of the LAG/LEAD function calls
USE <database_name>;
GO
CREATE TABLE #LagLeadSource
(
    ID INT IDENTITY(1, 1) NOT NULL ,
    Value INT NOT NULL
);

INSERT INTO #LagLeadSource
( Value )
VALUES ( 123 ),( 234 ),( 345 ),( 456 ),( 567 ),( 678 ),( 789 ),( 890 ),( 901 );

SELECT ID,
       LAG(Value, 4, 0) OVER ( ORDER BY ID ASC) AS Next_x_4_LowestValue,
       LAG(Value, 3, 0) OVER ( ORDER BY ID ASC) AS Next_x_3_LowestValue,
       LAG(Value, 2, 0) OVER ( ORDER BY ID ASC) AS Next_x_2_LowestValue,
       LAG(Value, 1, 0) OVER ( ORDER BY ID ASC) AS NextLowestValue,
       Value AS 'ACTUAL_VALUE',
       LEAD(Value, 1, 0) OVER ( ORDER BY ID ASC) AS NextHighestValue,
       LEAD(Value, 2, 0) OVER ( ORDER BY ID ASC) AS Next_x_2_HighestValue,
       LEAD(Value, 3, 0) OVER ( ORDER BY ID ASC) AS Next_x_3_HighestValue,
       LEAD(Value, 4, 0) OVER ( ORDER BY ID ASC) AS Next_x_4_HighestValue
FROM #LagLeadSource
-- using a WHERE clause here will "break" this code as the dataset will have a row-restriction applied before the LAG/LEAD functions are ever called, rendering them useless

IF OBJECT_ID('tempdb..#LagLeadSource') IS NOT NULL
DROP TABLE #LagLeadSource;