Using COALESEC Function in SQL SERVER

COLAESEC() Function

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:

 

Thanks and happy coding!

Read More »

GLOBAL SEARCH IN SQL SERVER

--EXEC SearchAllTables 'A38010088'
--GO

Here is the complete stored procedure code:

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

    -- Copyright Ã�© 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Tested on: SQL Server 7.0 and SQL Server 2000
    -- Date modified: 28th July 2002 22:50 GMT


    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
   
            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END   
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

Read More »

UPDATE QUERY USING CASE STATEMENT IN SQL SERVER

One of the keys to SQL Server database performance if keeping your transactions as short as possible. In this article we will look at a couple of tricks using the CASE statement to perform multiple updates on a table in a single operation. By doing this, some transactions can be shorted, and performance boosted.

--First Create a Table
CREATE TABLE [dbo].[UsingCaseInUpdateQuery](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NOT NULL,
[gender] [varchar](50) NOT NULL,
[address] [varchar](100) NULL,)

--Insert Data in to the Table
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('yogesh','male','almora')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('yogesh1','male','almora')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('yogesh2','male','ranikhet')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('yogesh3','male','nainital')

insert into [UsingCaseInUpdateQuery] (name,gender,address) values('naina','female','almora')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('pooja','female','ranikhet')
insert into [UsingCaseInUpdateQuery] (name,gender,address) values('ritu','female','nainital')

--Once Check the data in the table
select * from [UsingCaseInUpdateQuery]
 

USING CASE STATEMENT IN A SQL UPDATE QUERY
--Now Update all the Male Gender with Female and Female with Male
Update Query Using Case for the fast execution of data
update [UsingCaseInUpdateQuery] set gender = case
when gender ='male' then 'female'
when gender = 'female' then 'male'
end

*Note that there is a definite “top-down” priority involved in the CASE statement.

Multiple Updates to a Single column:

If we code our update like the example below, then the table will only need to be read once. For large tables, this can save us a lot of disk I/O, especially if the query requires a table scan over a long table

Update [UsingCaseInUpdateQuery]
Set address =
Case
    When gender = 'female'
    Then 'Almora'
    When id = '5'
    Then 'Delhi'
End
Where id = '5' OR gender = 'female'

Continues…

Read More »

Exporting Data Table OR Dataset from SQL Server to Microsoft Excel in ASP.NET Using C#

Exporting Data from SQL Server to Excel

This code sample is based on Daniel Olson's DataTable2ExcelString (Dataset) method. View Demo

  

Read More »

ASP.NET SQL Server Registration Tool

Creating the Application Services Database for SQL Server

Using aspnet_regsql.exe

ASP.NET includes a tool for installing the SQL Server database used by the SQL Server providers, named Aspnet_regsql.exe. The Aspnet_regsql.exe tool is located in the drive:\WINDOWS\Microsoft.NET\Framework\versionNumber folder on your Web server. Aspnet_regsql.exe is used to both create the SQL Server database and add or remove options from an existing database.

The Aspnet_regsql.exe tool is located in the following folder:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe

Note *

The Aspnet_regsql tool should not be used with a local installation of SQL Server Express running in user instance mode (that is, the connection string contains User Instance=true).

Installing the Database using Aspnet_regsql.exe

You can also run the Aspnet_regsql.exe tool as a command-line utility. For example, the following command installs the database elements for membership and role management on the local computer running SQL Server:

aspnet_regsql.exe -E -S localhost -A mr

Read More »