Graeme Reisinger

The Dreaded Startup Repair Loop on Win 7

2011-02-27T17:51:36-07:00:00

For most people, upgrading to Windows 7 has been a relatively painless process.

Not me. I am in the unlucky 1% or less who had a somewhat less pleasant experience. First, I cloned my entire hard drive onto a larger (and much faster) solid state hard drive, only experiencing minimal problems.

Then, I bought the Retail version of Windows 7 Ultimate, took a deep breath and... oh yeah, I almost forgot - BACK UP THE COMPUTER. The next morning I upgraded to Win 7 and everything seemed fine, until...

I rebooted the system, the nice Windows 7 launch graphics come up, it's about to launch and AWWW, are you kidding me?!?! Back to the BIOS splash screen? Next comes the sequence of failure - attempt repair - unable to repair - do you want to wipe your hard drive decisions.

Because I purchased the retail version, a number is provided where I could call Microsoft Tech support. When I did, they instructed me to click "Install" from my installation CD, which did not work. When I tried the "Upgrade" option, it reaches an impasse, saying you have a newer version of Win 7, and thus cannot Upgrade. If you choose "Install" you willl lose everything... files, programs, EVERYTHING. Or at least this is what it tells you. Not willing to take the risk, I took them at their word.

To make things worse, I had installed a new antivirus software application before I realized my system was unstable (Trend Micro Titanium Internet Security), and this was causing additional problems.

One interesting thing, and the only saving grace as it turns out, was that my system WOULD successfully reboot into the OS if I chose to restart it, rather than shut it down. If I chose to shut down, I would have to go through the loop again until I was given the option to restart.

As it turned out, I needed to update my BIOS. I assumed that since I had updated my BIOS a long time ago to settings that were stable under Windows Vista Ultimate x64, Win 7 would adopt the same settings and didn't expect there to be any problems. WRONG.

