.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Table variables do not re-initialize in a loop

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

Hi all,

I came across a peculiar problem while working with table variables. Here is a simple code to demonstrate it:


declare @c tinyint

set @c = 1

while @c <= 2
    declare @t table (srn int identity(1,1), name varchar(5))
    insert into @t(name) select 'A'
    insert into @t(name) select 'B'

    select * from @t
    set @c = @c + 1

The table variable does not get re-initialized and continues with the same identity seed for the next iteration.

I was wondering if anybody can throw some light on this behaviour of table variables where they do not get re-initialized with a declare statement. I worked around this problem by creating a temporary table and dropping at the end of each iteration nevertheless I was wondering if any tweak can help with using the table variable.



View Complete Post

More Related Resource Links

Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Computed Columns in Table Variables or Table Types

I was just looking for confirmation that computed columns can or cannot be used in a @Table Variable or User-Defined Table Type. I'm trying this: declare @testtable as table(  [testcol] as case when [testcol] = '' then 'a' else 'b' end,  [testcol2] varchar(100) ); insert into @testtable select '','someval' insert into @testtable select 'c','someval2' select * from @testtable It all parses fine, but during the first insert an error is returned Msg 402, Level 16, State 1, Line 2 The data types void type and varchar are incompatible in the equal to operator. Thank you, Paul

C# Allowing user to select variables to use in a foreach loop

Hi guys, First project, first post. My app builds webpages and personalises the content by running a foreach loop through rows in a dataset. My original web page build was hardcoded and works fine. I want to take this one stage further and allow users to paste their own html into a text box and use button controls to insert values from the dataset arrays.  Doing this however leads to the app writing the string verbatim. Some examples at this point might help:  Original code here System.IO.StreamWriter objWriter; objWriter = new System.IO.StreamWriter(webpage);                     objWriter.WriteLine("<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 4.01 Transitional//EN' 'http://www.w3.org/TR/html4/loose.dtd'>"); objWriter.WriteLine("<head>"); objWriter.WriteLine("<title>" + row.ItemArray.GetValue(0).ToString() + "'s very own webpage" + "</title>";); objWriter.WriteLine("<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"); objWriter.WriteLine(""); objWriter.WriteLine("</head>"); objWriter.WriteLine("<body>"); objWriter.WriteLine("<h1>Well hello there " + row.ItemArray.GetValue(5).ToStri

Executing sql statment stored in temp table (without variables)


How could I execute a sql statement stored in a temp table? something like




'select * from sysobjects' as test into #tmp




Loop through table records using variable


I know it’s possible to loop through table records using a variable and a For Each Loop Task. But is there a way to use the values in the variable and use them in a File System Task? When I tried, the File System Task complains that the variable is not a string. Any thoughts?


Objective – I need to loop though column values in a table which has the paths to the files that I’ll need to move using File System Task.   The table only has one column which stores the paths to the files to be moved.


Thanks in advance.

Loop a Table Like for each syntax


Using SQL server 2008.

I have a table whose rows contains parameters for processing 

I need to process in order

I apply two methods but both  fails and give exceptions......

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

For a short Description

1st Method : Take temporary table and add column row and loop by row number

insert into #tempMyTable(USERID,UTIME,CHECKTYPE,Row)

select USERID,<

How to bind data from linq to html table without a single line of loop.




This is the above code i have worked using querysing and displaying the listitem with in  a simple html table. Here I am using foreach statement to bind items. How to bind the data to a simple html table using SharePoint linq without  a single line of loop.

using System;
using System.ComponentModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Linq;
using Microsoft.SharePoint.Linq;

namespace QuerystringProgramWithoutLoop.QuerystringProgramWithoutLoop
    public class QuerystringProgramWithoutLoop : WebPart
        string strTitle=string.Empty;
        string strStatus=string.Empty;
        protected override void Render(HtmlTextWriter writer)

foreach loop - insert record in table



i createa a data flow task and read the records from a table to recordset.

now i inserted a foreach loop container which needs to go thru each record loaded in recordset from dataflow task. i made enumerator to foreach as ado enumerator and set the ado object source variable to recordset variable name from data flow task. also radiobox , rows in the first table is marked.

i also mapped the variable in foreach loop.


my question is how i can use these variable to insert variable values in antoher table?


mark it as answer if it answered your question :)

Null value causing problems in variables populated from table during fetch



I have some tsql that interates through a table, pulls the column values from the table into variables and then uses these variables to construct another variable to use as the @body parameter for an email.

The code works perfectly as long as all the columns in the table have values however, if any are null then the variable is not constructed and the body of the email is blank.

How can I deal with the fact that there will always be some columns in my table that are null?


Loop is failing when writing to table


My site extracts data from a MS SQL db and then connects to a mySql db which it writes the data to. My site is working properly when connecting to a db in cPanel at a remote website.

However, I have a mysql db also hosted on my site, and I'm connecting to it ok, but the loop is failing. There are 2 tables, one stores the category, and the other stores link data.

For each category

write to the category table

  for each link in this category

 write to the link table



The tables are being created, and the very first category is written to the table, but then nothing gets written to the link table.

How can I make this generate an error message? My webhost doesn't know what to do about it and they just want to see an error message.

I tried this once already, and neither of my lines worked:

HttpContext.Current.Response.Write(lnkid & " " & url)

Why doesn't that get written into the source of the page on each iterance of the loop? How do I see the data that isn't being written?

Public Shared Function TransferDatabase(ByVal websiteID As Integer) As Boolean
        Dim con As New MySqlConnection(getMySqlConnectionString(websiteID))

        Catch ex As Exception

Table Variables Stored Procedure NOLOCK


Sql Server 2008 R2

The error is "... can not continue scan with NOLOCK..".  These are the conditions:

- NOLOCK is not specified in any query. Transaction level is READ_COMMITTED
- When tested as a stand alone query batch, the operation completes successfully.
- When tested from withing a stored procedure context, the NOLOCK error occurs.
- There are no persisted tables being modified.

The below is just a snip. There are 5 levels all similar following the same pattern. 
Example: level 4 would include aliases M0 thru M4 as left outer joins on @menu and M5 as a left outer join @menuInterface.

Why would executing in a batch not return an error and executing in a stored procedure context cause a NOLOCK error? 

insert @menuInterface (
 '<menuItem level="3' + M3.[menuXML] + '&

loop through table with regex


I need to loop through tables on a web page and combine table rows with 1 <td> tag. I'm replace </td></tr><tr><td> with a <br /> tag. The problem is when there's more than one <td> tag in the same <tr>.

            <td>line 1</td>
            <td>line 2</td>
            <td>line 3a</td>
            <td>line 3b</td>
            <td>line 4</td>

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Microsoft's Collaborative Application Markup Language (CAML) Loop

discovered an interesting error recently while working with Microsoft's Collaborative Application Markup Language (CAML) that, surprisingly, had received no ink. Partly what surprises me about this is that the error may require you to rewrite large sections of your code if you haven't previously considered this SharePoint limitation. I'll start with some context, but first of all the error is:

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Using jQuery to Filter Table Rows

The project is using the .net GridView control, so I had limited control over the output HTML code. Still, I think this code can work for most tables. One thing to notice: you should use the class "filterable" on your table or on one of its parents for the code to work.
First, we need a text box:

RadioButton Groups, Table Rows And NamingContainers in asp.net

The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend