Advertisements

Archive

Archive for July, 2012

How to Create and use Table-Valued Parameter in C# and T-SQL/ How to pass table to stored procedures in SQL

July 17, 2012 Leave a comment

What is Table – Valued Parameter in SQL Server 2008?

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

What we are going to do with Table – Valued Parameter?

We are going to demonstrate a very simple example for using Table – Valued parameter. In this sample project we will insert bulk amount of data into the table by passing a bulk data using datatable in C# to SQL stored procedure.

Create a Table for insert data using Table – Valued Parameter 

Here we are having a table named Officer and having three fields ID,Name and Salary. We are going to fill the table with bulk data.

CREATE TABLE Officer(
ID INT PRIMARY KEY IDENTITY(1,1),
NAME VARCHAR(50),
SALARY DECIMAL(18, 0))

Stored Procedure for insert data by accepting Table Valued Parameter

Now we are going to create a Stored Procedure that accepting a table type as parameter and insert values in this type into the table.

CREATE PROCEDURE InsertOfficerDetails
(
@OfficerData OfficerDetails readonly
)
AS 
INSERT INTO Officer (Name, Salary)
SELECT Name, Salary
FROM @OfficerData;

C# code to call Stored Procedure to insert data in to the table using Table – Valued Parameter

We are creating a simple ASPX page with a single button. When we click this button we are calling above stored procedure by creating and passing some amount of sample data to the stored procedure as Table – Valued Parameter.

ASPX Page 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CallSP.aspx.cs" 
Inherits="ExperimentLab.CallSP" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
 <title></title>
</head>
<body>
 <form id="form1" runat="server">
 <div>
 <asp:Button ID="btnCallSP" runat="server" Text="Call SP" 
 OnClick="btnCallSP_Click" />
 </div>
 </form>
</body>
</html>

Code Behind

protected void btnCallSP_Click(object sender, EventArgs e)
 {
 try
 {
 DataTable dt = new DataTable();
 DataColumn dtCol = new DataColumn();
 dtCol.ColumnName = "ID";
 dt.Columns.Add(dtCol);
dtCol = new DataColumn();
 dtCol.ColumnName = "Name";
 dt.Columns.Add(dtCol);
dtCol = new DataColumn();
 dtCol.ColumnName = "Salary";
 dt.Columns.Add(dtCol);
for (int i = 0; i < 10; i++)
 {
 DataRow dr = dt.NewRow();
 dr["Name"] = "Name " + i;
 dr["Salary"] = 1000 + i;
 dr["ID"] = i;
 dt.Rows.Add(dr);
 }
 
 string connStr = ConfigurationManager.
 AppSettings["LocalSqlServer"].ToString();
 SqlConnection con = new SqlConnection(connStr);
using (var conn = new SqlConnection(connStr))
 using (var cmd = conn.CreateCommand())
 {
 cmd.Connection = con;
 con.Open();
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.CommandText = "dbo.InsertOfficerDetails";
 SqlParameter param = cmd.Parameters.AddWithValue("@OfficerData", dt);
 cmd.ExecuteNonQuery();
 }
 }
 catch (Exception a)
 {
 Response.Write(a.Message);
 }
 }

Web.Config

<appSettings>
 <add key="LocalSqlServer" 
value="Database=testDB;Server=Servername\SQLEXPRESS;User Id=userid;Password=password"/>
</appSettings>

Hence we discussed about how to create and use Table valued parameters, how to create a store procedure with table type as parameter, how to pass table to stored procedure in C#/Asp.Net,  how to insert multiple rows of data to a table with table valued parameter in SQL, how to insert bulk data to SQL table using Table Valued Parameter in SQL Server 2008 etc..

 

Advertisements

How to UNPIVOT table in SQL Server / UNPIVOT table example in SQL Server

July 12, 2012 10 comments

What is PIVOT and UNPIVOT operator in SQL Server 

We can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Please go through our previous post for PIVOT sample in SQL

Before UNPIVOT

UNPIVOT sample in SQL Server

Before UNPIVOT table

After UNPIVOT

UNPIVOT sample in SQL Server

UNPIVOT sample in SQL Server

A simple UNPIVOT sample in SQL Server

Here we are going to demonstrate a very simple UNPIVOT sample without any complexity. We are having a table named EmploymentHistory and its containing employee name with previous company name that particular employee worked.

Table Structure For showing simple UNPIVOT sample

CREATE TABLE EmploymentHistory 
(Id INT, 
Employee VARCHAR(500), 
Company1 VARCHAR(500), 
Company2 VARCHAR(500), 
Company3 VARCHAR(500)
) 
GO

-- Load Sample data
INSERT INTO EmploymentHistory SELECT 
1, 'John', 'Tata Motors', 'Ashok Leyland', 'Suzuki'
UNION ALL SELECT 
2, 'Kate', 'Airtel', 'Vodafone', 'Tata Docomo'
UNION ALL SELECT 
3, 'Sam', 'Hercules', 'Hero', 'Atlas'
GO

In order to UNPIVOT above table we can use below mentioned script. The result should be as Employee with each company as a single row.

SELECT Id, 
ROW_NUMBER()OVER(Order By ID) as NewID,
Employee, 
Company
FROM 
( 
SELECT Id, Employee, Company1, Company2, Company3
FROM EmploymentHistory 
) Main
UNPIVOT
( 
Company FOR Companies IN (Company1, Company2, Company3) 
) Sub

UNPIVOT Table with multiple columns

Now we are going to UNPIVOT table with multiple columns. Suppose we are having a table with Employee name, list of company names that particular employee worked and corresponding cities also. The table structure like below.