My BIOS had a setting to halt the boot cycle if various kinds of errors were detected. Windows Vista didn't care about this, but forget it under Windows 7. Needless to say, I immediately corrected that BIOS setting. Next, there were the two separate BIOS settings: enable USB mouse and enable USB keyboard. The only sequence of events that would work were to start my reboot process over from stratch with a hard-wired non-usb keyboard and mouse. Whent the system booted under these settings, it didn't detect any errors due to either the mouse or keyboard, and actually booted for the first time in a long while (let me tell ya, that's an aaamazing experience after fiddling with settings for two entire weekends!)

Next step: leave your old mouse and keyboard connected, but also connect your other two devices (mouse, keyboard) that use USB connections. During the boot cycle, the operating system will not fail due to missing requirements atstartup, and it will then pick up the new drivers necessary to use your new hardware.

If you think you are in the clear here, you are wrong. The next VERY IMPORTANT step is to remember to change your settings in the BIOS upon next startup. Specifically, you will need to change your BIOS to enable USB mouse and USB keyboard input. If you don't, Windows will detect an incompatibility upon the next startup, and you will be stuck once again in the endless cycle of reboot/Startup Repair/reboot/Startup Repair, without ever reaching a successful boot.

Here's the thing - the BIOS and the drivers registered in Win 7 need to match. If they don't, you're going to lose another weekend worrying and fiddling, all the while wondering if you've permanently damaged your hard drive beyond repair.

(Sigh). In the end, things worked out.

I must note that it is saddening to see how many posts there are out there that recommend just doing a clean install, as if it's the only option. How many countless poor souls have lost their data, their backups, their pictures and videos, all for nothing other than the fact that the person giving advice just didn't know what to do at that point?

My advice to you, try having a look at your BIOS settings first and making sure Win 7 can detect the BIOS settings it expects, and also disabling in your BIOS anything that might halt your system boot-up process if it encounters errors.

For additional information, have a look at MicroSoft's explanation of possible errors you may find in the SrtTrail.txt file, which logs boot problems and such:

http://technet.microsoft.com/en-us/library/dd744291(WS.10).aspx

For me, all I found was:

Root cause found:
---------------------------
Unspecified changes to system configuration might have caused the problem.

SQL Server Dynamic Where Clause Column Names

2010-12-29T19:40:12-07:00:00

Let me start this post by saying if you are searching for the capability to do this, there is a very good chance you have problems with your current database architecture.

Having said that, I realize you may not have the option of normalizing your database, and have no choice but to work with what you've got. If this is the case, you may find the following post a useful workaround to whatever problem is causing you to search for a dynamic WHERE clause.

A dynamic WHERE clause might be useful if you wish to search for an ID or a GUID generically from a junction table, but don't know what that particular item or object type (column name) might be, until run time. Or perhaps the information is coming from a denormalized table containing many types of objects, with similar column titles between each. For example, you might need Part ID 345698 and the part name from the dbo.MyJunction table. The next time you run the same query, you may need Assembly ID 43578 and the assembly name from the same dbo.MyJunction table.

I'm going to show you how to accomplish this.

The following example uses a Function, however if you need to use dynamic SQL along with the Execute statement, you will have to use some other kind of object, like maybe a stored proc.

USE [YourDbName]

/****** Object: UserDefinedFunction [dbo].fnDynamicWhereClause ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF EXISTS(

SELECT * FROM SYS.OBJECTS

WHERE name = 'fnDynamicWhereClause'

AND type = 'TF'

)

DROP FUNCTION [dbo].[fnDynamicWhereClause]

CREATE FUNCTION [dbo].[fnDynamicWhereClause](

@ID INT,

@TableName VARCHAR(255)

)

RETURNS @Table TABLE (

ID INT,

Name VARCHAR(255)

)

BEGIN

DECLARE @SQL VARCHAR(MAX)

INSERT INTO @Table VALUES

(

SELECT ID, Name

FROM SomeJunctionTable

WHERE @ID =

CASE UPPER(@TableName)

WHEN 'ASSEMBLY' THEN Assembly

WHEN 'PART' THEN Part

WHEN 'MODULE' THEN Module

END

)

RETURN

END -- End Function

The important thing here is the SQL contained in the WHERE clause. In the end, the SQL executed will look like:

SELECT ID, Name FROM SomeJunctionTable WHERE 345698 = Part

- or -

SELECT ID, Name FROM SomeJunctionTable WHERE 345698 = Assembly

This is slightly different from what you're probably used to, where the column name is declared first, and then the search condition is on the right side of the equals sign:

SELECT ID, Name FROM SomeJunctionTable WHERE Part = 345698

If you attempted to reorder the INSERT statement so the column name comes first, you would receive an error at compile time because SQL Server is unable to determine which column name to associate with the value. If you switch the two, SQL Server is ok with that because the item on the left-hand side of the equals sign is known (for example, a value of 345698), and the value on the right-hand side of the statement can be evaluated to a column name.

While I didn't expect this to work when I was working on it for the first time, I was pleasantly surprised when it did! It saved me a lot of work in the end, so I figured I'd share it with anyone else out there looking for this capability.

SQL Server Dynamic SQL - A Comprehensive Example

2010-06-15T21:32:51-06:00:00

With Dynamic SQL, one stored proc can do the work of many...

Over the years, I have worked with a variety of different clients. It seems that every client has unique and different needs. In some situations, you may find that dynamic sql fills the void better than other solutions available to you.

One scenario I have encountered in the past is one where the client wants the capability to run aggregate information at run time, but for any start month and number of time periods. There are many ways to accomplish this, but assuming you are in the situation where you are required to accomplish this task in the SQL Server environment itself, I will show you how to do that, using the Adventureworks database as an example from which you might develop your own solution tailored to your business environment.

Consider the following SQL:

SELECT OrderDate, SalesPersonID, COUNT(SalesPersonID) AS SalesBySalesPersonThisMonth

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

GROUP BY SalesPersonID, OrderDate

ORDER BY SalesPersonID, OrderDate

This will return a count of all sales made by all sales people within a given month, oriented vertically. This isn't very human-friendly, however, as it requires the reader to pick through all the data, if for example he/she wants to see a comprehensive picture of all sales for sales person 278.

A more readable format would be to display the data horizontally, as such:

USE AdventureWorks

WITH EmployeeSalesByMonth (OrderDate, SalesPersonID, SalesBySalesPersonThisMonth) AS

(

SELECT OrderDate, SalesPersonID, COUNT(SalesPersonID) AS

SalesBySalesPersonThisMonth

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

GROUP BY SalesPersonID, OrderDate

)

SELECT * FROM

(SELECT OrderDate, SalesPersonID, COUNT(SalesPersonID) AS

SalesBySalesPersonThisMonth

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

GROUP BY SalesPersonID, OrderDate

HAVING MIN(OrderDate) = '7/1/2001'

--ORDER BY SalesPersonID, OrderDate

) AS ESM1

LEFT OUTER JOIN EmployeeSalesByMonth AS ESM2

ON ESM1.SalesPersonID = ESM2.SalesPersonID

AND MONTH(ESM1.OrderDate) + 1 = MONTH(ESM2.OrderDate)

AND YEAR(ESM1.OrderDate) = YEAR(ESM2.OrderDate)

LEFT OUTER JOIN EmployeeSalesByMonth AS ESM3

ON ESM2.SalesPersonID = ESM3.SalesPersonID

AND MONTH(ESM2.OrderDate) + 1 = MONTH(ESM3.OrderDate)

AND YEAR(ESM2.OrderDate) = YEAR(ESM3.OrderDate)

ORDER BY ESM1.SalesPersonID, ESM1.OrderDate

Putting together your stored procedure in such a manner will be relatively straightforward from this point onward. However what it is likely your client will show this new report to his manager, and the manager might request the same report, while allowing him to choose any number of months he wishes. You could either create a stored procedure for every number of months imagineable (imagine creating the proc that returns five years of data!). Or, you could create one stored procedure that creates this information dynamically.

To do this, you will need to accept as parameters a start date, and number of months the client wishes to run the report for. Once you have this information, you will assemble the query within your stored procedure, and then finally execute it using the (implied) EXECUTE IMMEDIATE functionality of SQL Server:

USE AdventureWorks

IF EXISTS

(SELECT * FROM sys.objects

WHERE type = 'P'

AND name = 'ProcSalesReportDynamic')

DROP PROCEDURE dbo.ProcSalesReportDynamic

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE dbo.ProcSalesReportDynamic (

@StartDate DATETIME,

@NumberOfMonths INT

)

BEGIN

SET NOCOUNT ON;

DECLARE @SQL VARCHAR(MAX);

DECLARE @CurrentMonth INT;

SET @CurrentMonth = 1;

SET @SQL = 'WITH EmployeeSalesByMonth (OrderDate, SalesPersonID, SalesBySalesPersonThisMonth) AS

(

SELECT OrderDate, SalesPersonID, COUNT(SalesPersonID) AS SalesBySalesPersonThisMonth

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

GROUP BY SalesPersonID, OrderDate

-- ORDER BY SalesPersonID, OrderDate

)

SELECT * FROM

(SELECT OrderDate, SalesPersonID, COUNT(SalesPersonID) AS SalesBySalesPersonThisMonth

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

GROUP BY SalesPersonID, OrderDate

HAVING MIN(OrderDate) = ''' + CAST(@StartDate AS VARCHAR(50)) + '''

--ORDER BY SalesPersonID, OrderDate

) AS ESM1';

WHILE @CurrentMonth < @NumberOfMonths

BEGIN

SET @SQL = @SQL + '

LEFT OUTER JOIN EmployeeSalesByMonth AS ESM'

+ CAST(@CurrentMonth + 1 AS VARCHAR(2))

+ '

ON ESM'

+ CAST(@CurrentMonth AS VARCHAR(2))

+ '.SalesPersonID = ESM'

+ CAST(@CurrentMonth + 1 AS VARCHAR(2))

+ '.SalesPersonID

AND MONTH(ESM'

+ CAST(@CurrentMonth AS VARCHAR(2))

+ '.OrderDate) + 1 = MONTH(ESM'

+ CAST(@CurrentMonth + 1 AS VARCHAR(2))

+ '.OrderDate)

AND YEAR(ESM'

+ CAST(@CurrentMonth AS VARCHAR(2))

+ '.OrderDate) = YEAR(ESM'

+ CAST(@CurrentMonth + 1 AS VARCHAR(2))

+ '.OrderDate)';

SET @CurrentMonth = @CurrentMonth + 1;

END -- End WHILE

SET @SQL = @SQL + '

ORDER BY ESM1.SalesPersonID, ESM1.OrderDate'

--PRINT @SQL;

EXEC (@SQL);

END; --End dbo.ProcSalesReportDynamic

Not bad, right? (At least it beats writing two, three, twelve, or sixty stored procedures!)

When you execute this for many months, you may notice that the columns begin to become confusing. Once a column name repeats, it contains the same name for the sequence to be repeated for the next month; the only way to tell which column is which is to look at the data itself. I thought about correcting this, but for the sake of keeping this post as short as possible, as well as giving you the reader a chance to update the dynamically created SQL as you see fit, I thought it best to leave this correction out.

I will give you a hint, though, and that is to change the dynamically created 'SELECT *' into an explicitly named column list.

Lastly, beware the drawbacks of using dynamic SQL:

Your code can blow up at run time. If you're using SQL Server 2000 or earlier, the max size limit of a VARCHAR is 8000. That means the maximum size of your dyamically executed SQL will be 8000 characters long. A client or consumer that attempts to run a proc that terminates prematurely because it has run out of storage space for the string will receive some sort of SQL error. While you may be familiar with the error, the consumer or client will not.
If you are using SQL Server 2005 or later, you will have the VARCHAR(MAX) data type available to you. This is both a good and a bad thing. You will not have the size limitation you would normally encounter with SQL Server 2000. (Well, the string can only be 2Gb long - but is that really a limitation?) Instead, you could bump up against the limitation that your SQL takes one and a half years to execute; the client or consumer could hang indefinitely.

Either (perhaps both) of the scenarios described above are ones you should plan for, so you can avoid getting dirty looks from your boss. If your SQL creeps into the territory where it is beginning to take a long time to run, you may find yourself researching ways to improve your statement either through indexing or more likely optimization of the query itself.

Business Logic: Should it exist in the code or in the database?

2010-06-27T12:56:06-06:00:00

Some time ago, I found myself in the difficult situation of trying to explain why business logic shouldn't be placed in the database, but instead in the code (preferably in a Business Logic layer or Object Model layer).

The situation seemed so clear to me. It was striking how differently others in the team saw it.

You may find yourself in a situation similar to this one, where you are working on a database that contains *many* business rules. Perhaps it started out as a set of tables that very closely mirrored the Graphical User Interface presented to your users. It may have additionally contained an initial set of rules that dictated whether additional rows in other tables were to be updated.

Now, throw in to the mix the fact that the customer realized the application almost meets their needs, but not quite. Because of one small change in the business rules governing workflow in their ofice, the existing design your team had put into place now has to pretty much go out the window. (Well, maybe just migrated to a better model and then later abandoned).

You may find yourself thinking back to the original design, and how much more flexible it would be now to change, if only the database were used solely for the porpose of efficient storing and retrieval of data. Additionally, you may have further complications to unravel due to the fact that the database more resembles the objects in the Graphical User Interface, rather than a relational model that is unknowing and uncaring about what ever GUI object may display the data.

I did some searching around on the internet, and apparently this is a very common problem. One article in particular by Chad Z. Hower caught my eye:

http://www.codeproject.com/KB/architecture/DudeWheresMyBusinessLogic.aspx

Pay particular attention to his definition of 'Business Logic'. If I were to try to capture his definition into one sentence, I would describe business logic as "any decision, formatting, additional work, or decision-making that can be encumbered upon a data transaction, outside of the normal constraints that exist with in a database".

In his article, Chad outlines a detailed explanation of pros and cons associated with this very decision. Surprisingly, in his post I actually found identically worded explanations (compared to those I have heard before) as to why placing business logic into the database is a good thing. I must clarify: he DOES NOT espouse this practice (and neither do I!), but I'm saying I've heard the same explanations before, verbatim.

This perspective (in favor of placing logic in the database) can be a very frustrating one to refute, in part because it seems so simple for people to list the obvious, more immediate benefits. Warning: if you are reading this post right now, you have probably heard one or more of these benefits debated in discussions already at work.

You may hear that putting the logic into the database:

Allows your team to save time by avoiding a testing cycle (for e.g. when the team delivers a software patch or version upgrade to existing components).
Is "easier" than having to locate and maintain it in the code base somewhere
Is less buggy, because it requires less testing
Gives an additional layer of protection against bugs, because it does not solely rely on a bug-free code base

Pretty simple, obvious benefits, right?

The cons are much more subtle. And in my opinion, they are much more sinister than the pros, because their consequences can be paralyzing when it comes time to make changes to the code, the GUI, the database, or a combination of these.

When logic is placed in the database, it is often in one (or several) additional locations outside of the database. This is often the result of a "just in case" approach put into action by a developer, because he is unsure whether future changes might be applied to the database that can break the code segment he is currently working on. He is thinking to himself, "Just to be sure, I better place the same logic in the code, in case the rule gets changed in the database..."
There may be several copies of the same logic, encapsulated in many database objects that serve data to different layers of the application. Or perhaps several slightly different versions of the same object. These things evolve over time - unfortunately, it's up to you to figure them out :-/.
Rather than relying solely on constraints to uphold data integrity, unknown (probably countless) layers of subtle rules are being applied at run time to update the data in the database, as it is parsed. Over time, these rules make the database fragile to the expectations placed upon the data model itself. Meaning: "You should have known that you can only apply 'Y' for yes, 'N' for no, and 'M' for maybe." When the paradigm shifts, the data model (and all its associated rules) become obsolete almost instantly. What I mean by this is, the database becomes expectant to certain kinds of specific updates. It will eventually end up breaking the business model when a new developer comes along, expecting nothing out of the ordinary when updating the database. He didn't know that only data types of char(1) and only values of 'Y', 'N', or 'M' could be applied.
Which team member was it that worked on those stored procedures that tied all the logic together correctly?
Does that team member even still work here?

These cons build on each other. If not realized, they can either paralyze a team that wishes to make changes to their application, or force them to abandon the old model and start anew, with a fresh application code base, and (sigh) a fresh database.

The data will still need to be migrated however.

Graeme

Stub out your project using Visual Studio's Class Diagram

2010-07-11T12:00:51-06:00:00

A while back I was tasked with creating a class diagram for one of the applications created by my company. While I knew there was a possibility I might have to manually create the diagram by hand, I knew this method would probably consume a lot of time. I also knew there were third-party tools out there that automate some of these tasks.

The problem was, we didn't have any of these third-party diagramming tools. There are a myriad number of reasons why a company might choose not to purchase these kinds of productivity-enhancing tools; at my workplace the deciding factor seems to be lack of time and money.

I decided to see if Visual Studio had anything to offer, expecting it to fall short in the richness of its features and capabilities. I was pleasantly surprised to discover the opposite: Visual Studio offers quite a nice little package for class diagramming. ***Note, the version of Visual Studio I'm referring to is VS 2005. If I find better tools (third-party or inlcuded in subsequent releases of Visual Studio), I will update this post with my findings.

Here are a few of the features offered by the Visual Studio 2005 class diagram tool:

You can use it to stub out your code, or ...
You can capture all the class information for an already existing project without having to do any real work. More on this later.
The class designer stays up-to-date in real time. No need to fuss with keeping everything in synch all the time, excepting perhaps the latest version of class diagram on people's desks.
The VS class designer tool understands (and visually illustrates) all of the following:
- Object Properties, members, methods, events, delegates, and nested types.
- Object inheritance.
- Class types (abstract, interface, sealed)
- Class metadata, for displaying (or creating) the documentation and comments associated with your code
- Object relationships (I'm referring to the arrows depicted on most UML diagrams to express relationship between two or more objects)
The class diagram you've created is exportable to .pdf or image types, so you can print and distrube copies of it freely, without having to install Visual Studio on every consumer's machine.

The nice thing about this tool is it is a big time-saver. Whether you're coming from the design side of things, or whether you're wanting a class diagram for an existing code base, Visual Studio greatly reduces the time and effort required to accomplish either of these tasks.

I'll update this post later with pictures and a quick run-through of both processes. For now, I hope you might find this article by Microsoft very helpful in the mean time.

http://msdn.microsoft.com/en-us/library/aa288743(VS.71).aspx

I hope you enjoy exploring the many features provided by Visual Studio class diagram tool!

SQL Server Date Comparison Functions

2010-06-13T13:56:20-06:00:00

A few months ago, I found myself working with a repetitive cursor that looped until the data had been manipulated enough times that it was finally correct. The cursor was heavily dependent upon dates, every time requiring the earlier of two (or several) dates in one stored procedure, while requiring the later of two dates in another stored procedure.

In short what I needed was a function that would allow me to perform the following evaluation:

WHERE MAX(Date1, Date2) < @SomeDate

The problem is, the MAX() function in SQL Server does not perform this functionality.

So, I set out to put these functions together. They are titled: EarlierOf() and LaterOf().

/**********************************************************

EarlierOf.sql

**********************************************************/

/**********************************************************

Return the later of two DATETIME variables.

Parameter 1: DATETIME1

Parameter 2: DATETIME2

Works for a variety of DATETIME or NULL values. Even

though comparisons with NULL are actually indeterminate, we

know conceptually that NULL is not earlier

or later than any other date provided.

SYNTAX:

SELECT dbo.EarlierOf('1/1/2000','12/1/2009')

SELECT dbo.EarlierOf('2009-12-01 00:00:00.000','2009-12-01 00:00:00.521')

SELECT dbo.EarlierOf('11/15/2000',NULL)

SELECT dbo.EarlierOf(NULL,'1/15/2004')

SELECT dbo.EarlierOf(NULL,NULL)

**********************************************************/

USE AdventureWorks

IF EXISTS

(SELECT *

FROM sysobjects

WHERE name = 'EarlierOf'

AND xtype = 'FN'

)

BEGIN

DROP FUNCTION EarlierOf

END

CREATE FUNCTION EarlierOf (

@Date1 DATETIME,

@Date2 DATETIME

)

RETURNS DATETIME

BEGIN

DECLARE @ReturnDate DATETIME

IF (@Date1 IS NULL AND @Date2 IS NULL)

BEGIN

SET @ReturnDate = NULL

GOTO EndOfFunction

END

ELSE IF (@Date1 IS NULL AND @Date2 IS NOT NULL)

BEGIN

SET @ReturnDate = @Date2

GOTO EndOfFunction

END

ELSE IF (@Date1 IS NOT NULL AND @Date2 IS NULL)

BEGIN

SET @ReturnDate = @Date1

GOTO EndOfFunction

END

ELSE

BEGIN

SET @ReturnDate = @Date1

IF @Date2 < @Date1

SET @ReturnDate = @Date2

GOTO EndOfFunction

END

EndOfFunction:

RETURN @ReturnDate

END -- End Function

---- Set Permissions

--GRANT SELECT ON EarlierOf TO UserRole1

--GRANT SELECT ON EarlierOf TO UserRole2

--GO

The inverse of this function is only slightly different.

/**********************************************************

LaterOf.sql

**********************************************************/

/**********************************************************

Return the later of two DATETIME variables.

Parameter 1: DATETIME1

Parameter 2: DATETIME2

Works for a variety of DATETIME or NULL values. Even

though comparisons with NULL are actually indeterminate, we

know conceptually that NULL is not earlier

or later than any other date provided.

SYNTAX:

SELECT dbo.LaterOf('1/1/2000','12/1/2009')

SELECT dbo.LaterOf('2009-12-01 00:00:00.000','2009-12-01 00:00:00.521')

SELECT dbo.LaterOf('11/15/2000',NULL)

SELECT dbo.LaterOf(NULL,'1/15/2004')

SELECT dbo.LaterOf(NULL,NULL)

**********************************************************/

USE AdventureWorks

IF EXISTS

(SELECT *

FROM sysobjects

WHERE name = 'LaterOf'

AND xtype = 'FN'

)

BEGIN

DROP FUNCTION LaterOf

END

CREATE FUNCTION LaterOf (

@Date1 DATETIME,

@Date2 DATETIME

)

RETURNS DATETIME

BEGIN

DECLARE @ReturnDate DATETIME

IF (@Date1 IS NULL AND @Date2 IS NULL)

BEGIN

SET @ReturnDate = NULL

GOTO EndOfFunction

END

ELSE IF (@Date1 IS NULL AND @Date2 IS NOT NULL)

BEGIN

SET @ReturnDate = @Date2

GOTO EndOfFunction

END

ELSE IF (@Date1 IS NOT NULL AND @Date2 IS NULL)

BEGIN

SET @ReturnDate = @Date1

GOTO EndOfFunction

END

ELSE

BEGIN

SET @ReturnDate = @Date1

IF @Date2 > @Date1

SET @ReturnDate = @Date2

GOTO EndOfFunction

END

EndOfFunction:

RETURN @ReturnDate

END -- End Function

---- Set Permissions

--GRANT SELECT ON LaterOf TO UserRole1

--GRANT SELECT ON LaterOf TO UserRole2

--GO

The interesting thing about these function is their simplicity and the built-in NULL handling functionality. They are interesting, because it seems like something should already exist in SQL Server to provide this functionality. From a different vantage point, if you create this functionality and it is easy to use (ideally, intuitive or self-explanatory), you have made a successful contribution.

Interesting is good. Self-explanatory, or intuitive is FAR better. Happy coding!

Graeme

SQL Server Split() Function

2010-06-06T20:33:50-06:00:00

Diversify Your Database!

Ever wanted a dbo.Split() function, but not had the time to debug it completely?

Let me guess - you are probably working on a stored procedure with 50 or more parameters; two or three of them are parameters of differing types, while the other 47 or so all of the same type (id1, id2, id3, id4, id5...). Worse, you've found several other similar stored procedures with the ONLY DIFFERENCE being the number of like parameters taped to the end of the parameter list.

If this is the situation you find yourself in now, you may be wondering, "why am I working with all these different copies of what is basically the same stored procedure, and why am I having to maintain changes in several different places? Can't I have one stored procedure that accomplishes the job of all three?

My answer to you: YES!

Here is the Split() function I've created.


/******************************************************************* Split.sql *****************************************************************/ /******************************************************************* Split a delimited string into sub-components and return them as a table. Parameter 1: Input string which is to be split into parts. Parameter 2: Delimiter which determines the split points in input string. Works with space or spaces as delimiter. Split() is apostrophe-safe. SYNTAX: SELECT * FROM Split('Dvorak,Debussy,Chopin,Holst', ',') SELECT * FROM Split('Denver\|Seattle\|San Diego\|New York', '\|') SELECT * FROM Split('Denver is the super-awesomest city of them all.', ' ') SELECT * FROM Split('abcdefghijklmnopqrstuvwxyz', 'hijk') ********************************************************************/ USE AdventureWorks GO IF EXISTS (SELECT FROM sysobjects WHERE xtype = 'TF' AND name = 'Split' ) BEGIN DROP FUNCTION Split END GO CREATE FUNCTION Split ( @InputString VARCHAR(8000), @Delimiter VARCHAR(50) ) RETURNS @Items TABLE ( Item VARCHAR(8000) ) AS BEGIN IF @Delimiter = ' ' BEGIN SET @Delimiter = ',' SET @InputString = REPLACE(@InputString, ' ', @Delimiter) END IF (@Delimiter IS NULL OR @Delimiter = '') SET @Delimiter = ',' --INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic --INSERT INTO @Items VALUES (@InputString) -- Diagnostic DECLARE @Item VARCHAR(8000) DECLARE @ItemList VARCHAR(8000) DECLARE @DelimIndex INT SET @ItemList = @InputString SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0) WHILE (@DelimIndex != 0) BEGIN SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex) INSERT INTO @Items VALUES (@Item) -- Set @ItemList = @ItemList minus one less item SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex) SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0) END -- End WHILE IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString BEGIN SET @Item = @ItemList INSERT INTO @Items VALUES (@Item) END -- No delimiters were encountered in @InputString, so just return @InputString ELSE INSERT INTO @Items VALUES (@InputString) RETURN END -- End Function GO ---- Set Permissions --GRANT SELECT ON Split TO UserRole1 --GRANT SELECT ON Split TO UserRole2 --GO

