Daniel Moth

http://www.danielmoth.com/Blog/

Insert Null value into DateTime column in SQL server from ASPX application

http://blogs.msdn.com/jaskis/archive/2008/07/09/insert-null-value-into-datetime-column-in-sql-server-from-aspx-application.aspx

Scenario
========

We have following web form asking user to enter name and joining date

 

In SQL express we are having following columns

Error
======
When ever user does not enter joining date instead of getting null value added into join_date column we end with exception on browser

Code Behind
===========

    SqlCommand cmd1 = new SqlCommand("INSERT INTO emp(emp_name, join_date) VALUES(@emp_name1,@join_date1)", con1);

    cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar).Value = name.Text;
    cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime).Value = joindate.Text;

    cmd1.ExecuteNonQuery();
    Label1.Text = " Record added";

 

Solution
========

Make a check(if) for empty string and insert specific SqlDateTime.null value

C#

cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar);
cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime);

cmd1.Parameters["@emp_name1"].Value = name.Text;

System.Data.SqlTypes.SqlDateTime getDate;
//set DateTime null
getDate = SqlDateTime.Null;

if (joindate.Text == "")
{
    cmd1.Parameters["@join_date1"].Value = getDate;
}
else
{
    cmd1.Parameters["@join_date1"].Value = joindate.Text;
}

 

VB.NET

cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar)
cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime)

cmd1.Parameters("@emp_name1").Value = name.Text

Dim getDate As SqlDateTime
getDate = SqlDateTime.Null

If joindate.Text = "" Then
   cmd1.Parameters("@join_date1").Value = getDate
Else
   cmd1.Parameters("@join_date1").Value = joindate.Text
End If

 

Hope this helps!!

Enter Null Values for DateTime Column of SQL Server

http://www.c-sharpcorner.com/UploadFile/sd_patel/EnterNullValuesForDateTime11222005015742AM/EnterNullValuesForDateTime.aspx

Inserting a null value to the DateTime Field in SQL Server is one of the most common issues giving various errors. Even if one enters null values the value in the database is some default value as 1/1/1900 12:00:00 AM.

The Output of entering the null DateTime based on the code would in most cases have errors as:

• String was not recognized as a valid DateTime.
• Value of type ‘System.DBNull’ cannot be converted to ‘String’.

Or No Error but DataTime entered in Database would be as 1/1/1900 12:00:00 AM
So lets write the code to enter null values in the DataBase.

 

 

The User Interface is as follows:

 

 

To begin with Code:

Namespaces used

• System.Data.SqlClient/ System.Data.OleDb

• System.Data.SqlTypes
• Code for System.Data.SqlClient

C#

string sqlStmt ;
string conString ;
SqlConnection cn =null;
SqlCommand cmd =null;
SqlDateTime sqldatenull ;
try
{
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) “;
conString = “server=localhost;database=Northwind;uid=sa;pwd=;”;
cn = new SqlConnection(conString);
cmd = new SqlCommand(sqlStmt, cn);
cmd.Parameters.Add(new SqlParameter(“@FirstName”, SqlDbType.NVarChar, 11));
cmd.Parameters.Add(new SqlParameter(“@LastName”, SqlDbType.NVarChar, 40));
cmd.Parameters.Add(new SqlParameter(“@Date”, SqlDbType.DateTime));
sqldatenull = SqlDateTime.Null;
cmd.Parameters["@FirstName"].Value = txtFirstName.Text;
cmd.Parameters["@LastName"].Value = txtLastName.Text;
if (txtDate.Text == “”)
{
cmd.Parameters ["@Date"].Value =sqldatenull ;
//cmd.Parameters["@Date"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@Date"].Value = DateTime.Parse(txtDate.Text);
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = “Record Inserted Succesfully”;
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

VB.NET

Dim sqlStmt As String
Dim conString As String
Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim sqldatenull As SqlDateTime
Try
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) “
conString = “server=localhost;database=Northwind;uid=sa;pwd=;”
cn = New SqlConnection(conString)
cmd = New SqlCommand(sqlStmt, cn)
cmd.Parameters.Add(New SqlParameter(“@FirstName”, SqlDbType.NVarChar, 11))
cmd.Parameters.Add(New SqlParameter(“@LastName”, SqlDbType.NVarChar, 40))cmd.Parameters.Add(New SqlParameter(“@Date”, SqlDbType.DateTime))
sqldatenull = SqlDateTime.Null
cmd.Parameters(“@FirstName”).Value = txtFirstName.Text
cmd.Parameters(“@LastName”).Value = txtLastName.Text
If (txtDate.Text = “”) Then
cmd.Parameters(“@Date”).Value = sqldatenull
‘cmd.Parameters(“@Date”).Value = DBNull.Value
Else
cmd.Parameters(“@Date”).Value = DateTime.Parse(txtDate.Text)
End If
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = “Record Inserted Succesfully”
Catch ex As Exception
Label1.Text = ex.Message
Finally
cn.Close()
End Try

