@@transcount in sql server

-
Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection .
For ex :
PRINT @@TRANCOUNT
--  The BEGIN TRAN statement will increment the
--  transaction count by 1.
BEGIN TRAN
    PRINT @@TRANCOUNT
    BEGIN TRAN
        PRINT @@TRANCOUNT
--  The COMMIT statement will decrement the transaction count by 1.
    COMMIT
    PRINT @@TRANCOUNT
COMMIT
PRINT @@TRANCOUNT
--Results
0
1
2
1
0



Ex2 :- >

  Step 1:




create table a(m varchar(max), i int)


go


 


Step2 : 






declare @cnt int
--Explicit transaction
begin tran
set @cnt=@@trancount
insert into a select 'inside', @@trancount
insert into a select 'inside cnt', @cnt
commit tran

set @cnt=@@trancount

--Implicit transcation
insert into a select 'outside', @@trancount
insert into a select 'outside cnt', @cnt


 


 


Step 3 : 


 





select * from a


 


Output :

inside    2 
inside cnt             1


outside 2 
outside cnt         0



References :   http://blogs.msdn.com/b/jenss/archive/2010/07/10/usage-of-trancount-in-dml-statements.aspx 




http://technet.microsoft.com/en-us/library/ms187967


 




















































Comments











Post a Comment



















Popular posts from this blog









Add Serial no in crystal report without coding






-














Image







  Adding a serial number in grouping without any code. I have placed the step by step screenshots which will explain easily how to do it.         1) Add a new field in Running Total Fields in Field Explorer           Give a proper name to your Running total name. For example I have choosen here RTotal0. Now categorize based on which field in database you want to summarize it. Next is as we want serial number select count. If you want your serial number to start again from the start when the value in the group changes then select on change of group and specify the grouping name else keep the default value.           Smaple Preview of the Design View of Report where I Have added SRNo column at the start in grouping section.         








Read more










File operations in C# (.net)






-















  Here we are performing some basic file operations which widely needed in applications.   ASPX :       <div>          <asp:Button ID="Button1" runat="server" Text="CLick" onclick="Button1_Click" />          <asp:FileUpload ID="FileUpload1" runat="server" /><br /><br />           </div>   C# :         using System.IO;         using System.Text;            protected void Button1_Click(object sender, EventArgs e)          {               string path = @"E:\MVC Application Demos\ABC.txt";            // File Path taken               string name = FileUpload1.FileName.ToString();                // Here we get File Name only               string path1 = FileUpload1.PostedFile.FileName.ToString();    // Here we get File Path only               if (File.Exists(path.ToString()))              {                       //     ClientScript.RegisterStartupScript(this.GetType(), "myalert"








Read more










SQL – Generate decimal numbers sequence for particular number range






-














Image







  Friends, Sometimes we need mathematical assistance in SQL for such queries. Same here, it helps me to find challenging in all the way.   Some of my friends helps me out to crack the records for my query, Here I am sharing simple solution for this,   ;WITH cte      AS (SELECT Cast(-77 AS NUMERIC(22, 3)) AS num –Min value from your data          UNION ALL          SELECT Cast(num + 0.00001 AS NUMERIC(22, 5))          FROM   cte          WHERE  num < Cast(89 AS NUMERIC(22, 5))) — Max value from your data SELECT * FROM   cte OPTION (maxrecursion 0)    Try it    








Read more