The syntax is basically as follows:

SELECT <fields>
FROM Table 1
JOIN Table 2
ON ...
JOIN Table 3
ON ...
WHERE
LOGICAL CONDITION A
AND LOGICAL CONDITION B
AND LOGICAL CONDITION C
AND TABLE2.Id IN (SELECT * FROM Split(@IdList, ','))

@IdList is a parameter passed into the function, and the comma (',') is the delimiter you have chosen to split the parameter list on.

You can also use it like this:

SELECT <fields>
FROM Table 1
JOIN Table 2
ON ...
JOIN Table 3
ON ...
WHERE
LOGICAL CONDITION A
AND LOGICAL CONDITION B
AND LOGICAL CONDITION C
HAVING COUNT(SELECT * FROM Split(@IdList, ',')

Similarly, it can be used in other aggregate functions at run-time:

SELECT MIN(SELECT * FROM Split(@IdList, ','), <fields>
FROM Table 1
JOIN Table 2
ON ...
JOIN Table 3
ON ...
WHERE
LOGICAL CONDITION A
AND LOGICAL CONDITION B
AND LOGICAL CONDITION C
GROUP BY <fields>

Now that I've (hopefully effectively) explained the benefits to using this function and implementing it in one or more of your database objects, let me warn you of a caveat that you are likely to encounter. You may have a team member who waits until the right moment to ask you a pointed question: "Doesn't this function just do the same thing as using the IN function? Why didn't you just use that instead? In other words, why bother with this function?"

What's happening is, one or more team members has failed to understand the reason for implementing this kind of function in the first place. (Note: this is THE MOST IMPORTANT ASPECT OF THIS POST).

Allow me to outline a few pros to implementing this function, so you may effectively parry this question. Touche.

1) Code consolidation. You don't have to maintain what is basically the same code and logic, but with varying numbers of the same parameter in several SQL objects.

I'm not going to go into the cons related to using this function, because the afore mentioned team member is probably more than adept than I at pointing these out. Remember, the real positive contribution is you are decreasing the liklihood that your team fails to update all (x) duplicate copies of what are basically the same stored procedure, and so on... This is the classic downside to duplicate code. It is a virus, and you should kill it.

Rather than fending off criticism, you might be better off responding with your own question: "Would you rather maintain the same logic in multiple different stored procedures, and hope that the team doesn't forget to always update all of them at the same time?". In his head, your team member might be thinking, "YES, I would like to maintain several different copies of the same stored procedure", although the response you get is anyone's guess.

2) Added flexibility - you can use the Split function elsewhere, and for splitting your data in different ways. Plus, you can use any kind of delimiter you wish. How can you know today the ways in which you might want to examine your data tomorrow? Segue to my next point.

