XML Pass and insert into Table

DECLARE @ProductMapping XML

SET @ProductMapping='

Table1>
ProductId>1
InclusionDescription>0
SubCatID>0
CreditLimit>0
Quantity>10
CreatedBy>15
/Table1>
Table1>
ProductId>2
InclusionDescription>0
SubCatID>0
CreditLimit>0
Quantity>10
CreatedBy>15
/Table1>
Table1>
ProductId>3
InclusionDescription>0
SubCatID>0
CreditLimit>0
Quantity>10
CreatedBy>15
/Table1>
Table1>
ProductId>0
InclusionDescription>0
SubCatID>1
CreditLimit>10
Quantity>0
CreatedBy>15
/Table1>
Table1>
ProductId>0
InclusionDescription>0
SubCatID>2
CreditLimit>10
Quantity>0
CreatedBy>15
/Table1>
Table1>
ProductId>0
InclusionDescription>xxx
SubCatID>0
CreditLimit>0
Quantity>123
CreatedBy>15
/Table1>
Table1>
ProductId>0
InclusionDescription>yyy
SubCatID>0
CreditLimit>0
Quantity>10
CreatedBy>15
/Table1>
/DocumentElement>'


SELECT
PackageInclus.COL.value('ProductId[1]','INT') AS ProductId,
PackageInclus.COL.value('InclusionDescription[1]','NVARCHAR(500)') AS InclusionDescription,
PackageInclus.COL.value('SubCatID[1]','INT') AS SubCatID,
PackageInclus.COL.value('CreditLimit[1]','MONEY') AS CreditLimit,
PackageInclus.COL.value('Quantity[1]','INT') AS Quantity,
PackageInclus.COL.value('CreatedBy[1]','INT') AS CreatedBy
FROM @ProductMapping.nodes(N'//DocumentElement/Table1') AS PackageInclus(COL);

UnPivot Table Like (Colum to Row) Stored Procedure

ALTER PROCEDURE UNPIVOTTABLE
(
@TABLENAME AS VARCHAR(MAX),
@WHERECLAUSE AS VARCHAR(MAX) = NULL

)
AS
BEGIN


DECLARE @COLUMN_NAME_ORIGNAL AS VARCHAR(MAX)
DECLARE @COLUMN_NAME AS VARCHAR(MAX)
DECLARE @SQL AS VARCHAR(MAX)

SET @COLUMN_NAME = ''
SET @SQL = ''
SET @COLUMN_NAME_ORIGNAL = ''



SELECT
@COLUMN_NAME_ORIGNAL = @COLUMN_NAME_ORIGNAL + COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME



SELECT
@COLUMN_NAME = @COLUMN_NAME + 'ISNULL(' + 'CAST(' + CAST(ISNULL(COLUMN_NAME,'') AS NVARCHAR(MAX)) + ' AS VARCHAR(MAX)) ,'+ CHAR(39) + '' + CHAR(39) + ') '+ COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME

SELECT @COLUMN_NAME = SUBSTRING(@COLUMN_NAME,0,LEN(@COLUMN_NAME))
SELECT @COLUMN_NAME_ORIGNAL = SUBSTRING(@COLUMN_NAME_ORIGNAL,0,LEN(@COLUMN_NAME_ORIGNAL))


SET @SQL = 'SELECT ' + @COLUMN_NAME + ' INTO ##TEMPUNPIVOT FROM ' + @TABLENAME + + CASE WHEN @WHERECLAUSE IS NULL THEN '' ELSE ' WHERE ' + @WHERECLAUSE END

EXEC (@SQL)


--SELECT @COLUMN_NAME_ORIGNAL

--SELECT * from ##TEMPUNPIVOT

SET @SQL = 'SELECT COLUMN_NAME,COLUMN_VALUE FROM (
SELECT ' + @COLUMN_NAME_ORIGNAL
+ ' From ##TEMPUNPIVOT
) OBJ
UNPIVOT (
COLUMN_NAME FOR
COLUMN_VALUE IN ('
+ @COLUMN_NAME_ORIGNAL + '
)
) AS UNpvt'



--PRINT @SQL

EXEC (@SQL)

DROP TABLE ##TEMPUNPIVOT
END

make Calendar by grid view control

