A common need in SQL is the ability to iterate over a list as if it were an array. In SQL it is not possible to declare arrays, unlike other programming languages such as ColdFusion, ActionScript and Java. Fortunately, there is a way around this problem: use a User-Defined Functions (UDFs) to create a tabular version of the data. Arrays are, after all, essentially tabular data (at their simplest, one dimension level).

A User-Defined Function, is a function provided by the user of a program or environment. In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements.

The Function Code

Below is the complete function definition:

CREATE FUNCTION dbo.udf_ListToTable
(
	@LIST 		NVARCHAR(4000), 
	@DELIMITER 	NVARCHAR(10) = ','
)
RETURNS @ListTable TABLE 
(
	Item NVARCHAR(200)
)
AS
BEGIN
	DECLARE @LenDel 	INT
	DECLARE @Pos 		INT
	DECLARE @Item 		NVARCHAR(200)
 
	--Get the length of the delimiter, use hack to get around LEN(' ') = 0 issue
	SET @LenDel = LEN(@DELIMITER + '|') - 1 
 
	SET @Pos = CHARINDEX(@DELIMITER, @LIST)
	WHILE @Pos > 0
	BEGIN
		--Get the item
		SET @Item = SUBSTRING(@LIST, 1, @Pos-1)
		--Add it to the table (if not empty string) 
		IF LEN(LTRIM(@Item)) > 0
			INSERT @ListTable (Item) VALUES (LTRIM(@Item))
		--Remove the item from the list
		SET @LIST = STUFF(@LIST, 1, @Pos+@LenDel-1, '')
		--Get the position of the next delimiter
		SET @Pos = CHARINDEX(@DELIMITER, @LIST)		
	END
 
	--Add the last item to the table (if not empty string) 
	IF LEN(LTRIM(@LIST)) > 0
		INSERT @ListTable (Item) VALUES (LTRIM(@LIST))
 
	RETURN 
END
GO

The function simply loops over the list passed into the function. Each list item is then inserted into the variable named @ListTable, which is of type TABLE. The @ListTable variable is then returned out of the function and can be handled the same as any other table.

The Function In Use

A simple demonstration is as follows:

INSERT INTO tableName (column1, column2, column3, column4)
SELECT @variable1, @variable2, myTable.item, GETDATE()
FROM dbo.udf_ListToTable(@list,',') AS myTable

In this example, we insert the same information (@variable1, @variable2) for every instance of an item found in myTable.

This is useful, for example, if you want to apply a setting to a group of users. The group of users could be contained in a list that needs to be parsed as a table, whilst the individual setting details are contained in the other variables.

Download the Code

Download the code, rename the file to .sql and run on your database instance. You will then be able to reference the function in your Stored Procedures.

Rich Internet Applications (RIAs) are just the beginning. A key trend taking place throughout the Web industry is the urgency to integrate disparate systems and software tools to reduce costs, increase developer productivity, reduce the need for manual processing and intervention in transactions, and decrease time to market. To achieve these objectives, organisations have endorsed the adoption of standards-based systems (e.g. XML, Design Patterns, CSS, ECMAScript) combined with the migration to Web Services and Service Orientated Architecture (SOA). This has led to a requirement to create a consistent and intuitive interface to applications, data and services. The immediate goal of these efforts is to provide simpler, quicker and more efficient access and processing of information. Increasingly, Web applications are also offering customers application interfaces that are more personalised and customised to each individual’s specific requests and requirements.

It is clear that RIAs offer the potential to fundamentally change the user experience and in doing so, yield significant business benefits. However, in order for RIAs to be widely employed, and for more companies to receive these kinds of returns, technologies to build RIAs will need to appeal to a wider range of developers. The ability to cost effectively create rich, engaging user experiences that support corporate objectives and reach a broader developer audience without sacrificing development productivity require a new generation of RIA tools. These tools are being developed by a large number of organisations with Adobe, Microsoft, Google, Apple and Sun leading the way with the AIR/Flash/Flex combination, Silverlight, Gears, Quicktime and JavaFX respectively.

The new generation of RIA tools being developed by the likes of Adobe and Microsoft must do the following to allow developers to truely harness the power of RIAs in the commercial environment:

1. Allow developers to write applications using familiar development models to utilise and extend their current skills without requiring them to adopt entirely new or different skills
2. Use standard and standards-based technologies
3. Use industry specific programming models and patterns
4. Use and/or leverage the existing IT infrastructure through wrap and reuse rather than rip and replace
5. Provide pervasive, familiar programming models and an expressive user interface across platforms and devices; and
6. Allow developers to create a solution that delivers scalable, secure, high performance solutions that are bandwidth efficient

These new RIA tools will need to provide the features that enhance IT developer’s abilities to be more creative and to accomplish RIA development with the same or less effort than the tools they use to create other types of applications. What is required are the tools that can help developers achieve these objectives without relying on only HTML or other scripting languages, or having to learn a completely new development approach.

Two vendors which have the technology and capaibility to fully deliver Rich Internet Applications are Adobe and Microsoft. With Microsoft’s Silverlight and XAML, developing rich internet applications to run on Windows platforms will progress at a fast rate. In turn, Adobe has had a head start with the aquisition of Macromedia and the subsequent addition of Flash and Flex to its product offering. Flash and its relative ubiquity across platforms and devices ensures that RIA development and production will be accessible to a large user base and as such puts Adobe at a distinct advantage over Microsoft.

