Refactoring and static code analysis tool for SQL Server
Got a SQL Server or .NET question? Discuss it in the forums. (SQL Server Forums | Dot NET Forums)
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

Jacob's Blog

Recent Blog Posts

Most Viewed

Archive

September 2008 - Posts

Generating CROSS TAB results in SQL Server 2000

In the previous post we saw an example of the PIVOT operator introduced in SQL Server 2005. We saw a simple example that transformed rows to columns using the PIVOT operator. In this post, we will examine how to achieve this SQL Server 2000. Here is the...
by Jacob Sebastian on 09-29-2008 | 536 Views

Another PIVOT Query Example

Generating PIVOT/CROSS-TAB queries is one of the common requirements that we find in our day-to-day programming life. SQL Server 2005 introduced a new operator: PIVOT, which made generating pivot/cross-tab results much easier. Though the PIVOT operator...
by Jacob Sebastian on 09-29-2008 | 597 Views

An example using UNPIVOT (Transforming Columns to Rows)

In the previous post we discussed the PIVOT operator introduced by SQL Server 2005. We saw how to transform ROWS to COLUMNS using the PIVOT operator. This is the source data that we examined in the previous post. /* id --------- -- 1 2 3 4 5 6 7 8 9 10...
by Jacob Sebastian on 09-29-2008 | 331 Views

Be careful when renaming SPs/Views/Functions/Triggers

My friend and SQL Server MVP, Pinal has wrote a wonderful post warning people who rename their stored procedures, functions, views and triggers. You can find his post here . The problem is that, when you rename a stored procedure, function, view or trigger...
by Jacob Sebastian on 09-28-2008 | 194 Views

TSQL Lab 14 - Performing a recursive update for more than 32 levels

In a previous post we discussed how to perform a recursive update on the category table to display the number of items each category contains. A category may contain other sub-categories and they in turn might contain sub-sub-categories. We need to update...
by Jacob Sebastian on 09-27-2008 | 148 Views

TSQL Labs 13 - Matching a delimited string against another delimited string

We have seen a number of posts on various string operations using XQuery functions and FOR XML operator. This post explains how to match a delimited string against another delimited string. I wanted to share a piece of code that I wrote to help a member...
by Jacob Sebastian on 09-25-2008 | 43 Views

TSQL Lab 12 - Writing a recursive procedure to handle more than 32 levels

In the previous lab we discussed the limitation of a recursive stored procedure. You cannot perform recursion for more than 32 levels. I promised that in the next post, I will show you a new version of the stored procedure that works for more than 32...
by Jacob Sebastian on 09-25-2008 | 113 Views

FOR XML PATH - Matching a delimited string against another delimited string

We have seen a number of posts on various string operations using XQuery functions and FOR XML operator. This post explains how to match a delimited string against another delimited string. I wanted to share a piece of code that I wrote to help a member...
by Jacob Sebastian on 09-25-2008 | 164 Views

TSQL Lab 11 - Writing a recursive procedure to update the count of child items under each parent category

In the previous lab , we discussed a use case where we need a recursive procedure to update the count of child items under each parent category. Let us try to achieve this by using a recursive stored procedure. Let us write two stored procedures to achieve...
by Jacob Sebastian on 09-23-2008 | 240 Views

TSQL Lab 10 - Performing recursive updates in SQL Server

In the last UG meeting, we discussed recursion and recursive CTE in great detail. We saw a few real life examples that use recursion. In this post, I would like to present one more real life example where we might need a recursive procedure. Here is the...
by Jacob Sebastian on 09-23-2008 | 133 Views

TSQL Lab 9 - How to find the dependency chain of a database object?

One of my colleague needed a piece of code that returns all the dependent objects of a given database object. His project has a separate reporting server to which the data from primary database is replicated. He creates the database objects needed for...
by Jacob Sebastian on 09-17-2008 | 696 Views

XQuery Lab 36 - Writing a TSQL Function to compare two XML values (Part 2)

  In the previous post we discussed the logic we will apply to write the function that compares two XML values. We will write a function that accepts two XML variables and will compare elements and attributes as per the logic we discussed in the...
by Jacob Sebastian on 09-14-2008 | 830 Views

XQuery Lab 35 - How to write a loop to process all the child elements of an XML value?

  In the previous lab , we saw how to write a loop to process all the attributes of an element. We then discussed how to find the number of child elements that a parent element has. We also discussed how to retrieve a child element at the specified...
by Jacob Sebastian on 09-14-2008 | 1,169 Views

XQuery Lab 34 - How to retrieve the child element at a specified position?

  Some times you might need to retrieve the XML element at a given position. It may be because you are running a loop over all the elements of the XML document to perform some application specific operations or you want to pass those elements to...
by Jacob Sebastian on 09-14-2008 | 591 Views

XQuery Lab 33 - How to run a loop over all the attributes of an XML element?

  This is one of the requirements we came across, while writing the TSQL function to compare two XML values . In one of the previous posts, we saw how to find the name and value of attributes at a specified location. We then saw how to find the number...
by Jacob Sebastian on 09-14-2008 | 972 Views
More Posts Next page »

Copyright © Beyondrelational.com