Republish Article: Sql: Coalesce() Function

May 192011

Sql: Coalesce() Function

Please continue at Programminghelp.com Thanks and happy coding!

When developing queries in SQL Server, dealing with nulls can often be challenging. The COALESCE function was included in SQL 2005 to help deal with these issues. First let’s take a look at what COALESCE can offer. Generally, COALESCE function receives a sequence of values and a default value to used when all of items in the sequence of values are null. From here the function returns the first not-null in the sequence of values list.

Scenario

There is a requirement of showing a user’s full name and how much money they get paid per week. This scenario will be divided up into two segments, displaying the user’s full name and then computing and showing their weekly earnings. Please feel free to use the attached database to follow along.

Implementation

In this first example, suppose there is a table of users that have the columns FirstName, MiddleName and LastName. The table holds the following values:

In many applications, the requirement of welcoming the user is often needed. So, to put the user’s full name on the screen, a stored procedure using the COALESC function properly format all three fields into one field. So the query using COALESCE looks like this:

SELECT (FirstName + ' ' + COALESCE(MiddleName,'') + ' ' +
COALESCE(LastName,'')) AS FullName
FROM Users

The results will look like this:

For the next example, let’s extend the previous example by adding some information on how much the users get paid per week. Some users get paid by the hour while others get paid on a salary with a commission, so the table reflects this information.

For this example the table looks like this:

Please continue at Programminghelp.com Thanks and happy coding!

Article Source: http://www.articlesbase.com/databases-articles/sql-coalesce-function-521838.html

About the Author

Programming Help is a community collaboration for programming resources, tutorials, articles, forums, and help. Offering articles written by programmers and developers with years of experience. ProgrammingHelp.com strives to develop and create context and articles in which the beginning and most advanced level programmers/coders can use in everyday methods of development. We welcome you to come join us with your questions and knowledge to help us create an excelled programming community and open forum.

Republish Article: Sql Server Integration Services: Calling Ssis Package in C#

Republish Articles No Responses »

Oct 072010

Sql Server Integration Services: Calling Ssis Package in C#

Author: Programminghelp.com

SQL 2005: SQL Server Integration Services
Calling SSIS Package from C# Code

Introduction

During this series about SQL Server Integration Services, the basics of importing, exporting, manipulating data, typical database maintenance and deployment of SSIS packages have been covered. In this last article of this introductory series on SSIS, the technique of calling a SSIS package from C# will be covered.

Scenario

As seen in the last article, a package was successfully deployed and ran in SQL Server Management Studio. But there may be times when a package needs to be run by a user initiated action. This could be from another application or web service that has access to the where the dtsx package is physically located. For this example, a simple console application will be used to show how to call a SSIS package from some C# code.

Implementation

Open up Visual Studio and create a C# Console Project. Once the project is loaded, the first thing to take place is to add the reference to the code that uses SSIS packages. In solution explorer, add a reference to the Micorsoft.SQLServer.Managed.DTS.dll. Then in the blank class file add a using statement to reference the dll. There are four variable that are needed to be able to run a dtsx package. One being a string variable where the physical location of the package located, hence pkgLocation. Next is the actual package object, ssisPackage, followed by the application running the package, ssisApp. Finally it would be nice to display the results of the package once it is executed so ssisPackageResults will be used.

Please Visit Programminghelp.com For the full article and Pictorial tutorial.

http://www.programminghelp.com/database/sqlserver/sql-server-integration-services-calling-ssis-package-in-c/

Article Source: http://www.articlesbase.com/databases-articles/sql-server-integration-services-calling-ssis-package-in-c-514611.html

About the Author