tag:blogger.com,1999:blog-65924204588428657362024-03-19T17:02:26.100+05:30ASPMANTRA | Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server AND WCF snippets and tutorialAspmantra is technology based blog, provides snippets and tutorials about asp.net,JQuery,JavaScript,Gridview,SQL Server,Ajax,Crystal reports,HTML,RDLC reports,Web Services,ADO.NET,AngularJs,MVC,WCF,Entity Framework, Google, Facebook,CSS and many more.Vijay Saklanihttp://www.blogger.com/profile/08605063040623165445noreply@blogger.comBlogger57512tag:blogger.com,1999:blog-6592420458842865736.post-69830229883083808712021-05-16T22:42:00.006+05:302021-05-16T22:50:54.599+05:30SQL Server : Round function<p></p><p class="MsoNormal"><span style="font-family: Cambria, serif; font-size: 12pt;">In this article I am going to explain the ROUND() function of
SQL Server.<br /><br /></span></p>
<p class="MsoNormal"><span style="font-family: Cambria, serif; font-size: 12pt;">ROUND function returns the rounded value of numeric field to
the specified length or precision. ROUND function accepts 3 parameters.</span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">SYNTAX :<o:p></o:p></span></b></p>
<pre><p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">ROUND (numeric_expression , length [ ,function ] )<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></p></pre>
<p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;"><o:p> </o:p></span></p>
<p class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo1; text-indent: -18pt;"><!--[if !supportLists]--><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-bidi-font-family: Cambria; mso-fareast-font-family: Cambria;"><span style="mso-list: Ignore;">1.<span style="font: 7pt "Times New Roman";">
</span></span></span><!--[endif]--><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">numeric_expression
: </span></b><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">It
is an expression of the exact numeric or approximate numeric data type
category, except for the bit data type.<br style="mso-special-character: line-break;" />
<!--[if !supportLineBreakNewLine]--><br style="mso-special-character: line-break;" />
<!--[endif]--><o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -18pt;"><!--[if !supportLists]--><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-bidi-font-family: Cambria; mso-fareast-font-family: Cambria;"><span style="mso-list: Ignore;">2.<span style="font: 7pt "Times New Roman";">
</span></span></span><!--[endif]--><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">Length:</span></b><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;"> It is
the precision to which numeric_expression is to be rounded. length must be an
expression of type tinyint, smallint, or int. When length is a positive number,
numeric_expression is rounded to the number of decimal positions specified by
length. When length is a negative number, numeric_expression is rounded on the
left side of the decimal point, as specified by length.<br style="mso-special-character: line-break;" />
<!--[if !supportLineBreakNewLine]--><br style="mso-special-character: line-break;" />
<!--[endif]--><o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -18pt;"><!--[if !supportLists]--><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-bidi-font-family: Cambria; mso-fareast-font-family: Cambria;"><span style="mso-list: Ignore;">3.<span style="font: 7pt "Times New Roman";">
</span></span></span><!--[endif]--><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">Function:
</span></b><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">It
is the type of operation to perform. function must be tinyint, smallint, or
int. When function is omitted or has a value of 0 (default), numeric_expression
is rounded. When a value other than 0 is specified, numeric_expression is
truncated. This is an optional parameter.</span><b><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;"><o:p> </o:p></span></b></p><p class="MsoListParagraphCxSpMiddle"><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;"><o:p><span></span></o:p></span></b></p><a name='more'></a><p></p>
<p class="MsoListParagraphCxSpLast"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;"><o:p> </o:p></span><b><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">Example:</span></b></p>
<pre>
<p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">declare @value decimal(10,2)=163.346</span></p><p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">SELECT ROUND(@value,0)</span></p><p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">--Result : 163.00<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">SELECT ROUND(@value,1)</span></p><p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">--Result : 163.40<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">SELECT ROUND(@value, 2);</span></p><p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">--Result : 163.35<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">SELECT ROUND(@value, -1);</span></p><p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">--Result : 160.00<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">SELECT ROUND(@value, -2);</span></p><p class="MsoNormal"><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;">--Result : 200.00<o:p></o:p></span></p><br /><p></p>
</pre>Vijay Saklanihttp://www.blogger.com/profile/08605063040623165445noreply@blogger.com0tag:blogger.com,1999:blog-6592420458842865736.post-48642383184127181722021-04-03T23:28:00.004+05:302021-04-03T23:30:09.959+05:30SQL Server error handling<p><span style="font-family: Cambria, serif; font-size: 12pt;">Here in this article I am
going to explain how we can handle the error in SQL Server.<br /><br /></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;">SQL server has the built
in support to handle the Transact-SQL statements</span> errors. <b style="mso-bidi-font-weight: normal;">TRY...CATCH</b> constructs allow us to
handle the error(s) in SQL server. To use the TRY...CATCH: write the T-SQL
statements in TRY section. If any error occurs in TRY section written
code, CATCH section T-SQL will be executed and it returns the error
description.<br /><br /><span></span></p><a name='more'></a><o:p></o:p><p></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><span lang="EN-GB" style="color: red; font-family: "Cambria",serif; font-size: 14pt; line-height: 107%; mso-ansi-language: EN-GB;">Note : </span></b><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;">We can’t use TRY...CATCH in User defined function. CATCH
section will not have executed if there is any syntax error in TRY section.<br /><br /></span><span style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%;"><o:p></o:p></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><span lang="EN-GB" style="color: #00b0f0; font-family: "Cambria",serif; font-size: 14pt; line-height: 107%; mso-ansi-language: EN-GB;">Syntax:<o:p></o:p></span></b></p>
<pre>BEGIN TRY
-- Transact-SQL statements that may have error
END TRY
BEGIN CATCH
-- Write the Transact-SQL statements if an error occurs in code written in TRY section
END CATCH
</pre>
<p class="MsoNormal"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;">We can get the detailed
information of errors. To get error info, use the below given functions inside
CATCH block:<o:p></o:p></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;">ERROR_NUMBER()</span></b><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;"> returns the number of the error.<o:p></o:p></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;">ERROR_STATE()</span></b><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;"> returns the error state number.<o:p></o:p></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;">ERROR_SEVERITY()</span></b><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;"> returns the severity.<o:p></o:p></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;">ERROR_PROCEDURE() </span></b><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;">returns the name of the stored procedure or trigger
where the error occurred.<o:p></o:p></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;">ERROR_LINE()</span></b><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;"> returns the line number inside the routine that
caused the error.<o:p></o:p></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;">ERROR_MESSAGE()</span></b><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;"> returns the complete text of the error message. The
text includes the values supplied for any substitutable parameters, such as
lengths, object names, or times.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;"><o:p> </o:p></span></p>
<p class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><span lang="EN-GB" style="color: #00b0f0; font-family: "Cambria",serif; font-size: 14pt; line-height: 107%; mso-ansi-language: EN-GB;">Example<o:p></o:p></span></b></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;">Now let’s move to practical.
Here in this example I want to know the result of 6/0. On executing this error
occurs because as we know divide by 0 is an error.<o:p></o:p></span></p>
<pre>BEGIN TRY
declare @ans int = 6/0
select @ans as answer
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
</pre>
<p class="MsoNormal"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQchr0CyCwsMkbgy9DngiWZeMOHYdRVGM1LulwzO9x3-PM40y3l5_u-i57xCyhNwibFrmxWV-h_jkU_Sg_SleUiUxTA6lxU56qq3b6ybFutHyLebp76qaF2nb08CQjh0NoZRJAmzA22ecj/s600/SQL+Server+error+handling.PNG" style="margin-left: 1em; margin-right: 1em;"><img alt="SQL Server error handling" border="0" data-original-height="385" data-original-width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQchr0CyCwsMkbgy9DngiWZeMOHYdRVGM1LulwzO9x3-PM40y3l5_u-i57xCyhNwibFrmxWV-h_jkU_Sg_SleUiUxTA6lxU56qq3b6ybFutHyLebp76qaF2nb08CQjh0NoZRJAmzA22ecj/s16000/SQL+Server+error+handling.PNG" title="SQL Server error handling" /></a></div><br /><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;"><br /></span><p></p><p class="MsoNormal"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;">On dividing the 6/2
getting result 3.<o:p></o:p></span></p>
<pre>BEGIN TRY
declare @ans int = 6/2
select @ans as answer
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
</pre>
<p class="MsoNormal"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;"><o:p> </o:p></span><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;"><o:p> </o:p></span><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5Y_RCEt_U3teGuR3ui8Xyk1Mjnibzj5tUovqrXDGs47rwBYvuqOGXr_oRYryQ37jvDClziG_4_6PSIoXI_9oSsRqZZU2i9BQ8Aue5KQjVZ48q2EQCMZ9UBI9HBm9d-R5PJ5Mzv3m9YTJ-/s495/Error+handling.PNG" style="font-family: Cambria, serif; font-size: 12pt; margin-left: 1em; margin-right: 1em; text-align: center;"><img alt="SQL Server error handling" border="0" data-original-height="377" data-original-width="495" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5Y_RCEt_U3teGuR3ui8Xyk1Mjnibzj5tUovqrXDGs47rwBYvuqOGXr_oRYryQ37jvDClziG_4_6PSIoXI_9oSsRqZZU2i9BQ8Aue5KQjVZ48q2EQCMZ9UBI9HBm9d-R5PJ5Mzv3m9YTJ-/s16000/Error+handling.PNG" title="SQL Server error handling" /></a></p><p class="MsoNormal"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;"><o:p><br /></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-family: "Cambria",serif; font-size: 12pt; line-height: 107%; mso-ansi-language: EN-GB;"><o:p> </o:p></span></p>Vijay Saklanihttp://www.blogger.com/profile/08605063040623165445noreply@blogger.com0