Code for System.Data.SqlClient.

C#

string sqlStmt;
string conString ;
OleDbConnection cn =null ;
OleDbCommand cmd =null ;
try
{
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (?,?,?) “;
conString = “Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost”;
cn =new OleDbConnection(conString);
cmd = new OleDbCommand(sqlStmt, cn) ;
cmd.Parameters.Add(new OleDbParameter(“@FirstName”, OleDbType.VarChar, 40));
cmd.Parameters.Add(new OleDbParameter(“@LastName”, OleDbType.VarChar, 40));
cmd.Parameters.Add(new OleDbParameter(“@Date”, OleDbType.Date));
cmd.Parameters["@FirstName"].Value = txtFirstName.Text;
cmd.Parameters["@LastName"].Value = txtLastName.Text;
if ((txtDate.Text == “”) )
{
cmd.Parameters["@Date"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@Date"].Value = DateTime.Parse(txtDate.Text);
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = “Record Inserted Succesfully”;
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
} 

VB.NET

Dim sqlStmt As String
Dim conString As String
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Try
sqlStmt = “insert into Emp (FirstName,LastName,Date) Values (?,?,?) “
conString = “Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost”
cn = New OleDbConnection(conString)
cmd = New OleDbCommand(sqlStmt, cn)
cmd.Parameters.Add(New OleDbParameter(“@FirstName”, OleDbType.VarChar, 40))cmd.Parameters.Add(New OleDbParameter(“@LastName”, OleDbType.VarChar, 40))cmd.Parameters.Add(New OleDbParameter(“@Date”, OleDbType.Date))cmd.Parameters(“@FirstName”).Value = txtFirstName.Text
cmd.Parameters(“@LastName”).Value = txtLastName.Text
If (txtDate.Text = “”) Then
cmd.Parameters(“@Date”).Value = DBNull.Value
Else
cmd.Parameters(“@Date”).Value = DateTime.Parse(txtDate.Text)
End If
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = “Record Inserted Succesfully”
Catch ex As Exception
Label1.Text = ex.Message
Finally
cn.Close()
End Try

Searchalot.com

http://searchalot.com/

We search a lot,so you don’t have to!

WITH common_table_expression (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms175972(SQL.90).aspx

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.

Transact-SQL Syntax Conventions

 Syntax

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

 Arguments

expression_name

Is a valid identifier for the common table expression. expression_name must be different from the name of any other common table expression defined in the same WITH <common_table_expression> clause, but expression_name can be the same as the name of a base table or view. Any reference to expression_name in the query uses the common table expression and not the base object.

column_name

Specifies a column name in the common table expression. Duplicate names within a single CTE definition are not allowed. The number of column names specified must match the number of columns in the result set of the CTE_query_definition. The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

CTE_query_definition

Specifies a SELECT statement whose result set populates the common table expression. The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE. For more information, see the Remarks section and CREATE VIEW (Transact-SQL).

If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT. For more information about how to use recursive CTE query definitions, see the following “Remarks” section and Recursive Queries Using Common Table Expressions.

 Remarks

Guidelines for Creating and Using CTEs

The following guidelines apply to nonrecursive CTEs. For guidelines that apply to recursive CTEs, see “Guidelines for Defining and Using Recursive CTEs” that follows.

• A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.
• Multiple CTE query definitions can be defined in a nonrecursive CTE. The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.
• A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
• Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.
• The following clauses cannot be used in the CTE_query_definition:
  • COMPUTE or COMPUTE BY
  • ORDER BY (except when a TOP clause is specified)
  • INTO
  • OPTION clause with query hints
  • FOR XML
  • FOR BROWSE
• When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
• A query referencing a CTE can be used to define a cursor.
• Tables on remote servers can be referenced in the CTE.
• When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. When this occurs, the query returns an error. For more information, see View Resolution.

Guidelines for Defining and Using Recursive CTEs

The following guidelines apply to defining a recursive CTE:

• The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. All CTE query definitions are anchor members unless they reference the CTE itself.
• Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.
• The number of columns in the anchor and recursive members must be the same.
• The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.
• The FROM clause of a recursive member must refer only one time to the CTE expression_name.
• The following items are not allowed in the CTE_query_definition of a recursive member:
  • SELECT DISTINCT
  • GROUP BY
  • HAVING
  • Scalar aggregation
  • TOP
  • LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)
  • Subqueries
  • A hint applied to a recursive reference to a CTE inside a CTE_query_definition.

The following guidelines apply to using a recursive CTE:

• All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.
• An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement. For more information, see Query Hint (Transact-SQL).
• A view that contains a recursive common table expression cannot be used to update data.
• Cursors may be defined on queries using CTEs. The CTE is the select_statement argument that defines the result set of the cursor. Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. If another cursor type is specified in a recursive CTE, the cursor type is converted to static.
• Tables on remote servers may be referenced in the CTE. If the remote server is referenced in the recursive member of the CTE, a spool is created for each remote table so the tables can be repeatedly accessed locally.

 Examples

A. Creating a simple common table expression

The following example shows the number of employees reporting directly to each manager at Adventure Works Cycles.

Copy Code

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
    SELECT ManagerID, COUNT(*)
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO

B. Using a common table expression to limit counts and report averages

The following example shows the average number of employees reporting to managers.

Copy Code

WITH DirReps (Manager, DirectReports) AS
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
)
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps
WHERE DirectReports>= 2 ;
GO

C. Referencing a common table expression more than one time

The following example shows the total number of sales orders and the most recent sales order date in the SalesOrderHeader table for each salesperson. In the running statement, the CTE is referenced two times: one time to return the selected columns for the salesperson, and again to retrieve similar details for the salesperson’s manager. The data for both the salesperson and the manager are returned in a single row.

Copy Code

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

D. Using a recursive common table expression to display multiple levels of recursion

The following example shows the hierarchical list of managers and the employees who report to them.

Copy Code

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO

E. Using a recursive common table expression to display two levels of recursion

The following example shows managers and the employees reporting to them. The number of levels returned is limited to two.

Copy Code

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports
WHERE EmployeeLevel <= 2 ;
GO

F. Using a recursive common table expression to display a hierarchical list

The following example builds on Example C by adding the names of the manager and employees, and their respective titles. The hierarchy of managers and employees is additionally emphasized by indenting each level.

Copy Code

USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;
GO

G. Using MAXRECURSION to cancel a statement

MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two.

Copy Code

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte
    JOIN  HumanResources.Employee AS e
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

After the coding error is corrected, MAXRECURSION is no longer required. The following example shows the corrected code.

Copy Code

USE AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

H. Using a common table expression to selectively step through a recursive relationship in a SELECT statement

The following example shows the hierarchy of product assemblies and components that are required to build the bicycle for ProductAssemblyID = 800.

Copy Code

USE AdventureWorks;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

I. Using a recursive CTE in an UPDATE statement

The following example updates the VacationHours value by 25 percent for all employees who report directly or indirectly to ManagerID 12. The common table expression returns a hierarchical list of employees who report directly to ManagerID 12 and employees who report to those employees, and so on. Only the rows returned by the common table expression are modified.

Copy Code

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

J. Using multiple anchor and recursive members

The following example uses multiple anchor and recursive members to return all the ancestors of a specified person. A table is created and values inserted to establish the family genealogy returned by the recursive CTE.

Copy Code

-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother
    FROM Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father
    FROM Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO

 See Also

Reference

CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL)
EXCEPT and INTERSECT (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
UPDATE (Transact-SQL)

Other Resources

Recursive Queries Using Common Table Expressions
Using Common Table Expressions

Common Table Expressions (CTE) in SQL Server 2005 By Scott Mitchell

http://www.4guysfromrolla.com/webtech/071906-1.shtml

 

---

Introduction
When crafting a query in SQL, there are often times when we may need to operate over a set of data that doesn’t inherently exist within the system. For example, the database for an eCommerce web application would have the standard tables – Products, Customers, Orders, OrderDetails, and so on – but we may need to run reports on a particular subset of the data or against aggregate data across these tables. Or the reporting queries we need might need to group or filter by results returned by scalar subqueries. Typically, views are used to break down complex queries into digestible chunks or to provide scalar subquery results that can be grouped and filtered. Views, however, are sometimes overkill, as they are permanent objects at the system-level. If we only need to reference this complex query in a single stored procedure or UDF, another option is to use a derived table. Unfortunately, derived tables muddle the readability of the query and must be repeated for each use in a statement.

Common Table Expressions, or CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query. Moreover, CTEs can be recursively defined, allowing a recursive entity to be enumerated without the need for recursive stored procedures. In this article we’ll examine the benefits, uses, and syntax of both recursive and non-recursive CTEs. Read on to learn more!

 

- continued -

Microsoft.com Operations Virtualizes MSDN and TechNet on Hyper-V This article provides further details about testing methods and the results from Hyper-V Beta to RC0 that generated the confidence to fully roll out MSDN and TechNet on Hyper-V in production. » Virtualization Case Study: Copa Airlines Copa Airlines joined a Microsoft Rapid Deployment Program to test Hyper-V to provide a dynamic and reliable virtualization environment and System Center Virtual Machine Manager for the physical to virtual conversions of the chosen servers. The RDP program proved that a Microsoft virtualization solution could be a cost-effective way for Copa to increase business application availability, reduce data center costs, and optimize data center management. » Virtualization from the Data Center to the Desktop Integrated virtualization solutions can help you meet evolving demands more effectively as you transform IT infrastructure from a cost center to a strategic business asset. »

 

 

Obtaining the Northwind Traders Database for SQL Server 2005

The examples used in this article are all operating against the Northwind Traders database, which has a simple eCommerce schema (Products, Customers, Orders, Order Details, and so on). Originally, the Northwind database was released for Microsoft Access. A version for SQL Server 2000 was later provided. Microsoft has not posted an “official” Northwind database for SQL Server 2005. However, you can create a SQL Server 2005 database and then import the schema and data from the create scripts provided in the SQL Server 2000 version download. Or, even better yet, you can simply download the Northwind database files from the list below for SQL Server 2005 and bypass having to create this database yourself.  Download the Microsoft Access version Download the Microsoft SQL Server 2000 version Download the Microsoft SQL Server 2005 version

A Simple Common Table Expression Example
Before we dive into the syntax or gritty details of CTEs, let’s start by looking at a simple example. I think you’ll agree that even without knowing the syntax of CTEs, they are pretty readable and straightforward (the hallmark of a well-designed programming language construct).

WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS (    SELECT       p.ProductName,       c.CategoryName,       p.UnitPrice    FROM Products p       INNER JOIN Categories c ON          c.CategoryID = p.CategoryID    WHERE p.UnitPrice > 10.0 ) SELECT * FROM ProductAndCategoryNamesOverTenDollars ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

This query creates a CTE named ProductAndCategoryNamesOverTenDollars that returns the name, category name, and price of those products whose unit price exceeds $10.00. Once the CTE has been defined, it must then immediately be used in a query. The query treats the CTE as if were a view or table in the system, returning the three fields defined by the CTE (ProductName, CategoryName, and UnitPrice), ordered alphabetically by category, then by price, and then alphabetically by product name.

 

In short, a Common Table Expression allows us to define a temporary, view-like construct. We start by (optionally) specifying the columns it returns, then define the query. Following that, the CTE can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Common Table Expression Syntax
A Common Table Expression contains three core parts:

• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

The query using the CTE must be the first query appearing after the CTE. That is, you cannot do the following:

WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS (    SELECT       p.ProductName,       c.CategoryName,       p.UnitPrice    FROM Products p       INNER JOIN Categories c ON          c.CategoryID = p.CategoryID    WHERE p.UnitPrice > 10.0 ) SELECT * FROM Products SELECT * FROM ProductAndCategoryNamesOverTenDollars ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

The ProductAndCategoryNamesOverTenDollars CTE only applies to the first query following it. So when the second query is reached, ProductAndCategoryNamesOverTenDollars is undefined, resulting in an “Invalid object name ‘ProductAndCategoryNamesOverTenDollars’” error message.

You can, however, define multiple CTEs after the WITH keyword by separating each CTE with a comma. For example, the following query uses two CTEs. The subsequent SELECT query then uses an INNER JOIN to match together the records from the two CTEs:

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS (    SELECT       CategoryID,       CategoryName,       (SELECT COUNT(1) FROM Products p        WHERE p.CategoryID = c.CategoryID) as NumberOfProducts    FROM Categories c ), ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS (    SELECT       ProductID,       CategoryID,       ProductName,       UnitPrice    FROM Products p    WHERE UnitPrice > 10.0 ) SELECT c.CategoryName, c.NumberOfProducts,       p.ProductName, p.UnitPrice FROM ProductsOverTenDollars p    INNER JOIN CategoryAndNumberOfProducts c ON       p.CategoryID = c.CategoryID ORDER BY ProductName

 

 

Unlike a derived table, CTEs can be defined just once, yet appear multiple times in the subsequent query. To demonstrate this, consider the following example: the Northwind database’s Employees table contains an optional ReportsTo column that, if specified, indicates the employee’s manager. ReportsTo is a self-referencing foreign key, meaning that, if provided, it refers back to another EmployeeID in the Employees table. Imagine that we wanted to display a list of employees including how many other employees they directly managed. This could be done using a simple, CTE-free SELECT statement, but let’s use a CTE for now (for reasons which will become clear soon):

WITH EmployeeSubordinatesReport (EmployeeID, LastName, FirstName, NumberOfSubordinates, ReportsTo) AS (    SELECT       EmployeeID,       LastName,       FirstName,       (SELECT COUNT(1) FROM Employees e2        WHERE e2.ReportsTo = e.EmployeeID) as NumberOfSubordinates,       ReportsTo    FROM Employees e ) SELECT LastName, FirstName, NumberOfSubordinates FROM EmployeeSubordinatesReport

This query will return the employees records, showing each employee’s last and first name along with how many other employees they manage. As the figure below shows, only Andrew Fuller and Steven Buchanan are manager material.

 

Now, imagine that our boss (Andrew Fuller, perhaps) comes charging into our office and demands that the report also lists each employee’s manager’s name and number of subordinates (if the employee has a manager, that is – Mr. Fuller is all to quick to point out that he reports to no one). Adding such functionality is a snap with the CTE – just add it in a LEFT JOIN!

WITH EmployeeSubordinatesReport (EmployeeID, LastName, FirstName, NumberOfSubordinates, ReportsTo) AS (    SELECT       EmployeeID,       LastName,       FirstName,       (SELECT COUNT(1) FROM Employees e2        WHERE e2.ReportsTo = e.EmployeeID) as NumberOfSubordinates,       ReportsTo    FROM Employees e ) SELECT Employee.LastName, Employee.FirstName, Employee.NumberOfSubordinates,    Manager.LastName as ManagerLastName, Manager.FirstName as ManagerFirstName, Manager.NumberOfSubordinates as ManagerNumberOfSubordinates FROM EmployeeSubordinatesReport Employee    LEFT JOIN EmployeeSubordinatesReport Manager ON       Employee.ReportsTo = Manager.EmployeeID

With this additional LEFT JOIN, the employee’s manager’s results are returned; if there’s no manager for the employee, NULLs are returned instead.

 

When to Use Common Table Expressions

Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don’t necessarily need a view defined for the system. Even when a CTE is not necessarily needed (as when listing just the employees and their subordinate count in the example above), it can improve readability. In Using Common Table Expressions, Microsoft offers the following four advantages of CTEs: Create a recursive query. Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access. Reference the resulting table multiple times in the same statement. Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated. Using scalar subqueries (such as the (SELECT COUNT(1) FROM ...) examples we’ve looked at in this article) cannot be grouped or filtered directly in the containing query. Similarly, when using SQL Server 2005’s ranking functions – ROW_NUMBER(), RANK(), DENSE_RANK(), and so on – the containing query cannot include a filter or grouping expression to return only a subset of the ranked results. For both of these instances, CTEs are quite handy. (For more on SQL Server 2005’s ranking capabilities, be sure to read: Returning Ranked Results with Microsoft SQL Server 2005.)CTEs can also be used to recursively enumerate hierarchical data. We’ll examine this next!

Recursive Common Table Expressions
Recursion is the process of defining a solution to a problem in terms of itself. For example, a teacher needs to sort a stack of tests alphabetically by the students’ names. She could process the tests one at a time and, for each test, insert it into the appropriate spot to the left (called insertion sort), probably the way most people sort a hand of cards (at least that’s the way I do it). However, depending on the distribution of the tests, the size of the work space, the number of tests to sort, and so on, it may be far more efficient to break down the problem into pieces. Rather than doing an insertion sort right off the bat, it might first make sense to divide the stack of papers in half, and then do an insertion sort on one half, an insertion sort on the second half, and then a merge of the two piles. Or perhaps it would make sense to divide the tests into four piles, or eight piles. (This approach is referred to as merge sort.)

With a recursive solution you will always have the following two pieces:

• The base case – what to do when you’re done recursing. After dividing the tests into separate piles of say, eight elements per pile, the base case is to sort these piles via insertion sort.
• The recursive step – the action to perform that involves plugging the input “back into” the system. For merge sort, the recursive step is the division of one pile into two. Then into four. Then into eight, and so on, until the base case is reached.

For more on recursion, see Recursion, Why It’s Cool.

Returning to CTEs… the Employees database table holds the corporate hierarchy within its rows. Imagine that good ol’ Andrew Fuller has come back and insisted on a report that would list all persons in the company along with their position in the hierarchy. Since the Employees table can capture an arbitrary number of hierarchy levels, we need a recursive solution. Enter CTEs…

Like any recursive definition, a recursive Common Table Expression requires both a base case and the recursive step. In SQL parlance, this translates into two SQL queries – one that gets the “initial” data UNIONed with one that performs the recursion. For the Employees example, the base case is returning those employees that have no manager:

SELECT ... FROM Employees WHERE ReportsTo IS NULL

The recursion includes a query on the CTE itself. The following shows the CTE – with both the base case and recursive step – along with a SELECT query that returns the rows from the CTE:

WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS (    -- Base case    SELECT       EmployeeID,       LastName,       FirstName,       ReportsTo,       1 as HierarchyLevel    FROM Employees    WHERE ReportsTo IS NULL    UNION ALL    -- Recursive step    SELECT       e.EmployeeID,       e.LastName,       e.FirstName,       e.ReportsTo,       eh.HierarchyLevel + 1 AS HierarchyLevel    FROM Employees e       INNER JOIN EmployeeHierarchy eh ON          e.ReportsTo = eh.EmployeeID ) SELECT * FROM EmployeeHierarchy ORDER BY HierarchyLevel, LastName, FirstName

The recursion occurs in the second query in the CTE by joining the results of Employees against the CTE itself (EmployeeHierarchy) where the employees’ ReportsTo field matches up to the CTE’s EmployeeID. Included in this query is the HierarchyLevel field, which returns 1 for the base case and one greater than the previous level for each recursive step down the hierarchy. As requested, this resultset clearly shows that Mr. Fuller is the alpha male in this organization. Furthermore, we can see that Steven, Laura, Nancy, Janet, and Margaret make up the second tier in the organizational hierarchy, while poor Anne, Robert, and Michael are down at the bottom:

 

Alternatives to Recursive Common Table Expressions

As we saw in this article, enumerating hierarchical data recursively can be accomplished via CTEs (for more on using recursive CTEs, don’t forget to check out the official documentation – Recursive Queries Using Common Table Expressions). However, there are other options as well. One choice is to perform the recursion at the ASP/ASP.NET layer. That is, read in all employee information to a Recordset of DataSet in code, and then recurse there. My article Efficiently DisplayingParent-Child Data discusses this approach.If you need to perform the recursion in SQL, you can use recursive stored procedures, as discussed in The Zen of Recursion. If you are designing a data model that needs to support hierarchical data, your best bet is to bake in some lineage information directly into the table from the get-go. See SQL for Threaded Discussions and More Trees & Hierarchies in SQL for more information.

Conclusion
One of the many new features in SQL Server 2005 are Common Table Expressions (CTEs), which provide a more readable and usable approach to derived tables. Additionally, CTEs may be recursively defined, allowing a recursive entity to be enumerated without the need for recursive stored procedures. For more on the new features found in SQL Server 2005, be sure to also check out Returning Ranked Results with Microsoft SQL Server 2005 and TRY...CATCH in SQL Server 2005.

Happy Programming!

 

By Scott Mitchell

Creating a Stored Procedure for Custom Paging with the ASP.NET DataGrid Control

http://www.dotnetjunkies.com/Article/975BE770-E5DC-4610-870B-A82BDB9B8845.dcik

Gauri Sohoni

http://www.sqlservercurry.com/

Passing parameter to the TOP clause

http://www.sqlservercurry.com/2008/03/passing-parameter-to-top-clause.html

The TOP clause in SQL Server 2005 has been enhanced. You can now specify an expression as the number definition in the TOP clause. This makes your TOP clause dynamic as you can pass the number value in a variable and use that variable in the TOP clause of your T-Sql query

Sample Usage:
DECLARE @TopVar AS int
SET @TopVar = 20
SELECT TOP(@TopVar)
CustomerID,CompanyName, ContactName
FROM Northwind.dbo.Customers

 

 

Row_Number() function in SQL Server 2005 – Part II

http://www.databasejournal.com/features/mssql/article.php/3577481/RowNumber-function-in-SQL-Server-2005–Part-II.htm

In part I of this article, we saw how to generate row numbers and delete a simple single duplicate row. This article walks you through how to delete multiple duplicate rows. This article uses the new feature Row_Number() function, common table expression and the ANSI co-related subquery.

Let us assume we have the following table, Mytable, in the database MyDB. Let us create the database MyDB and MyTable by using the following script.

USE [MASTER]
GO
IF  EXISTS
  (SELECT NAME FROM SYS.DATABASES
   WHERE NAME = N'MYDB')
DROP DATABASE [MYDB]
GO
CREATE DATABASE MYDB
GO
USE [MYDB]
GO
IF  EXISTS
  (SELECT * FROM SYS.OBJECTS
  WHERE OBJECT_ID =
    OBJECT_ID(N'[DBO].[MYTABLE]')
  AND TYPE IN (N'U'))
DROP TABLE [DBO].[MYTABLE]
GO
CREATE TABLE MYTABLE
  (ID INT, FNAME VARCHAR(50),
  LNAME VARCHAR(50))
GO
insert into mytable select 1,'Jen','Ambelang'
insert into mytable select 11,'Jiong','Hong'
insert into mytable select 25,'Sandra','Mator'
insert into mytable select 35,'Chun','Chang'
insert into mytable select 21,'Yuki','Fukushima'
insert into mytable select 1,'Jen','Ambelang'
insert into mytable select 1,'Jen','Ambelang'
insert into mytable select 25,'Sandra','Mator'
insert into mytable select 25,'Sandra','Mator'
go

Let us query all the rows from the table MyTable using the following Select query.

SELECT * FROM MYTABLE ORDER BY ID

This query displays the following results.

1	Jen	Ambelang
1	Jen	Ambelang
1	Jen	Ambelang
11	Jiong	Hong
21	Yuki	Fukushima
25	Sandra	Mator
25	Sandra	Mator
25	Sandra	Mator
35	Chun	Chang

From the results, it is very clear that we have duplicate rows: 1, Jen, Ambelang and 25, Sandra, Mator.

Step 1

The first step in deleting duplicate rows is to generate a unique row id for the entire table. This can be done using the Row_Number() function.

Let us generate the unique numbers by executing the following query:

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,
ID,LNAME,FNAME FROM MYTABLE

This query produces the following results.

1	1	Ambelang	Jen
2	1	Ambelang	Jen
3	1	Ambelang	Jen
4	11	Hong	Jiong
5	21	Fukushima	Yuki
6	25	Mator	Sandra
7	25	Mator	Sandra
8	25	Mator	Sandra
9	35	Chang	Chun

Step 2

The second step in deleting duplicate rows is to generate unique row ids for every group.

Now, by using a co-related sub-query we can produce unique row ids for each group.

SELECT ROW,GROUPROW= CASE WHEN ID=ID
THEN
(SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,
ID,LNAME,FNAME FROM MYTABLE
) AS A WHERE A.ID=B.ID AND
A.ROW<B.ROW)+1 END,ID,FNAME,LNAME FROM (SELECT ROW_NUMBER() OVER (ORDER
BY ID) AS ROW,
ID,LNAME,FNAME FROM MYTABLE
)AS B

