Why/How To Drop SQL Entities If They Exist?

Sometimes while writing SQL scripts purposed for fixing production issues you face some unexpected problems. These problems may be due to the existance of discrepancy between client's environments caused by missing some scripts and may be by other reasons.

In these cases you have to make sure that your scripts are somehow adaptable and can be run on all environments without raising errors and without causing any unexpected behaviors. These errors like trying to update a stored procedure which doesn't exist and so on.

The best practice in this situation which works perfectly most of the times is to first drop the entity you wish to modify if it already exists and then fully re-create it as you wish. This allows you to avoid many unexpected issues you can face.

So, below you will find the code which checks for the existance of some entities and accordingly drops these entities.


Tables

--Check if table exists in a database
IF EXISTS
(
 SELECT *
 FROM dbo.sysobjects
 WHERE id = object_id(N'[dbo].[TableName]')
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)

DROP TABLE [dbo].[TableName]
GO

Views

--Check if view exists in a database
IF EXISTS
(
 SELECT *
 FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_NAME = 'ViewName'
)

DROP VIEW [dbo].[ViewName]
GO

Stored Procedures

--Check if stored procedure exists in a database
IF EXISTS
(
 SELECT *
 FROM dbo.sysobjects
 WHERE id = object_id(N'[dbo].[StoredProcedureName]')
 AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)

DROP PROCEDURE [dbo].[StoredProcedureName]
GO

User-defined Functions

--Check if user-defined function exists in a database
IF EXISTS
(
 SELECT  *
 FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_NAME = 'FunctionName'
 AND ROUTINE_SCHEMA = 'dbo'
 AND ROUTINE_TYPE = 'FUNCTION'
)

DROP FUNCTION [dbo].[FunctionName]
GO

User-defined Table Types

--Check if user-defined table type exists in a database
IF EXISTS
(
 SELECT * 
 FROM sys.types 
 WHERE is_table_type = 1 
 AND name = 'TableTypeName'
)

DROP TYPE [dbo].[TableTypeName]
GO

Table Indexes

--Check if table index exists in a database
IF EXISTS
(
 SELECT * 
 FROM sys.indexes 
 WHERE name='IndexName'
 AND object_id = OBJECT_ID('TableName')
)

DROP INDEX [IndexName] ON [dbo].[TableName]
GO

Hope you find this useful.

Read More

How To Apply Recursive SQL Selections On Hierarchical Data

Sometimes we work on systems where a hierarchical data structure exists on some entities like employees and their managers. Both employees and managers can be called employees but there is a self join relation between them as each employee must have a manager. I think we are all familiar with this pattern.

Also, I think we all faced the situation when we need to get the info about each employee and his/her direct manager. At this point we used to join between the employees (child) table and itself (parent) on the condition that the parent id of the child is equal to the id of the parent. This is good.

But, what about if we need to get the hierarchical tree of managers of a certain employee not just his direct manager. It seems as we just need to the same join but more than one time till we are up all the way to the head manager. This is somehow logical but how can we do this number of joins and we don't know the number of levels up to the head manager?!!!

This introduces the problem or the challenge we are going to find a solution to right now. The answer is simply recursive.

Before going into more details, let's simplify the meaning of the expression "Recursive". It means that we have some logic and we want this logic to be repeated more than one time till a certain condition is satisfied (or not satisfied), at this point the execution should be stopped to get the result of the whole process.

So, is this what we really need to find a solution for what we have on hand right now? I think it is so let's now see some code and try to simplify things a little bit.

First, let's assume that we have a hierarchical tree of departments in a certain company. The tree is as in the image below

Now, we need to write a select statement which will be converted to a stored procedure later. This select statement will select all parent departments from a certain department up to the head department which is department #1 as in the picture.

So, if we assume that the certain department we want to investigate is department #6, then we should have a table having the results as follows
6, 5
5, 2
2, 1
1, 1

As explained before this can't be achieved using single join operations, so lets now check the proposed solution.

Note
You can see the code already written and ready for execution on this link


First we will create our "Departments" table. This table includes each department and a reference to its parent (self join).

CREATE TABLE Departments
(
  [ID] [int] NOT NULL
  , Name nvarchar(max)
  , ParentID int
);

Second, let's fill some data into the "Departments" table to work on.

INSERT INTO Departments (ID, Name, ParentID)
VALUES(1, 'Dept1', 1)
,(2, 'Dept2', 1)
,(3, 'Dept3', 1)
,(4, 'Dept4', 1)
,(5, 'Dept5', 2)
,(6, 'Dept6', 5);

