SQL SERVER: Generate Comma Separated List with SELECT statement

Below example shows, how to get comma separated value from SQL server.

-- Create table
CREATE TABLE #test(field1 VARCHAR(5), field2 VARCHAR(5))


--Lets insert some data in this table:
INSERT INTO #test
SELECT '001','AAA'  UNION ALL
SELECT '001','BBB'   UNION ALL
SELECT '002','CCC'  UNION ALL
SELECT '003','DDD'  UNION ALL
SELECT '004','EEE'    UNION ALL
SELECT '004','FFF'    UNION ALL
SELECT '004','GGG'
 
SELECT field1,
SUBSTRING(
(
  SELECT ( ', ' + field2)
  FROM #test t2
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
), 3, 1000)
FROM #test t1
GROUP BY field1

Output will be:
field1   field2
001     AAA,BBB
002     CCC
003     DDD
004     EEE,FFF,GGG

How to split comma separated string into SQL server.

Below function can be used to split comma seperated string, output will be in fom of table.

-- SELECT * FROM dbo.fn_Split ('1,2,3',',')

CREATE FUNCTION [dbo].[fn_Split]
(
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
RETURNS @ValueTable table (ID [int] IDENTITY (1, 1) NOT NULL,[Value] nvarchar(4000))


BEGIN


DECLARE @NextString nvarchar(4000)
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @CommaCheck nvarchar(1)


--Initialize
SET @NextString = ''
SET @CommaCheck = right(@String,1)


--Check for trailing Comma, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
SET @String = @String + @Delimiter


--Get position of first Comma
SET @Pos = charindex(@Delimiter,@String)
SET @NextPos = 1


--Loop while there is still a comma in the String of levels
WHILE (@pos <> 0)
BEGIN
  SET  @NextString = substring(@String,1,@Pos - 1)
  INSERT INTO @ValueTable ( [Value]) Values (@NextString)
  SET  @String = substring(@String,@pos +1,len(@String))
  SET  @NextPos = @Pos
  SET  @pos = charindex(@Delimiter,@String)
END


RETURN

END

Get number of days in month from given date in SQL server

Below is example of SQL query which will give number of days in month for given date:

DECLARE @dtDate AS DATETIME = GETDATE()   --This will select current date.
SELECT DAY(DATEADD(MONTH, 1, @dtDate) - DAY(DATEADD(MONTH, 1, @dtDate))) 

Search / find keyword in your entire database(Procedure, Function) in SQL Server

You can use below procedure to search any keyword in your entire database. In output you will get list of procedures and functions in which given keyword is found.

CREATE PROC SP_SEARCH_CODE
(
  @SearchStr VARCHAR(100),
  @RowsReturned INT = NULL OUT
)


AS
BEGIN


SET NOCOUNT ON


SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',


CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1 THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1 THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1 THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 THEN 'Inline function'
WHEN OBJECTPROPERTY(c.id, 'IsView') = 1 THEN 'VIEW'
END AS 'Object type',


'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'
FROM syscomments c
INNER JOIN sysobjects o ON c.id = o.id
WHERE
c.text LIKE '%' + @SearchStr + '%' AND encrypted = 0 AND
(
OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsView') = 1
)
ORDER BY 'Object type', 'Object name'


SET @RowsReturned = @@ROWCOUNT


END

Disable Copy Paste Cut options in textbox using javascript in ASP.NET

Below is example to disable COPY, PASTE and CUT option in textbox:

<asp:TextBox ID="TextBox1"runat="server"oncopy="return false" oncut="return false" onpaste="return false" ></asp:TextBox> 

How to convert lowercase to uppercase in SQL Server

Below query is example of converting lowercase into uppercase using SQL query:

SELECT UPPER(ColumnName) FROM TableName

How to request READ RECEIPT in email in ASP.Net

Many email clients such as outlook has a feature that allows you to request a receipt when your email is read by the recipient(s).
It can be achieved in asp.net generated emails using additional header called Disposition-Notification-To
Here is simple code:

MailMessage email = new MailMessage(fromAddress, toAddress);
email.Headers.Add("Disposition-Notification-To", receiptToEmailAddress); //Use email address on which you want receive the receipt

Create PROCEDURE in SQL Server

-- Below is code to create new procedure in SQl server.

CREATE PROCEDURE ProcedureName
    -- Add the parameters for the stored procedure her
    @Param1 Datatype_For_Param1 = ,
    @Param2 Datatype_For_Param2 =
AS
BEGIN
--Here you can write your SQL statement.
END

Get MONTH name from Month number in ASP.NET

You can use below function to get MONTH name from MONTH number in ASP.NET:

DIM intMonth AS INT = 1;   'i.e. for January
MonthName(intMonth);  'Output will be January

Declare CURSOR in SQL SERVER / Syntax for implementing CURSOR

Below is example showing simple use of CURSOR:

-- This cursor will print all Employee name from Employee table

DECLARE cur CURSOR  --Declare Cursor
FOR SELECT EmpName FROM tbl_Employee

OPEN cur
FETCH NEXT FROM cur INTO @EmpName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee Name:' + @EmpName;

    FETCH NEXT FROM cur INTO @EmpName
END

CLOSE cur;  -- Close Cursor
DEALLOCATE cur;  -- Deallocate

DELETE identical or same or duplicate entries from table in SQL SERVER

You can use below query to delete identical or same or duplicate entries from table:

1) Delete from table:

DELETE T FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS ID
FROM tbl_Test
WHERE Id='1' ) T
WHERE ID > 1

2) Delete from table having join with other tables

DELETE tbl_Test FROM (
SELECT ROW_NUMBER() OVER (ORDER BY T.Id) AS ID ,T.Id
FROM tbl_Test T
INNER JOIN tbl_Join J on J.Id = T.Id
WHERE T.Id='1' ) R
WHERE R.ID > 1

Difference between jQuery object and DOM element(Javascript Object)

Below given is relation between JQuery object and DOM element (i.e. Javascript object), Using this you can convert JQuery object into javascript object and apply formulas of javascript on it.

Consider Below Div
<div id="foo"></div>

Now below 3 alert will give same Javascript object (On which javascipt function can be performed)
alert($("#divTest")[0]);  //Jquery
alert($("#divTest").get(0));  //Jquery
alert(document.getElementById("divTest"));  //Javascript