3) Because the function takes a delimiter parameter, you can split the data in any number of ways. This greatly increases the utility of such a function and enables your team to work with the data in a variety of different ways in the future. You can split on a single char, symbol, word, or group of words. You can split on spaces. (The list goes on... test it out).

Finally, you can dynamically define the behavior of a stored procedure (or other SQL object) at run time, through the use of this function. Rather than have several objects that accomplish almost the same thing, why not have only one instead?

High-Powered Sites for low Cost

2010-05-23T23:45:25-06:00:00

Ahh, I am experiencing the intimidation of my very first post - visible by the whole world.

Ok, here goes.

This first post is nothing exceptional. It is simply a recommendation based (fittingly, I suppose) upon the job search you may be gearing up for. I find myself in this very situation right now. And, I will take my own recommendation after posting this entry.

Job-Seekers:

To the left you will notice two links under "Recommended Learning". I have found these links to be invaluable when it comes to re-tooling, re-familiarizing, or otherwise resharping my skills when looking for that next job.

Often, you will find job-postings with the text, usually posted after a laborious list of qualifications indicating the company's desire to hire candidates who know what they are doing: "...Looking for a candidate who can hit the ground running...". The interesting thing about this post to me is I've encountered many individuals who, after speaking and working with them for some time, I've realized are perfectly capable of hitting the ground running - and FAST. But what if they speed off in the wrong direction?