Now, we are ready to work on the select statement.

WITH AllDepartments([ChosenDept], [ChildID], [ChildName], [ParentID], [ParentName])
AS
(
 SELECT Child.ID AS [ChosenDept]
 , Child.ID AS [ChildID]
 , Child.Name AS [ChildName]
 , Parent.ID AS [ParentID]
 , Parent.Name AS [ParentName]
 FROM Departments AS Child
 LEFT OUTER JOIN Departments AS Parent
 ON Child.ParentID = Parent.ID
 
 UNION ALL
 
 SELECT AllDepartments.ChosenDept AS [ChosenDept]
 , AllDepartments.ParentID AS [ChildID]
 , AllDepartments.ParentName AS [ChildName]
 , NewParent.ParentID AS [ParentID]
 , NewParentInfo.Name AS [ParentName]
 FROM AllDepartments
 INNER JOIN Departments AS NewParent
 ON AllDepartments.ParentID = NewParent.ID
 AND AllDepartments.ParentID <> AllDepartments.ChildID
 INNER JOIN Departments as NewParentInfo
 ON NewParent.ParentID = NewParentInfo.ID
)

SELECT AllDepartments.[ChildID]
, AllDepartments.[ChildName]
, AllDepartments.[ParentID]
, AllDepartments.[ParentName]
FROM AllDepartments
WHERE ChosenDept = 6;

As you can see in the code above it seems to be a bit complicated but believe me it is not that hard to understand, so let's go through the analysis.

Analysis