A new breed of Web-based data integration applications is emerging across the Internet. Colloquially known as “mashups”, their popularity stems from the emphasis on interactive user participation and the manner in which they aggregate third-party data.

A mashup is a website or web application that seamlessly combines content from more than one source into an integrated experience.

Mashups are an exciting genre of interactive Web applications that are characterised by, and draw upon, content and functionality retrieved from external data sources to create entirely new and innovative services. They are a hallmark of the second generation of Web applications widely known as Web 2.0.

This vague data-integration definition of a mashup certainly isn’t a rigorous one. A good insight as to what makes a mashup is to look at the etymology of the term:

Mashup, or bastard pop, is a musical genre which, in its purest form, consists of the combination (usually by digital means) of the music from one song with the a cappella from another. Typically, the music and vocals belong to completely different genres. At their best, bastard pop songs strive for musical epiphanies that add up to considerably more than the sum of their parts.

Like these songs, a mashup is an unusual or innovative composition of content (often from unrelated data sources), made for human (rather than computerized) consumption.

Mapping mashups

In this age of information technology, people are collecting a immense amount of data about things, activities, events, all of which can be annotated with locations. These diverse data sets that contain location data, are wanting to be presented graphically using maps. One of the big catalysts for the advent of mashups was Google’s introduction of its Google Maps API. This opened the floodgates, allowing Web developers to mash all sorts of data (everything from nuclear disasters to Weather Bonk and Keotag) onto maps. Not to be left out, APIs from Microsoft (Virtual Earth), Yahoo (Yahoo Maps), and AOL (MapQuest) shortly followed.

Video and photo mashups

The emergence of photo hosting and social networking sites like Flickr with APIs that expose photo sharing has led to a variety of interesting mashups. Because these content providers have metadata associated with the images they host (such as who took the picture, what it is a picture of, where and when it was taken, user-defined tags for describing the image and more), mashup designers can mash photos with other information that can be associated with the metadata. For example, a mashup might analyse song or poetry lyrics and create a mosaic or collage of relevant photos, or display social networking graphs based upon common photo metadata (subject, timestamp, and other metadata.). Yet another example might take as input a Web site (such as a news site like CNN) and render the text in photos by matching tagged photos to words from the news. EducationSearch is an education search tool which enables you to search by: Location, Career, Industry/Salary and provides personalized searches to save for future reference. EducationSearch Utilises Flickr, Google Maps and YouTube.

Search and Shopping mashups

Search and shopping mashups have existed long before the term mashup was coined. Before the days of Web APIs, comparative shopping tools such as BizRate, PriceGrabber, MySimon, CrowdStorm, Shopping.com and Google’s Froogle used combinations of business-to-business (B2B) technologies or screen-scraping to aggregate comparative price data. To facilitate mashups and other interesting Web applications, consumer marketplaces such as eBay and Amazon have released APIs for programmatically accessing their content.

News mashups

News sources (such as the New York Times, the BBC, or Reuters) have used syndication technologies like RSS and Atom since 2002 to disseminate news feeds related to various topics. Syndication feed mashups can aggregate a user’s feeds and present them over the Web, creating a personalized newspaper that caters to the reader’s particular interests. An example includes Diggdot.us, which combines feeds from the techie-oriented news sources Digg.com, Slashdot.org, and Del.icio.us. This is in contrast to Google News which aggregates news content through complex search algorithms.

Mashups represent huge benefits and challenges to software companies. No longer is the web simply a collection of web pages that a user ’surfs’ through on a day to day basis. The web is becoming an omnipotent tool, a global application along the mold of Microsoft’s Windows OS. People are learning to develop Web 2.0 with much the same energy as seen in the early innovations of the personal computer market. The more people seize control of this new paradigm, the more the long-delayed promise of software and services that can be tapped on demand is realised.

At the same time these bottom-up efforts represent a tough challenge to the service providers upon which the mashup is based. Mashups often use data with out licence, and present this data in unintended ways. For example, Yahoo initially blocked the use of its API by one mashup website that was using it’s content in conjunction with the Google Maps API. Amazon blocked the use of it’s API by Amazon Light until it changed how it linked to rival sites and the GreaseMonkey extension for the Firefox Browser, which allows the quick installation of scripts to manipulate web pages, represents a security threat if exposed to malicious scripts.

Inexpensive Research & Development

Amazon and other giants in the web business are embracing the mashup phenomenon by allowing easier access to their data services. Indeed, these companies are programming their interfaces so that much of the computations are made on the client’s computer rather than a server located on potentially another continent. This allows developer’s to make their own tweaks.

The appeal to web sites is clear. Mashups represent a way to develop creativity, software, tools and communicate messages to the community.

However, mashup business models don’t extend beyond running a few Google ads and collecting fees for sending buyers to e-commerce sites. One reason is that most Web sites don’t allow for-profit use of their data by outsiders. But as traffic to mash-ups grows, companies may cut deals, especially if mash-up sites spur new markets. Map-based mash-ups, for instance, may finally attract local businesses to advertise on the Web.

Link(s)

http://www.programmableweb.com