TSQL Loop Through Table Rows

Sometimes we need to loop through some particular result set, and to perform some action on every row. we can use following logic.


DECLARE @TotalRecords INT = 0,
@Index INT = 1,
@LastName nVARCHAR(MAX)

-- get total number of records to loop
SELECT @TotalRecords = COUNT(1) FROM dbo.Users

WHILE(@Index <= @TotalRecords)
BEGIN

-- select what you want to
SELECT @LastName = LastName
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY UserId ASC) AS RowNumber,
UserId,
LastName
FROM Users
)TempTable
WHERE TempTable.RowNumber = @Index

-- use it as you like
PRINT @LastName

-- increment index by 1
SET @Index = @Index + 1
END

By Amer Posted in SQL

TSQL XML Update/Modify Specific Field Value

here is the quick sample code:




declare @xmldoc xml
set @xmldoc = N'<DS><DT><Level1>5</Level1><Level2>10</Level2><Level3>15</Level3><Path>5\10\15</Path></DT>
 <DT><Level1>50</Level1><Level2>10</Level2><Level3>15</Level3><Path>5\10\15</Path></DT></DS>'
SELECT
T.Item.value('Level1[1]', 'INT') AS Level1
FROM @xmldoc.nodes('//DS/DT') AS T(Item)

DECLARE @Index INT = 2
SELECT
 T.Item.value('Level1[1]', 'INT') AS Level1
FROM @xmldoc.nodes('//DS/DT[position() = sql:variable("@Index")]') AS T(Item)

SET @xmldoc.modify('replace value of
 (/DS/DT/Level1[1]/text())[1]
 with sql:variable("@Index")
')

SELECT
 T.Item.value('Level1[1]', 'INT') AS Level1
FROM @xmldoc.nodes('//DS/DT') AS T(Item)


 

MDSN link

 

TSQL XML Get Row By Position

To select a specific row (first,second…. N) from xml following code can be used

DECLARE @xmldoc XML
set @xmldoc = N'<DS><DT><Level1>5</Level1><Level2>10</Level2><Level3>15</Level3><Path>5\10\15</Path></DT>

<DT><Level1>50</Level1><Level2>10</Level2><Level3>15</Level3><Path>5\10\15</Path></DT></DS>'
 SELECT
 T.Item.value('Level1[1]', 'INT') AS Level1
 FROM @xmldoc.nodes('//DS/DT') AS T(Item)

DECLARE @Index INT = 2
 SELECT
 T.Item.value('Level1[1]', 'INT') AS Level1
 FROM @xmldoc.nodes('//DS/DT[position() = sql:variable("@Index")]') AS T(Item)

SQL Server Cursor Syntax


DECLARE cur_Org_Info CURSOR FOR
SELECT empManager_ID, empDeptId, OccupationalGroupId, UserGroupId FROM #tempTable WHERE edId IS NULL

OPEN cur_Org_Info
FETCH NEXT FROM cur_Org_Info INTO @d_empManager_ID,@d_empDeptId,@d_OccupationalGroupId,@d_UserGroupId

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ATPEmployeeDepartment(edID,empEmployee_ID, empManager_ID, empDeptId,OccupationalGroupId,UserGroupId)
VALUES (dbo.getATPEmployeeDepartment_ID(),@EmployeeId,@d_empManager_ID,@d_empDeptId,@d_OccupationalGroupId,@d_UserGroupId)

FETCH NEXT FROM cur_Org_Info INTO @d_empManager_ID,@d_empDeptId,@d_OccupationalGroupId,@d_UserGroupId
END

CLOSE cur_Org_Info
DEALLOCATE cur_Org_Info

How to have div min height

Basically when you specify a height to a div, ie6 will take it as minimum height. But ie7, firefox, chrome and modern browser will treat as height. So here is a simple way to have minimum height to all web browser.
Css Code:

.test{min-height:50px;height:auto!important;height:50px;}

For example, if you want to give 50px height to a div, Use the above code will work fine. That after 50px the height of the div will automatically expands.

This is the link to original post.

Ajax Using JQuery

 

$.blockUI();
$.ajax({
type: "POST",
url: "searchajax.aspx",
data: "haction=talentchange&talentID=" + talentid+"&stage="+stage,
dataType: "json",
success: function (ret) {
alert('success');
alert(ret.d);
});
},
error: function () {
alert("Failed to load");
}
});
$.unblockUI();

DBCC RESEED Table Identity Value – Reset Table Identity

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.


     DBCC CHECKIDENT (yourtable, RESEED, 34)

If table has to start with an identity of 1 with the next insert then table should be reseeded with the identity to 0. If identity seed is set below values that currently are in table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.

Generate RTF Table From Data Table, Insert RTF Table in RichTextBox