1. As you can see in the first line we are using the "with" statement to create what is known as "Common Table Expression" (aka: CTE)
2. The CTE is an expression by which we a create a table using a select statement to be used as a common table on which other later select statements depend
3. CTE is not only used with SQL recursion but it is one of the most important applications
4. CTE is defined as "WITH tableName (columnAlias1, columnAlias2, columnAlias3, .....)
5. Inside the CTE body, there is two select blocks with a "UNION ALL" operator in the middle
6. The select statement on the top, before the "UNION ALL", is a select statement which provides the data to start with as a seed for the recursion process to start with
7. This select statement is only called once at first then the recursion process will depend on its results to work on
8. In our case, we need this select to get all the info we need about each department like
1. Department ID
2. Department Name
3. Parent Department ID
4. Parent Department Name (needs a self join)
9. So, it is a simple self join as we see but you can notice that we added an extra column over the 4 columns above, so what about this column?
10. This column represents the department we choose for investigation;
1. We need the final result (after the recursion is fully executed) to include each department and all parent departments up to the head one
2. So, for each department upon investigation we have multiple records (as in our example above, to investigate department #6, we had records {(6, 5), (5, 2), (2, 1), (1, 1)}, for #5 we had records {(5, 2), (2, 1), (1, 1)}, ............. so to consolidate all these results into one result set, we should add the department to investigate with each result so for #6 it should be {(6, 6, 5), (6, 5, 2), (6, 2, 1), (6, 1, 1)} and for #5 it should be {(5, 5, 2), (5, 2, 1), (5, 1, 1)} and so on)
3. Then at last when we decide to choose certain department to investigate, we can use this extra column to filter with, so if we need to investigate department #6, we can filter the final result set as follows "WHERE ChosenDept = 6"
11. For the second select statement below, this is the select which represents the recursive opertation
12. To understand what is going on let's imagine that we have the results of the first select statement stored int the CTE we created and try to go through the operation manually just using our minds
13. The results will be
1. {ChosenDept, ChildID, ChildName, ParentID, ParentName}
2. {1, 1, 'Dept1', 1, 'Dept1'}
3. {2, 2, 'Dept2', 1, 'Dept1'}
4. {3, 3, 'Dept3', 1, 'Dept1'}
5. {4, 4, 'Dept4', 1, 'Dept1'}
6. {5, 5, 'Dept5', 2, 'Dept2'}
7. {6, 6, 'Dept6', 5, 'Dept5'}
14.  Let's assume that we are going to investigate "Dept6"
15. Then we will check which department is its parent, it is "Dept5"
16. So now we try to find the department which is parent of "Dept5"
17. This piece of info exists in the "Departments" table
18. This means that we need to join the CTE result set above with the "Departments" table (let's call it "NewParent") on the condition that the "ParentName" column of the CTE result set is equal to the "ID" of the "NewParent" table. All of this to be able to get the parent department of "Dept5"
19. So now in the new select the selected columns will be as follows:
1. "ChildID" will be the "ParentID" of the CTE (because now this is the new child we are trying to find its parent, in this example, it is "Dept5")
2. "ChildName" will be the "ParentName" of the CTE
3. "ParentID" will be the "ParentID" of the "NewParent" table (because this is the direct parent of the current child which is "Dept5" in this example)
4. "ParentName" will be the name of the new parent we got in the column above (this required an extra join cause the name of the parent doesn't exist in the same table)
5. "ChosenDept" will be got from the CTE cause this is the same department we are still investigating from the main CTE
20. Now after we have finished the CTE including the recursion, we will write our final select statement which will get only the results when the "ChosenDept" column of the CTE is equal to 6

That's it. I know that it is not easy to understand it the first time but believe me if you read it one more time you will get it. Try to imagine the whole process in your head as if you are going to do it manual. After short time you will get the common sense of the whole operation and you will be able to understand every detail.

I encourage you to repeat reading the code and the steps more than once and for sure you can search the internet for any other tutorials or resources talking about "Recursive SQL using Common Table Expression".


Other Resources

1. Recursive Queries Using Common Table Expressions
2. SQL SERVER – Simple Example of Recursive CTE | Journey to SQL Authority with Pinal Dave
3. SQL SERVER – SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop | Journey to SQL Authority with Pinal Dave
4. SQL Anywhere: Example: RECURSIVE UNION

Hope you find this post useful someday :)

Read More

The Difference Between SQL Join Conditions Into "ON" And "WHERE" Clauses

Some of us while writing an SQL select statement with a join between two tables or more may get confused with whether to add a certain condition to the "ON" clause of the join or just add it to the "WHERE" clause as if it is just a filtering condition.

Some people may think that both approaches will return the same results every time. This is not completely true. Yes in some cases both approaches will return the same results but in other cases they won't. Let's check the case below and we will get the whole thing at the end.

Assume that we have two tables:

1. Departments table which includes all departments which exist at a certain company
2. Employees table which includes all employees in the same company

The two tables can be as follows

 

As you can see the "DepartmentId" column in the "Employees" table is a foreign key which references the "deptID" column of the "Departments" table.

Now, before going deep into code let's introduce a very useful online tool which is simply a SQL online simulator/compiler. Using this tool you can write some SQL statements, run them and get results as if you are running on an SQL management studio. I used this tool to prepare and test my queries while writing this post and I really encourage you to give it a try.

So, to use this tool:

1. Browse to http://sqlfiddle.com 
2. In the site tool bar on the upper left side there is a drop down list to choose the SQL engine you wish to use, so choose "MS SQL Server 2008" ...... I was so happy to find "MS SQL Server 2012" in the list :)

Now we are ready to start testing. You will find that you have two wide text areas. The one on the left is where you write your schema building code while the one on the right is where you write your SQL selects.

So, first let's build our schema. To do so, paste the code below into the text area on the left and hit the "Build Schema" button.

create table Departments
(
  [deptID] [int] NOT NULL
  , Name nvarchar(max)
  , CONSTRAINT pk_departments_pid PRIMARY KEY(deptID)
);

create table Employees
(
  [empID] [int] IDENTITY(1,1) NOT NULL
  , Name nvarchar(max)
  , DepartmentId int
  , CONSTRAINT pk_employees_pid PRIMARY KEY(empID)
  , CONSTRAINT FK_employees_Department FOREIGN KEY (DepartmentId) 
    REFERENCES Departments (deptID)
);

So now we have our two tables as in the picture

Now, let's test our two approaches and see what happens. Assume that for some reason we only care about all departments except department with deptID = 3.


Adding Condition To The "WHERE" Clause
Copy and paste the code below into the text area on the right, on the "Run SQL" button there is a small arrow pointing down, hit this arrow and choose "Tabular Output" and finally hit "Run SQL".

insert into Departments (deptID, Name)
values(1, 'Dept1'),(2, 'Dept2'),(3, 'Dept3'),(4, 'Dept4'),(5, 'Dept5');

insert into Employees (Name, DepartmentId)
values('Ahmed', 1),('Tarek', 2),('Hasan', 2),('Saleh', 2),('Selim', 3);

select e.Name as empName
, d.Name as deptName
from Employees as e
left outer join Departments as d
on e.DepartmentId = d.deptID
where d.deptID <> 3;

After running the code you will get the results as in the picture below

As you can see there should be the record (Selim, Dept3) but it is filtered out because we added a condition to the "WHERE" clause which states that we only need records matching to all departments except the departments which have the "deptID" column equal to "3".


Adding Condition To The "ON" Clause
Copy and paste the code below into the text area on the right, on the "Run SQL" button there is a small arrow pointing down, hit this arrow and choose "Tabular Output" and finally hit "Run SQL".

insert into Departments (deptID, Name)
values(1, 'Dept1'),(2, 'Dept2'),(3, 'Dept3'),(4, 'Dept4'),(5, 'Dept5');

insert into Employees (Name, DepartmentId)
values('Ahmed', 1),('Tarek', 2),('Hasan', 2),('Saleh', 2),('Selim', 3);

select e.Name as empName
, d.Name as deptName
from Employees as e
left outer join Departments as d
on (e.DepartmentId = d.deptID and d.deptID <> 3);

After running the code you will get the results as in the picture below

As you can see there should be the record (Selim, Dept3) but instead we have the record (Selim, NULL) and this is because we added the condition to the "ON" clause to filter out the departments with "deptID" equal to "3". So, just before the joining the table "Departments" is filtered according to the condition and then the joining is performed. Since the joining is outer left joining (knowing that the "Employees" table is the one on the left), then whatever values in the "Departments" table are all records in the "Employees" table will return. This time the employee "Selim" has no corresponding department in the "Departments" table because it is already filtered out by the condition just before the joining occurred.


Conclusion
When adding the condition to the "WHERE" clause, the joining is executed first and the filtering based on the condition is the last thing to happen. While when adding the condition to the "ON" clause the filtering is applied to the tables first then the joining happens.

So, in which cases do both approaches return the same results? This happens in case of inner joins as in inner joins if a record in the "Employees" table doesn't have a corresponding record in the "Departments" table then this record will be filtered out. So, even if the condition is added to the "ON" clause, the department record is filtered out before joining, joining is applied, then the joining itself will filter out the whole employee record because it has no corresponding department.


That's it. Hope you find this post useful.

Read More

How To Reconstruct String Sections From Concatenated String Format

 

Sometimes you find yourself in a need to pass some values from one module to another beside being restricted to using a string format not a fully qualified object. This restriction may be due to performance purposes or some technical restrictions like using a dictionary object which only provides you with a "key" and "value" pairs.

There is more than one way to deal with such situation like using serialization. But, I think that the simplest way to go with in this situation is using string concatenation but keep in mind that this approach needs that every single value you wish to pass can be represented into a string format.

For sure every object/value can be represented into a string format but not always using the string format is the best choice. If the object is too complicated then trying to construct the object from its string format will be somehow hard, not impossible, but hard.

So, assume that we decided to use the string concatenation approach. This is done as follows:

1. Constructing the string format: concatenate all sections/values into one string while using a certain character or set of characters as a separator between each two sections/values
2. Reconstructing sections/values: split the constructed string by the same character or set of characters you used in the constructing part

Here we have a problem that whatever the character or set of characters we will use as a separator between sections/values there is always a probability that the sections/values may also contain this character or set of characters in the first place. This will cause a problem when splitting the constructed string as the returned sections/values will be deformed.

So, to overcome this problem we will use the same approach but with some extra steps.

Analysis

• Let's say that we will use "#;" as our separator
• Also let's say that the sections/values we want to work on are "Ahm#;ed" and "Tar#ek"
• So, if we just concatenated the sections using the separator the result will be "Ahm#;ed#;Tar#ek"
• Now when splitting to reconstruct values the result will be "Ahm", "ed" and "Tare#ek"
• This is wrong
• So, since that the "#;" found in the "Ahm#;ed" is causing us problems, let's deal with it first
• So, we will first replace "#;" in "Ahm#;ed" to something else so that it will not confuse us while splitting
• But wait, if we replace it to another character or set of characters won't this cause the same problem we are trying to avoid in the first place???
• Say that we will replace "#;" in "Ahm#;ed" to "<>", then this will work because we already know that "Ahm#;ed" doesn't contain "<>". But what if at run-time one of the values we are dealing with already contain "<>"??? Then, we will have the same problem of invalid splitting, right?
• Ok, this is confusing but we have a solution
• Let's get back a few points, we said that the "#;" found in the "Ahm#;ed" is causing us problems
• We tried before to replace "#;" as a whole although we can only replace a part of it
• Also, the something to replace to should not be a completely different thing
• Confused, let's check this example
• We will replace "#" in "Ahm#;ed" to "#&", so the result will be "Ahm#&;ed"
• The same for "Tare#ek", it will be "Tare#&ek"
• So after concatenation it will be "Ahm#&;ed#;Tare#&ek"
• So after splitting the sections will be "Ahm#&;ed" and "Tare#&ek"
• Finally replace the "#&" to "#" in all sections
• Then the sections will be "Ahm#;ed" and "Tare#ek" which are the same sections we started with

Conclusion

1. Decide a separator ("#;")
2. Decide the part to replace in each section given that it is a part of the separator ("#")
3. Decide the string to replace to ("#&")
4. While concatenation, replace each occurrence of "#" in each section to "#&" then concatenate using "#;"
5. While splitting, split by "#;" then replace each occurrence of "#&" in each section to "#"
6. That's it, you now have your sections as they are without any deformation

Now, let's see some code.

The code below represents a class which encapsulates the logic described above.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;

namespace DevelopmentSimplyPut.Utilities
{
    public class EmployeeToken
    {
        #region Properties
  private string firstName;
        public string FirstName
        {
            get
   {
    return firstName;
   }
        }

  private string secondName;
        public string SecondName
        {
            get
   {
    return secondName;
   }
        }
  
  private string concatenatedSections;
        public string ConcatenatedSections
        {
            get
   {
    return concatenatedSections;
   }
        }
  
  public string NamesSeparator
        {
            get
   {
    return "#;";
   }
        }
  
  public string NamesStringToReplace
        {
            get
   {
    return "#";
   }
        }
  
  public string NamesToReplaceTo
        {
            get
   {
    return "#&";
   }
        }
        #endregion Properties

        #region Constructors
        public EmployeeToken(string _firstName, string _lastName)
        {
            firstName = _firstName;
            lastName = _lastName;
   
   concatenatedSections =
    string.Format(CultureInfo.InvariantCulture, "{0}{1}{2}"
                    , Encode(firstName)
                    , NamesSeparator
                    , Encode(lastName));
        }
  
  public EmployeeToken(string _concatenatedSections)
        {
   concatenatedSections = _concatenatedSections;
   
            if (!string.IsNullOrEmpty(id))
            {
                string[] separators = { NamesSeparator };
                stirng[] sections = concatenatedSections.Split(separators, StringSplitOptions.None);

                if (null != sections && sections.Length == 2)
                {
                    firstName = Decode(sections[0]);
                    lastName = Decode(sections[1]);
                }
            }
        }
        #endregion Constructors

        #region Utilities
        private static string Encode(string str)
        {
            return ((str == null) ? string.Empty : str.Replace(NamesStringToReplace, NamesToReplaceTo));
        }
        private static string Decode(string str)
        {
            return ((str == null) ? string.Empty : str.Replace(NamesToReplaceTo, NamesStringToReplace));
        }
        #endregion Utilities
    }
}

So now if you try to use this class you will get your results right

EmployeeToken newEmp = new EmployeeToken("Ahm#;ed", "Tar#ek");
Console.WriteLine(newEmp.ConcatenatedSections);
Console.WriteLine(newEmp.FirstName);
Console.WriteLine(newEmp.SecondName);

EmployeeToken newEmp1 = new EmployeeToken(newEmp.ConcatenatedSections);
Console.WriteLine(newEmp1.ConcatenatedSections);
Console.WriteLine(newEmp1.FirstName);
Console.WriteLine(newEmp1.SecondName);

That's it. Hope you find this useful.

Read More

How To Sell Your Work And Get Paid?

Everyone of us may have some personal work items like software, tutorials, presentations, articles, videos, graphics,....... Now, instead of just keeping these items in the shades on your drive you can sell these items and get paid for your work every time one of your items is sold.

One of the problems is always to find the right market. So, I found a great market where you can sell your work and get paid. This is what this article about.

Freelancer is a great reliable online service for freelancing. You will find that you can benefit from this service in two ways:

1. Freelancing: You will find some people posting requests for software, articles, remote assistance, professional resume writing, technical writing, translations, graphics, ........... and so many things which you can find yourself interested in and capable of providing. Whatever service you provide you will get paid
2. Freemarket: You can post items of your work and provide a price so that every time someone buys one of your items you get paid.

That's it, I wish you find this service useful and profitable.

Read More