simple cursor

Create table #tmp ( SalesOrderID int, OrderQty int ) GO --simple cursor in sql server Declare @orderid int, @orderqty int -- declare a cursor DECLARE insert_cursor CURSOR FOR SELECT SalesOrderId,OrderQty from Sales.SalesOrderDetail WHERE SalesOrderID=43659 -- open cursor and fetch first row into variables OPEN insert_cursor FETCH NEXT FROM insert_cursor into @orderid,@orderqty -- check for a new row WHILE @@FETCH_STATUS=0 BEGIN -- do complex operation here Insert into #tmp SELECT @orderid,@orderqty -- get next available row into variables FETCH NEXT FROM insert_cursor into @orderid,@orderqty END close insert_cursor Deallocate insert_cursor GO

temp tables in sql

http://www.c-sharpcorner.com/blogs/local-and-global-temporary-tables-using-stored-procedure-in-sql-server

Get list of all indexes in current database ----- find Index for all tables in DB

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
ORDER BY o.name, i.type


Index naming convention:
PK_ for primary keys
UK_ for unique keys
IX_ for non clustered non unique indexes
UX_ for unique indexes
All of my index name take the form of
<index or key type>_<table name>_<column 1>_<column 2>_<column n>


Check index on specific table
select * from sys.indexes
where object_id = (select object_id from sys.objects where name = 'TableName')

(split) string with comma in SQL Server stored procedure (SQL COMMA SPLIT)

DECLARE @str VARCHAR(500) = '1,2,3'
CREATE TABLE #Temp (tDay VARCHAR(100),Tid BigInt)
WHILE LEN(@str) > 0
BEGIN
    DECLARE @TDay VARCHAR(100)
DECLARE @Tid BIGINT
    IF CHARINDEX(',',@str) > 0
BEGIN
        SET  @TDay = SUBSTRING(@str,0,CHARINDEX(',',@str))
SET @Tid=CAST(@TDay AS bigint)
END
    ELSE
        BEGIN
        SET  @TDay = @str
SET @Tid=0
        SET @str = ''
        END
  INSERT INTO  #Temp VALUES (@TDay,@Tid)
 SET @str = REPLACE(@str,@TDay + ',' , '')
 END




BELOW IS WITH LIVE EXAMPLE

USE [STRATEOwnership]
GO
/****** Object:  StoredProcedure [dbo].[sp_getBeneficialHolderDetails]    Script Date: 2017/10/10 11:30:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_getBeneficialHolderDetails]
@IDs varchar(500)
AS
BEGIN
SET NOCOUNT ON;

/* Get all IDS after splitting*/
DECLARE @str VARCHAR(500) = @IDs
CREATE TABLE #Temp (tDay VARCHAR(100),Tid BigInt)
WHILE LEN(@str) > 0
BEGIN
    DECLARE @TDay VARCHAR(100)
DECLARE @FundHolderDetailID BIGINT
    IF CHARINDEX(',',@str) > 0
BEGIN
        SET  @TDay = SUBSTRING(@str,0,CHARINDEX(',',@str))
SET @FundHolderDetailID=CAST(@TDay AS bigint)
END
    ELSE
        BEGIN
        SET  @TDay = @str
SET @FundHolderDetailID=0
        SET @str = ''
        END
  INSERT INTO  #Temp VALUES (@TDay,@FundHolderDetailID)
 SET @str = REPLACE(@str,@TDay + ',' , '')
 END


SELECT [t0].[Holdings], [t0].[Unique Identifier] AS [UniqueIdentifier], [t1].[Client Name] AS [ClientName], [t1].[ClientName1], [t1].[Address1], [t1].[Address2]
FROM [BeneficialShareHolder] AS [t0], [CompanyDetails] AS [t1]
WHERE ([t0].[FundHolderDetailID] in (SELECT Tid FROM #Temp)) AND ([t0].[CompanyDetailsID] = ([t1].[CompanyDetailsID]))


drop table #Temp

END

get all indexes in DB

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
ORDER BY o.name, i.type

DateTimeCOnversions

  class Program
    {
        static void Main(string[] args)
        {
            //string date = "01 April 2017";
            string[] dates = { "20170404", "20170703", "20170601", "20170405" };
            string dt;
            int lastday;

            string Month = "April", Year = "2017";
            string date = "01" + " " + Month + " " + Year;
            foreach (var item in dates)
            {
                //string date conversion
                dt = DateTime.ParseExact(item, "yyyyMMdd",
                CultureInfo.InvariantCulture).ToString("yyyy-MM-dd");
                Console.WriteLine("dts:" + dt);
            }

            //long date conversion
            date = (Convert.ToDateTime(date).ToString("yyyy-MM-dd"));
            lastday = DateTime.DaysInMonth(Convert.ToDateTime(date).Year, Convert.ToDateTime(date).Month);

            string lastdate = lastday.ToString() + " " + Month + " " + Year;
            lastdate = (Convert.ToDateTime(lastdate).ToString("yyyy-MM-dd"));

            Console.WriteLine("lastDay:" + lastday);
            Console.WriteLine("first date:" + date);
            Console.WriteLine("lastdate:" + (Convert.ToDateTime(lastdate).ToString("yyyy-MM-dd")));
            DateTime ldate = Convert.ToDateTime((Convert.ToDateTime(lastdate).ToString("yyyy-MM-dd")));
            DateTime fdate = Convert.ToDateTime(date);

            Console.WriteLine("fdate:" + fdate + ";ldate" + ldate);
            Console.ReadLine();
        }
    }