1.
CREATE FUNCTION [dbo].[DisplayCurrentWeekDays]
(@today SMALLDATETIME)
RETURNS @WeekDateDay TABLE
(
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
/*
Purpose: To return the weekly calendar for the week any date
By: Pinal Dave -- SQLAuthority.com
On: 6/8/2007
*/
BEGIN
DECLARE @day INT
SET @today = CAST(CONVERT(VARCHAR(10), @today, 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
INSERT INTO @WeekDateDay (Sunday, Monday,
Tuesday, Wednesday, Thursday, Friday, Saturday)
SELECT DATEADD(dd, 1 - @day, @today) Sunday,
DATEADD(dd, 2 - @day, @today) Monday,
DATEADD(dd, 3 - @day, @today) Tuesday,
DATEADD(dd, 4 - @day, @today) Wednesday,
DATEADD(dd, 5 - @day, @today) Thursday,
DATEADD(dd, 6 - @day, @today) Friday,
DATEADD(dd, 7 - @day, @today) Saturday
RETURN
END

----------
2.
Create the function named
CREATE FUNCTION [dbo].[GetSQLcalendar](@WhatDate SMALLDATETIME)
RETURNS @WeekDateDay TABLE
(
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
BEGIN
/*
Purpose: To return a calendar fOR whatever MONTH you want,
driven by the date you pick AS a variable.
By: Dan Golden
On: 2/20/2008
*/
DECLARE @FourWeeksAgo SMALLDATETIME
SET @FourWeeksAgo = DATEADD(d,-28,@WhatDate)
DECLARE @ThreeWeeksAgo SMALLDATETIME
SET @ThreeWeeksAgo = DATEADD(d,-21,@WhatDate)
DECLARE @TwoWeeksAgo SMALLDATETIME
SET @TwoWeeksAgo = DATEADD(d,-14,@WhatDate)
DECLARE @PreviousWeek SMALLDATETIME
SET @PreviousWeek = DATEADD(d,-7,@WhatDate)
DECLARE @ThisWeek SMALLDATETIME
SET @ThisWeek = (@WhatDate)
DECLARE @NextWeek SMALLDATETIME
SET @NextWeek = DATEADD(d,7,@WhatDate)
DECLARE @LastWeek SMALLDATETIME
SET @LastWeek = DATEADD(d,14,@WhatDate)
DECLARE @AfterLastWeek SMALLDATETIME
SET @AfterLastWeek = DATEADD(d,21,@WhatDate)
DECLARE @TwoAfterLastWeek SMALLDATETIME
SET @TwoAfterLastWeek = DATEADD(d,28,@WhatDate)
INSERT INTO @WeekDateDay (Sunday, Monday, Tuesday,
Wednesday, Thursday, Friday, Saturday)
SELECT *
FROM
(
SELECT CASE MONTH(calendar.Sunday)
WHEN MONTH(@WhatDate)
THEN calendar.Sunday
ELSE NULL END AS 'Sunday',
CASE MONTH(calendar.Monday)
WHEN MONTH(@WhatDate)
THEN calendar.Monday
ELSE NULL END AS 'Monday',
CASE MONTH(calendar.Tuesday)
WHEN MONTH(@WhatDate)
THEN calendar.Tuesday
ELSE NULL END AS 'Tuesday',
CASE MONTH(calendar.Wednesday)
WHEN MONTH(@WhatDate)
THEN calendar.Wednesday
ELSE NULL END AS 'Wednesday',
CASE MONTH(calendar.Thursday)
WHEN MONTH(@WhatDate)
THEN calendar.Thursday
ELSE NULL END AS 'Thursday',
CASE MONTH(calendar.Friday)
WHEN MONTH(@WhatDate)
THEN calendar.Friday
ELSE NULL END AS 'Friday',
CASE MONTH(calendar.Saturday)
WHEN MONTH(@WhatDate)
THEN calendar.Saturday
ELSE NULL END AS 'Saturday'
FROM
(
SELECT *
FROM dbo.DisplayCurrentWeekDays(@FourWeeksAgo)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@ThreeWeeksAgo)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@TwoWeeksAgo)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@PreviousWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@ThisWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@NextWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@LastWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@AfterLastWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@TwoAfterLastWeek)
) calendar
) dates
WHERE dates.sunday IS NOT NULL
OR dates.monday IS NOT NULL
OR dates.tuesday IS NOT NULL
OR dates.wednesday IS NOT NULL
OR dates.thursday IS NOT NULL
OR dates.friday IS NOT NULL
OR dates.saturday IS NOT NULL
RETURN
END

-------

After that create the Stored Procedure which come with Date for which u want to see the calendar in the grid.
3.
Create Procedrue GetCalendar
@Date DATETIME
AS
SELECT ISNULL(DATEPART(DAY,CONVERT(NVARCHAR(20), Sunday, 101)), '') AS 'Sunday',
ISNULL(DATEPART(DAY,CONVERT(NVARCHAR(20), Monday, 101)), '') AS 'Monday',
ISNULL(DATEPART(DAY,CONVERT(NVARCHAR(20), Tuesday, 101)), '') AS 'Tuesday',
ISNULL(DATEPART(DAY,CONVERT(NVARCHAR(20), Wednesday, 101)), '') AS 'Wednesday',
ISNULL(DATEPART(DAY,CONVERT(NVARCHAR(20), Thursday, 101)), '') AS 'Thursday',
ISNULL(DATEPART(DAY,CONVERT(NVARCHAR(20), Friday, 101)), '') AS 'Friday',
ISNULL(DATEPART(DAY,CONVERT(NVARCHAR(20), Saturday, 101)), '') AS 'Saturday'
FROM dbo.GetSQLcalendar(CONVERT(SMALLDATETIME,@Date))

----
use the calendar control by which send the date & month year

Calendar ID="cldStaff" Font-Size="16px" BackColor="#c9c9c9" Width="180px" runat="server"
OnDayRender="cldStaff_DayRender" ShowDayHeader="False" OnVisibleMonthChanged="cldStaff_VisibleMonthChanged">

Paging IN SQL 2005

This is the SP For Paging IN SQL(2005)

Create PROCEDURE [dbo].[SelectBlogComments]
@Start INT,
@PageLength INT,
@NoOfPage INT OUTPUT
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @TotalRows INT
DECLARE @PageCheck INT

BEGIN
WITH BlogComments
AS

(Select Row_Number() Over(Order by CommentID)AS RowNo, *
From
[dbo].[BlogComments])

SELECT * FROM BlogComments WHERE RowNo>(@Start)*@PageLength And RowNo<=(@Start+1)* @PageLength

Select @TotalRows=COUNT (CommentID) FROM [dbo].[BlogComments]

SET @PageCheck=@TotalRows%@PageLength

IF(@PageCheck=0)

SET @NoOfPage=@TotalRows/@PageLength

ELSE

SET @NoOfPage=@TotalRows/@PageLength+1

END