This query would produce the following results with a unique id for each group.

1	1	1	Jen	Ambelang
2	2	1	Jen	Ambelang
3	3	1	Jen	Ambelang
4	1	11	Jiong	Hong
5	1	21	Yuki	Fukushima
6	1	25	Sandra	Mator
7	2	25	Sandra	Mator
8	3	25	Sandra	Mator
9	1	35	Chun	Chang

Step 3

The last step in deleting duplicate rows is to use the common table expression, as shown below.

WITH DUPLICATE(ROW,GROUPROW,ID,FNAME,LNAME)
AS
(
SELECT ROW,GROUPROW= CASE WHEN ID=ID
THEN
(SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,
ID,LNAME,FNAME FROM MYTABLE
) AS A WHERE A.ID=B.ID AND
A.ROW<B.ROW)+1 END,ID,FNAME,LNAME FROM (SELECT ROW_NUMBER() OVER (ORDER
BY ID) AS ROW,
ID,LNAME,FNAME FROM MYTABLE
)AS B
)
DELETE FROM DUPLICATE WHERE GROUPROW<>1

Let us query all the rows from the source table, Mytable, by using the following Select Query.

SELECT * FROM MYTABLE ORDER BY ID

This query displays the following results:

1	Jen	Ambelang
11	Jiong	Hong
21	Yuki	Fukushima
25	Sandra	Mator
35	Chun	Chang

From the results, it is clear that duplicates from the table, Mytable, have been removed.

Conclusion

The main intent of this article was to demonstrate the use of SQL Server 2005’s new feature Row_number() function and Common Table Expression, with the help of co-related sub-query, to delete duplicate rows.

» See All Articles by Columnist MAK