In some cases we need to append text in rich text editor control as table. I googled it around much, but cant find any solution. So, I implement my own solution of converting DataTable to rtf table – code. This is pretty much easy after diving into rtf code rules. Here it is, you can configure this solution for your own requirements easily.

        private string GetRtfTable(DataTable pDt)
        {
            string rtf = "";
            // Width will be in TWIPS
            int LeftColWidth = 4000, OtherColumnsWidth = 2000;
            int ColumnPosition;

            DataRow dr;
            for (int i = 0; i < pDt.Rows.Count; i++)
            {
                dr = pDt.Rows[i];
                // STARTING TABLE ROW
                rtf += @"{\trowd\trleft0\trgaph-0\trbrdrt\brdrnone\trbrdrb\brdrnone\trbrdrr\brdrnone\trbrdrl\brdrnone\trbrdrv\brdrnone\trbrdrh\brdrnone\trftsWidth1\trftsWidthB3\trpaddl10\trpaddr10\trpaddb10\trpaddt10\trpaddfl3\trpaddfr3\trpaddft3\trpaddfb3\trql\ltrrow";
                //rtf += @"{\trowd\trleft0\trgaph-0\trbrdrt\brdrs\brdrw15\brdrcf0\trbrdrb\brdrs\brdrw15\brdrcf0\trbrdrr\brdrs\brdrw15\brdrcf0\trbrdrl\brdrs\brdrw15\brdrcf0\trbrdrv\brdrs\brdrw15\brdrcf0\trbrdrh\brdrs\brdrw15\brdrcf0\trftsWidth1\trftsWidthB3\trpaddl10\trpaddr10\trpaddb10\trpaddt10\trpaddfl3\trpaddfr3\trpaddft3\trpaddfb3\trql\ltrrow";
                // LEFT COLUMN DEFINITION
                ColumnPosition = LeftColWidth;
                rtf += @"\clvertalt\clbrdrt\brdrnone\clbrdrl\brdrnone\clbrdrb\brdrnone\clbrdrr\brdrnone\clftsWidth3\clwWidth" + LeftColWidth.ToString() + @"\cellx" + ColumnPosition.ToString();
                // OTHER COLUMNS DEFINITION
                for (int j = 1; j < pDt.Columns.Count; j++)
                {
                    ColumnPosition += OtherColumnsWidth;
                    rtf += @"\clvertalt\clbrdrt\brdrnone\clbrdrl\brdrnone\clbrdrb\brdrnone\clbrdrr\brdrnone\clftsWidth3\clwWidth" + OtherColumnsWidth.ToString() + @"\cellx" + ColumnPosition.ToString();
                }

                rtf += "{";
                // PLACING DATA
                for (int j = 0; j < pDt.Columns.Count; j++)
                {
                    if (i == 0)
                        rtf += @"{\fs22\f3\b\intbl {\ul\ltrch " + dr[j].ToString() + @"}\li0\ri0\sa0\sb0\fi0\ql\sl15\slmult0\cell}";
                    else
                        rtf += @"{\fs22\f3\intbl {\ltrch " + dr[j].ToString() + @"}\li0\ri0\sa0\sb0\fi0\ql\sl15\slmult0\cell}";
                }

                rtf += "}";
                // ENDING TABLE ROW
                rtf += @"{\trowd\trleft0\trgaph-0\trbrdrt\brdrnone\trbrdrb\brdrnone\trbrdrr\brdrnone\trbrdrl\brdrnone\trbrdrv\brdrnone\trbrdrh\brdrnone\trftsWidth1\trftsWidthB3\trpaddl10\trpaddr10\trpaddb10\trpaddt10\trpaddfl3\trpaddfr3\trpaddft3\trpaddfb3\trql\ltrrow";
                //rtf += @"{\trowd\trleft0\trgaph-0\trbrdrt\brdrs\brdrw15\brdrcf0\trbrdrb\brdrs\brdrw15\brdrcf0\trbrdrr\brdrs\brdrw15\brdrcf0\trbrdrl\brdrs\brdrw15\brdrcf0\trbrdrv\brdrs\brdrw15\brdrcf0\trbrdrh\brdrs\brdrw15\brdrcf0\trftsWidth1\trftsWidthB3\trpaddl10\trpaddr10\trpaddb10\trpaddt10\trpaddfl3\trpaddfr3\trpaddft3\trpaddfb3\trql\ltrrow";
                // LEFT COLUMN
                ColumnPosition = LeftColWidth;
                rtf += @"\clvertalt\clbrdrt\brdrnone\clbrdrl\brdrnone\clbrdrb\brdrnone\clbrdrr\brdrnone\clftsWidth3\clwWidth" + LeftColWidth.ToString() + @"\cellx" + ColumnPosition.ToString();
                // OTHER COLUMNS
                for (int j = 1; j < pDt.Columns.Count; j++)
                {
                    ColumnPosition += OtherColumnsWidth;
                    rtf += @"\clvertalt\clbrdrt\brdrnone\clbrdrl\brdrnone\clbrdrb\brdrnone\clbrdrr\brdrnone\clftsWidth3\clwWidth" + OtherColumnsWidth.ToString() + @"\cellx" + ColumnPosition.ToString();
                }
                rtf += @"\row}}";
            }
            return rtf;
        }

This is Input to above function:

And this is the generated output:

🙂