CREATE TABLE EmploymentHistoryWithCity 
(Id INT, 
Employee VARCHAR(500), 
Company1 VARCHAR(500), 
Company2 VARCHAR(500), 
Company3 VARCHAR(500),
City1 VARCHAR(500), 
City2 VARCHAR(500), 
City3 VARCHAR(500) 
) 
GO
-- Load Sample data 
INSERT INTO EmploymentHistoryWithCity SELECT
1, 'John', 'Tata Motors', 'Ashok Leyland', 'Suzuki', 'Mumbai', 'Kolkata', 'Delhi'
UNION ALL SELECT 
2, 'Kate', 'Airtel', 'Vodafone', 'Tata Docomo', 'Chennai', 'Kolkata', 'Banglore'
UNION ALL SELECT 
3, 'Sam', 'Hercules', 'Hero', 'Atlas', 'Delhi', 'Mumbai', 'Banglore'
GO
UNPIVOT sample in SQL Server

Before UNPIVOT multiple columns table

 

In order to UNPIVOT above table we can use below mentioned script. The result should be as Employee name with Company1 and City1 as a first row, Employee Name with Company2 and City2 as second row and so on
SELECT Id, 
ROW_NUMBER()OVER(Order By ID) as NewID,
Employee, 
Company, 
City 
FROM 
( 
SELECT Id, Employee, Company1, Company2, Company3, City1, City2, City3
FROM EmploymentHistoryWithCity 
) Main
UNPIVOT 
( 
Company FOR companies IN (Company1, Company2, Company3) 
) Sup
UNPIVOT 
( 
City For Cities IN (City1, City2, City3 ) 
) Ct
WHERE RIGHT(companies,1) = RIGHT(Cities,1)
UNPIVOT sample in SQL Server

After UNPIVOT multiple columns table 

 

How to PIVOT table in SQL Server / PIVOT table example in SQL Server

July 10, 2012 2 comments

What is PIVOT and UNPIVOT operator in SQL Server 

We can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Please go through our post for simple UNPIVOT example

Before PIVOT

Before PIVOT table

After PIVOT

After PIVOT table

A simple PIVOT sample in SQL Server

Here we are going to demonstrate a very simple PIVOT sample without any complexity. We are having a table named Accounts and it containing customer name and their deposit with denominations.

Table Structure For showing simple PIVOT sample

CREATE TABLE Accounts(Customer VARCHAR(25), Denomination VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','10 $',2)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','50 $',6)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','100 $',1)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','10 $',4)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','50 $',3)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','100 $',11)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','10 $',20)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','50 $',12)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','100 $',2)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','10 $',0)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','50 $',5)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','100 $',5)

In order to PIVOT above mentioned table we can use below script. The result should be as Customer name with all denomination will be coming as columns with qty as values for each column.

SELECT * FROM Accounts
PIVOT (SUM(QTY) For Denomination IN ([10 $],[50 $],[100 $])) AS Total

Dynamic Query to PIVOT table for dynamic columns 

In the above example we are using a simple structured table and then PIVOT with denomination values. This can be achieved only when we are having denomination values as static. Suppose this denomination values are dynamic (Each country having different denomination like $,EUR, IND etc..), we need to create a dynamic query to PIVOT above table.

Suppose we are having different table for getting Denomination values and we are going to take Denomination values from this table at run time as dynamic.

CREATE TABLE Denomination(Value VARCHAR(25))
GO
INSERT INTO Denomination(Value)
VALUES('10 $')
INSERT INTO Denomination(Value)
VALUES('50 $')
INSERT INTO Denomination(Value)
VALUES('100 $')

First of all, we need to get dynamic columns names from the above table. After that we can create a dynamic query with these columns.

Declare @ColumnNames VARCHAR(100);
SELECT @ColumnNames = COALESCE(@ColumnNames+ ',','') +
'['+ Cast(Value AS VARCHAR(50)) +']' FROM Denomination cust
PRINT @ColumnNames
DECLARE @DynamicQuery Varchar(MAX);
SET @DynamicQuery = '
SELECT * FROM Accounts
PIVOT (SUM(QTY) For Denomination IN (' + @ColumnNames + ')) AS Total'
EXEC (@DynamicQuery);

How to Calculate Age in Sql Server OR Find age using SQL query

July 3, 2012 2 comments

Calculate age of employee using SQL Server

To calculate an age from the SQL table is little bit tricky.  We are demonstrating how to calculate the age from the date of birth fields in the SQL table. While fill the application form it’s better to fill the Date Of Birth field instead of age field. Because age field can calculate from the Date Of Birth field easily and accurately.

Find the age from the Date Of Birth using SQL Server

Here we are having a table called Employee and having coloumns ID,Name and DOB. From this table we need to find the age of each employee using SQL script.

 

Please see the table structure.

Calculate age from SQL Server

We can use Date Diff function in SQL Server to get the age of each employee. But if we consider date field only it will get wrong answer because it will consider the current year even date of birth not came this year. The below script and result is example.

 

Calculate age from SQL Server

 

For avoiding above mentioned issue, we can use below script to find out the age from the Date Of Birth field using SQL Server.

SELECT DOB AS DateOfBirth,
GETDATE() AS CurrentDate,
DATEDIFF(YEAR,DOB,GETDATE()) - 
(CASE WHEN 
 DATEADD(YY,DATEDIFF(YEAR,DOB,GETDATE()),DOB) > GETDATE()
 THEN 1
 ELSE 0 END) AS Age
FROM Employee
Find the age using SQL Server

Calculate age using SQL Server


								
%d bloggers like this: