Round values to N number of Decimal points in sql server

Round (expression , length)

SELECT ROUND(3.1415 , 2)        -- 3.14

SELECT ROUND(33.234234234 , 1)  -- 33.2

SELECT ROUND(55.6345345 , 0)    -- 56

Find Length of string with LEN function in sql server

It returns the number of characters of string expression but with excluding trailing blanks.

SELECT LEN('Sql Server Database')

-- Result : 19

Get List of all tables of a database in sql server

SELECT * from sys.tables

Get List of all stored procedures of a database in sql server

SELECT * from sys.procedures

Find Age from Date of Birth in Sql Server

declare @dob datetime = '1952-08-14 21:11:19.300'

select CAST( DATEDIFF(Y , @dob , getdate() )/365.25 as int)

-- 365 are the Number of Average days in 4 years

Select Top N rows from a table in Sql Server

Suppose if N=10 then ,

Select Top 10 * from Person

IF you want to Select only particular columns then,

Select Top 10 Name , City , Age from Person

Get current system date in Sql Server

SELECT GETDATE()

-- Result : 2014-03-23 19:57:21.630

Find Version of Sql Server through query

select @@Version

Result from My Computer:

--Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

--Feb 10 2012 19:39:15

--Copyright (c) Microsoft Corporation

--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

Reverse values with Reverse function in Sql Server

select REVERSE(45)  -- Result 54

select REVERSE('abc') -- Result cba

Select Last Date of Current year in Sql Server

SELECT DATEADD(yy, DATEDIFF(yy, 0, getdate()) + 1, -1) AS LastDateOfYear

Select First Date of Current year in Sql Server

SELECT DATEADD( yy, DATEDIFF( yy, 0, getdate() ), 0) AS FirstDateOfYear

#region in Visual Studio

It allows block of code to expand or collapse when using the outlining feature of the Visual Studio Code Editor. You can write namespaces, classes, methods, interfaces, delegates, events inside it.

#region MyRegion

//  Namespaces

//  Classes

//  Methods

//  Interfaces

//  Delegates

//  Events

#endregion

Page Pre_Render Event in Asp.net C#

If you want to perform some action after the page is completely loaded and Ready to Render then the desired code should be written in this event.

protected void Page_PreRender(object sender, EventArgs e)

{

    // Your Code Goes Here

}

Retrieve DataRow by Index from Dataset in C#

int index = 3;

DataSet ds = GetData();

DataRow dr = ds.Tables[0].Rows[index];

Get type information of any object using GetType( )

object obj = new object();

obj.GetType();

string s;

s.GetType();

List<string> StringList = new List<string>();

StringList.GetType();

Concatenate Dictionary objects in c#

Dictionary<string, object> d1 = new Dictionary<string, object>();

d1.Add("Name","Talha");

Dictionary<string, object> d2 = new Dictionary<string, object>();

d2.Add("City","Lahore");

// --- Now Concatenate Both Dictionaries using concat Extension method

d1 = d1.Concat(d2).ToDictionary(x=> x.Key , x=> x.Value);

Note:  Here "Concat" is an Extension method  so You have to include it’s Namespace reference on top.

i.e.

using System.Linq;

Dynamic Query in Sql Server

It is used commonly when you are Selecting the Result set on the base of some parameter or condition ,  or to optimize the Sql to generate the accurate query at Runtime according to the Requirements. .

suppose we have a Stored Procedure which takes a single Parameters , which is a table name and generates the query according to it.

Here is a simple Example below:

Create procedure DynamicQuery

(

@tableName varchar(33)

)

as

BEGIN

Declare @SelectQuery Varchar(25)

If (@tableName = 'Teacher')

set @SelectQuery = 'select * from Teacher'

exec (@SelectQuery)

If (@tableName = 'Student')

set @SelectQuery = 'select * from Student'

exec (@SelectQuery)

END

Boxing and Unboxing in c#

Boxing:

int i = 123;

object o = i;  // boxing interger i into object o.

Unboxing:

object o = 123;

i = (int)o;  // unboxing int value from object o to int i.

Select Date Part From DateTime in Sql Server

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

SQL LIKE Operator

LIKE operator is used to filter Records for a specified pattern in a column.

Syntax:

SELECT column_name(s)
FROM table_name                  

WHERE column_name LIKE pattern;


Use:

SELECT * FROM Persons

WHERE Name LIKE 's%';

Above query will display All those Person whose name Starts with 's'.

Space Function in Sql Server

Returns a string of repeated spaces

Syntax:

Select FirstName + Space(1) + LastName from Person

IF FirstName is Talha and LastName is Tanweer than Result would be:

Talha Tanweer 

Convert List to Comma Seperated String in C#

List<string> List = new List<string>();

string.Join(",", List);

Comma Seperated String Function in Sql Server

Create function [dbo].[Split]

(

       @RowData nvarchar(2000),

       @SplitOn nvarchar(5)

) 

RETURNS @RtnValue table

(

       Id int identity(1,1),

       Data nvarchar(100)

)

AS 

BEGIN

       Declare @Cnt int

       Set @Cnt = 1

       While (Charindex(@SplitOn,@RowData)>0)

       Begin

              Insert Into @RtnValue (data)

              Select Data = Ltrim(Rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

              Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

              Set @Cnt = @Cnt + 1

       End

      

       Insert Into @RtnValue (data)

       Select Data = Ltrim(Rtrim(@RowData))

       Return

END