The next time you spearhead a major task in your job, ask yourself: Am I headed in the wrong direction? There are many ways to do this. In fact, I've found in this new field there are more ways to steer your project in the wrong direction than there are good ones. You might be carrying forward a pattern from an loder language (C++?) that does not work in the newer language. Or, perhaps, you are following the advice of an 'expert' who prefers to stay within his comfort zone, rather than explore newer, more innovative or practical solutions.

In my own personal past experiences, regardless of the reason I may have gotten started heading off into the weeds, the clue I learned to pay attention to more and more is that nagging voice in the back of my mind. Though annoying, usually I end up giving in to what it suggesting, which is: "There has GOT to be a better way of doing this..." I would compare this to the "bad smells"described in the SW development community.

I don't want to suggest that every one of my posts will fall into the "right direction" category, however I do think a healthy dose of introspection of the pros and cons will always be beneficial before you set off.

That said, allow me to expound on the previously mentioned links.

These web sites are invaluable. They demonstrate the capabilities of existing as well as new and upcoming tools available in several IDE's. I've viewed many tutorials in LearnVisualStudio.NET, and only one or two so far in TrainingSpot, however I've been delighted in their simplicity and straightforward approach to proper usage of the particular tool or concept being discussed. They have not (so far in my experience) demonstrated ways in which to use the tools that become cumbersome, impractical, or error-prone.

Each website has step-by-step videos that can be paused, replayed, and most importantly, they are done in real time. As the author is typing, the viewer gets to experience the coding experience from a first-person perspective, including syntax errors, unexpected behaviors, IDE setup idiosyncracies, everything. A subtle value I've gained from these videos is that a certain degree of confusion and introspection is normal when working with new tools and exploring new paths.

They (as well as your own experience) are not to be feared, but enjoyed. I highly recommend them.

Good work, guys!