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