BEGIN TRY
BEGIN TRAN
--put queries here
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
BEGIN
SELECT @@ERROR,ERROR_MESSAGE ( )
ROLLBACK;
END
END CATCH
any one can use above code to manage transaction as well as error handling in sql server.
Tuesday, January 24, 2012
Ideal SQL Query For Handling Error & Transcation in MS SQL
BEGIN TRY
BEGIN TRAN
--put queries here
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
BEGIN
SELECT @@ERROR,ERROR_MESSAGE ( )
ROLLBACK;
END
END CATCH
any one can use above code to manage transaction as well as error handling in sql server.
Tuesday, May 10, 2011
Capitalize the First Character of Each Word using queries
I have found t-sql challenges question where they were asked to provide a solution of oracle INITCAP function in sql.
I was started to find the solution of INITCAP function & found lots of user defined function.
But here I have shared a solution where queries doesn't use any user defined function as well as any variable.
I was started to find the solution of INITCAP function & found lots of user defined function.
But here I have shared a solution where queries doesn't use any user defined function as well as any variable.
--First of all create one temp table
DECLARE @t TABLE(ID INT IDENTITY, Data VARCHAR(1000))
--insert the sample data
INSERT INTO @t
SELECT 'yogesh bhadauriya'
--use the Common table expression
;with CTE AS
(
--anchor query where we are finding the first character for replacing with uppercase
SELECT Cast(STUFF(LOWER(Data), 1, 1, UPPER(LEFT(Data, 1))) As varchar(max)) As Data,
PATINDEX('%[^a-zA-Z][a-z]%', Data COLLATE Latin1_General_Bin) As Position,ID,Data As OldData
From @t
Union All
--recursive query
SELECT Cast(STUFF(CTE.Data, Position, 2, UPPER(SUBSTRING(CTE.Data, CTE.Position, 2))) As Varchar(max)) as Data,
Cast(PATINDEX('%[^a-zA-Z][a-z]%', CTE.Data COLLATE Latin1_General_Bin) As Int) As Position,CTE.ID,T.Data As OldData
From @t As T
Inner Join CTE On CTE.ID=CTE.ID
where CTE.Data is not null
)
Select ID,OldData As Data,Data As Results
From CTE
where Data is not null
and Position=0
---output
--Id Data Results
--1 yogesh bhadauriya Yogesh Bhadauriya
Friday, April 29, 2011
User defined function to convert the number into charcter
How to convert the numeric value into its specific character? If this is your question, than create the following User Defined Function into your database.
To check the output of the above User Defined Function, execute the below query into your database.
--
Happy Coding to All
--create the following function
CREATE FUNCTION dbo.udf_Num_ToWords
(
@Number Numeric (38, 0) -- Input number with as many as 18 digits
) RETURNS VARCHAR(8000)
/*
* Converts a integer number as large as 34 digits into the
* equivalent words. The first letter is capitalized.
*
* Attribution: Based on NumberToWords by Srinivas Sampath
* as revised by Nick Barclay
*
* Example:
select dbo.udf_Num_ToWords (1234567890) + CHAR(10)
+ dbo.udf_Num_ToWords (0) + CHAR(10)
+ dbo.udf_Num_ToWords (123) + CHAR(10)
select dbo.udf_Num_ToWords(76543210987654321098765432109876543210)
DECLARE @i numeric (38,0)
SET @i = 0
WHILE @I <= 1000 BEGIN
PRINT convert (char(5), @i)
+ convert(varchar(255), dbo.udf_Num_ToWords(@i))
SET @I = @i + 1
END
*
* Published as the T-SQL UDF of the Week Vol 2 #9 2/17/03
****************************************************************/
AS BEGIN
DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)
IF @Number = 0 Return 'Zero'
-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
, @outputString = ''
, @counter = 1
SELECT @length = LEN(@inputNumber)
, @position = LEN(@inputNumber) - 2
, @loops = LEN(@inputNumber)/3
-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1
-- insert data for the numbers and words
INSERT INTO @NumbersTable SELECT '00', ''
UNION ALL SELECT '01', 'one' UNION ALL SELECT '02', 'two'
UNION ALL SELECT '03', 'three' UNION ALL SELECT '04', 'four'
UNION ALL SELECT '05', 'five' UNION ALL SELECT '06', 'six'
UNION ALL SELECT '07', 'seven' UNION ALL SELECT '08', 'eight'
UNION ALL SELECT '09', 'nine' UNION ALL SELECT '10', 'ten'
UNION ALL SELECT '11', 'eleven' UNION ALL SELECT '12', 'twelve'
UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
UNION ALL SELECT '15', 'fifteen' UNION ALL SELECT '16', 'sixteen'
UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
UNION ALL SELECT '30', 'thirty' UNION ALL SELECT '40', 'forty'
UNION ALL SELECT '50', 'fifty' UNION ALL SELECT '60', 'sixty'
UNION ALL SELECT '70', 'seventy' UNION ALL SELECT '80', 'eighty'
UNION ALL SELECT '90', 'ninety'
WHILE @counter <= @loops BEGIN
-- get chunks of 3 numbers at a time, padded with leading zeros
SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)
IF @chunk <> '000' BEGIN
SELECT @tensones = SUBSTRING(@chunk, 2, 2)
, @hundreds = SUBSTRING(@chunk, 1, 1)
, @tens = SUBSTRING(@chunk, 2, 1)
, @ones = SUBSTRING(@chunk, 3, 1)
-- If twenty or less, use the word directly from @NumbersTable
IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE @tensones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' thousand '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
+ @outputString
END
ELSE BEGIN -- break down the ones and the tens separately
SET @outputString = ' '
+ (SELECT word
FROM @NumbersTable
WHERE @tens + '0' = number)
+ '-'
+ (SELECT word
FROM @NumbersTable
WHERE '0'+ @ones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
+ @outputString
END
-- now get the hundreds
IF @hundreds <> '0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE '0' + @hundreds = number)
+ ' hundred '
+ @outputString
END
END
SELECT @counter = @counter + 1
, @position = @position - 3
END
-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, ' ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)
RETURN @outputString -- return the result
END
To check the output of the above User Defined Function, execute the below query into your database.
--now run the following query to get the result select dbo.udf_Num_ToWords(5800000) --output --Five thousand eight hundred thousand
--
Happy Coding to All
Recursive Query To Find All Child Node in SQL server 2005+
How to find the all child of particular parent using the Recursive Query
--here we are creating the table variable so that we can insert some dummy record
Declare @Table Table
(
TId int,
ParentId int,
Name varchar(10)
)
--inserting some records
--using the union all to insert more than one records
insert into @Table
Select 1,NULL,'ICT'
Union All
Select 2,1,'ICT-M1'
Union All
Select 4,1,'ICT-M2'
Union All
Select 7,2,'ICT-M1U1'
Union All
Select 8,2,'ICT-M1U2'
Union All
Select 9,4,'ICT-M2U1'
Union All
Select 10,4,'ICT-M2U2'
Union All
Select 11,7,'ICT-M1U1P1'
Union All
Select 12,7,'ICT-M1U1P2'
Union All
Select 13,8,'ICT-M1U2P1'
Union All
Select 14,8,'ICT-M1U2P2'
Union All
Select 15,9,'ICT-M2U1P1'
Union All
Select 16,9,'ICT-M2U1P2'
Union All
Select 17,10,'ICT-M2U2P1'
Union All
Select 18,10,'ICT-M2U2P2'
--variable to hold data
Declare @ChildNode varchar(1000)
Set @ChildNode='';
--use the standard recursive query
;with [CTE] as
(
--anchor query where we are finding the all parents
select TId,ParentId,Name,CAST(ISNULL(CAST(ParentId as varchar(10)),'0') As Varchar(100)) As ChildNode
from @Table c where c.[ParentId] is null
union all
--recursive query where we are finding the all child according to anchor query parent
select c.TId,c.ParentId,c.Name,
CAST( p.ChildNode +','+cast(c.TId as varchar(10) ) As Varchar(100)) As ChildNode
from [CTE] p, @Table c where c.[ParentId] = p.[TId]
)
--select the child node as per the id
--Assigin the all Ids into one variable
select @ChildNode=@ChildNode+','+Cast(TId as varchar(10))
from [CTE]
Cross Apply
dbo.Split(ChildNode,',')
where items=9
order by TId
select SUBSTRING(@ChildNode,2,LEN(@ChildNode))
--outpu
--2,7,8,11,12,13,14
----
--create the tabular function to split the multi valued into table
Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
--
Happy Coding
Saturday, April 16, 2011
How to insert multiple record using xml in sql server 2005+
How to insert the multiple record using the xml into sql server, please check the following example.
DECLARE @idoc int
DECLARE @doc varchar(max)
SET @doc ='
<ROOT>
<Trans TransId="1" Add="false" Edit="true" Delete="true" View="true" Block="false">
</Trans>
<Trans TransId="2" Add="1" Edit="1" Delete="1" View="1" Block="0">
</Trans>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
Into #TempTable
FROM OPENXML(@idoc, '/ROOT/Trans',1)
WITH
(
TransId varchar(10),
[Add] bit,
Edit bit,
[Delete] bit,
[View] bit,
Block bit
)
Select * From #TempTable
drop table #TempTable
--
happy coding
Tuesday, December 14, 2010
Performance Comparision Between For, While and Foreach Loop
Today I was posted one post regarding list to datatable conversion. But one of the my senior told me that try to avoid foreach loop.
So that I was googled/binged regarding the same.
Here I am showing you some figure for the same here.
Using the System.Diagnostics.Stopwatch class I ran some tests. 100,000 iterations in a for loop that did nothing inside took me 0.0003745 seconds. This was the code for the loop:
The while loop resulted 0.0003641 seconds, which is pretty much the same as the for loop. Here is the code I used:
foreach creates an instance of an enumerator (returned from GetEnumerator) and that enumerator also keeps state throughout the course of the foreach loop. It then repeatedly calls for the Next() object on the enumerator and runs your code for each object it returns.
So, it seems that 'while' is the fastest looping technique among the three available techniques in C#, for a given processing within the loop. Right?
It varies. "while" and "for" have pretty much the same results. While had a slight advantage, but not one that great.
Im not an expert, but I have the feeling that a while and for loop, once compiled to MSLI, probably are both the exact same thing.
And I wouldn't be surprised if foreach was faster when iterating through objects, since the optimizer can "expect" whats going to happen... So I'd go with foreach being faster if you can use it, and the 2 others being the same thing, if foreach isn't applicable.
My logic here is that if you're looping through a collection, and you use a for loop for example, you're going to have to use the index of the collection, which depending on implementation, could be a minor performance hit to "seek" the object, as opposed to going through a well written iterator.
Just an example.So really: I wouldn't care too much about performance of these loops. This isn't C/C++, and when you compile, its not native code (at first). So it is safe to assume that the solution that seems the most efficient "logically" will be so in practice.
For each is slower for a number of reasons. One is it is using the IEnumerable interface, which requires some casting (assuming you aren't using a generic collection). My test above seemed to go along with that as well. A simple for/while/do loop is pretty much as simple as it gets.
As for the MSIL code, let's take a look. This is the MSIL for the for loop:
And here it is for the while loop:
So yes, they are exactly identical.
Although very handy, C#'s
Performance
Iterating through a collection using
And that's all I can find. Why are there unpredictable side effects? I don't know. But seems best to heed Microsoft's warning.
Doing so will give you unnoticeable performance increases at the cost of maintainability and programmer time and the effort you're putting in to such small performance boosts is better spent on a proper design and implementation. Let's take a real world example of finding duplicates in a list to illustrate my point:
first let's assume that comparing two items is O(1). We can implement this any number of ways, two of which are:
A) use nested loops with i on the outer loop and j on the inner loop, when list[i] == list[j] push the pair onto a list of duplicates.
B) copy the list to tmpList. quicksort tmpList. iterate over the list with i, when list[i] == list[i+1] push the pair onto the list of duplicates
No amount of optimization can change the fact that A runs in O(n^2) while B runs in O(2*n+n*log(n)).
So that I was googled/binged regarding the same.
Here I am showing you some figure for the same here.
Using the System.Diagnostics.Stopwatch class I ran some tests. 100,000 iterations in a for loop that did nothing inside took me 0.0003745 seconds. This was the code for the loop:
for (int i = 0; i < 100000; i++) ;
The while loop resulted 0.0003641 seconds, which is pretty much the same as the for loop. Here is the code I used:
int i=0; while (i < 100000) i++;
The foreach loop has a slightly different purpose. It is meant for itterating through some collection that implements IEnumerable. It's performance is much slower, my test resulted in 0.0009076 seconds with this code:
int[] test = new int[100000]; foreach (int i in test) ;
foreach creates an instance of an enumerator (returned from GetEnumerator) and that enumerator also keeps state throughout the course of the foreach loop. It then repeatedly calls for the Next() object on the enumerator and runs your code for each object it returns.
So, it seems that 'while' is the fastest looping technique among the three available techniques in C#, for a given processing within the loop. Right?
It varies. "while" and "for" have pretty much the same results. While had a slight advantage, but not one that great.
Im not an expert, but I have the feeling that a while and for loop, once compiled to MSLI, probably are both the exact same thing.
And I wouldn't be surprised if foreach was faster when iterating through objects, since the optimizer can "expect" whats going to happen... So I'd go with foreach being faster if you can use it, and the 2 others being the same thing, if foreach isn't applicable.
My logic here is that if you're looping through a collection, and you use a for loop for example, you're going to have to use the index of the collection, which depending on implementation, could be a minor performance hit to "seek" the object, as opposed to going through a well written iterator.
Just an example.So really: I wouldn't care too much about performance of these loops. This isn't C/C++, and when you compile, its not native code (at first). So it is safe to assume that the solution that seems the most efficient "logically" will be so in practice.
For each is slower for a number of reasons. One is it is using the IEnumerable interface, which requires some casting (assuming you aren't using a generic collection). My test above seemed to go along with that as well. A simple for/while/do loop is pretty much as simple as it gets.
As for the MSIL code, let's take a look. This is the MSIL for the for loop:
IL_0000: nop IL_0001: ldc.i4.0 IL_0002: stloc.0 IL_0003: br.s IL_0009 IL_0005: ldloc.0 IL_0006: ldc.i4.1 IL_0007: add IL_0008: stloc.0 IL_0009: ldloc.0 IL_000a: ldc.i4 0x186a0 IL_000f: clt IL_0011: stloc.1 IL_0012: ldloc.1 IL_0013: brtrue.s IL_0005
And here it is for the while loop:
IL_0000: nop IL_0001: ldc.i4.0 IL_0002: stloc.0 IL_0003: br.s IL_0009 IL_0005: ldloc.0 IL_0006: ldc.i4.1 IL_0007: add IL_0008: stloc.0 IL_0009: ldloc.0 IL_000a: ldc.i4 0x186a0 IL_000f: clt IL_0011: stloc.1 IL_0012: ldloc.1 IL_0013: brtrue.s IL_0005
So yes, they are exactly identical.
Although very handy, C#'s
foreach statement is actually quite dangerous. In fact, I may swear off its use entirely. Why? Two reasons: (1) performance, and (2) predictability. Performance
Iterating through a collection using
foreach is slower than with for. I can't remember where I first learned that, perhaps in Patterns & Practices: Improving .Net Application Performance. Maybe it was from personal experience. How much slower? Well, I suppose that depends on your particular circumstances. Here are a few interesting references:- FOREACH Vs. FOR (C#): shows the extra IL code created for a
foreachloop - To foreach or not to foreach that is the question: claims it isn't so clear-cut, at least for regular arrays. Not absolutely conclusive.
Predictability
I was looking at the C# Reference entry for foreach today and noticed this for the first time (italics added by me):
The foreach statement is used to iterate through the collection to get the desired information, but should not be used to change the contents of the collection to avoid unpredictable side effects.What's that all about? Let's take this as an example:foreach(MyClass myObj in ListLooking deeper into the C# Language Specification... the iteration variable is supposed to be read-only, though apparently that doesn't stop you from updating a property of an object. Thus for instance it would be illegal to assign a new value to)
myObj, but not to assign a new value to myObj.MyProperty. And that's all I can find. Why are there unpredictable side effects? I don't know. But seems best to heed Microsoft's warning.
Conclusion
Some argue that you shouldn't code for performance from the beginning, and therefore go ahead and useforeach whenever you want so long as you don't update the values. In my experience that's hogwash — most of the code I work on goes into environments where performance is extremely important. Besides, writing a for statement requires very little extra coding compared to a foreach statement. Furthermore, if you have a lot going on inside your iteration block, it can be easy to forget and accidentally update the iteration variable inside a foreach loop. Thus do I conclude: just avoid foreach altogether.Honestly the academically correct answer is "It's Irrelevant". You cant optimize performance by somehow picking "the best loop". If you're doing performance optimizations you should start with a Big O analysis of your algorithms and Profiling. You'd be amazed at how much faster keeping around a dictionary for lookups or using a sorted list + binary search is than looping over a list every time you need to find an object.
Doing so will give you unnoticeable performance increases at the cost of maintainability and programmer time and the effort you're putting in to such small performance boosts is better spent on a proper design and implementation. Let's take a real world example of finding duplicates in a list to illustrate my point:
first let's assume that comparing two items is O(1). We can implement this any number of ways, two of which are:
A) use nested loops with i on the outer loop and j on the inner loop, when list[i] == list[j] push the pair onto a list of duplicates.
B) copy the list to tmpList. quicksort tmpList. iterate over the list with i, when list[i] == list[i+1] push the pair onto the list of duplicates
No amount of optimization can change the fact that A runs in O(n^2) while B runs in O(2*n+n*log(n)).
Convert List to DataTable
One day I confused to convert the List collection to datatable object. I was wondering how to iterate the columns wise and rows wise in list to make some calculation.
I got the solution which was posted below:
--
Happy coding.
I got the solution which was posted below:
public DataTable ListToDataTable(IEnumerable list) { var dt = new DataTable(); foreach (var info in typeof(T).GetProperties()) { dt.Columns.Add(new DataColumn(info.Name, info.PropertyType)); } foreach (var t in list) { var row = dt.NewRow(); foreach (var info in typeof(T).GetProperties()) { row[info.Name] = info.GetValue(t, null); } dt.Rows.Add(row); } return dt; }
Wednesday, May 19, 2010
Age calculation with SQL Server
There seem to be many different methods being suggested to calculate an age in SQLServer. Some are quite complex but most are simply wrong. This is by far the simplest and accurate method that I know.
Declare @Date1 datetime Declare @Date2 datetime Select @Date1 = '15Feb1971'Select @Date2 = '08Dec2009'select CASE WHEN dateadd(year, datediff (year, @Date1, @Date2), @Date1) > @Date2 THEN datediff (year, @Date1, @Date2) - 1 ELSE datediff (year, @Date1, @Date2)END as Age
--
happy coding
Monday, April 19, 2010
[WPF] How to assign a dynamic resource from code-behind ?
When working on WPF projects, it’s mandatory to assign resources to user interface controls. When you work in XAML, it’s pretty simple: you just need to use the MarkupExtension named StaticResource (or DynamicResource if the resource is going to be modified):
But, how to do the same using code-behind ? The key is to use the method SetResourceReference (http://msdn.microsoft.com/en-us/library/system.windows.frameworkelement.setresourcereference.aspx):
Key="brush" Color="Red" />
As you can see, it’s really simple to use: you define the resource, you define the control and, in code behind, you call the method SetResourceReference and use the following parameters:
Happy coding !
<Button Content="Find Position" Click="Button_Click" Background="{DynamicResource brush}" />
<Window.Resources>
Window.Resources>
this.btn.SetResourceReference(BackgroundProperty, "brush");
- the DependencyProperty on which the resource will be applied
- the name of the resource
Happy coding !
Wednesday, February 24, 2010
Reset Identity Column Value in SQL Server
If you are using an identity column on your SQL Server tables, you can set the next insert value to whatever value you want. An example is if you wanted to start numbering your ID column at 1000 instead of 1.
It would be wise to first check what the current identify value is. We can use this command to do so:
DBCC CHECKIDENT (orders, RESEED, 999)
Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000.
Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it. (which it really shouldn’t)
It would be wise to first check what the current identify value is. We can use this command to do so:
DBCC CHECKIDENT ('tablename', NORESEED) For instance, if I wanted to check the next ID value of my orders table, I could use this command:
DBCC CHECKIDENT (orders, NORESEED)
To set the value of the next ID to be 1000, I can use this command:
DBCC CHECKIDENT (orders, RESEED, 999)
Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000.
Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it. (which it really shouldn’t)
DBCC CHECKIDENT ('databasename.dbo.orders',RESEED, 999)
Subscribe to:
Posts (Atom)