Posts

Showing posts from February, 2017

While Looping - SQL SERVER

DECLARE @StartDate AS DATE='2014-12-17'; DECLARE @EndDate AS DATE='2014-12-25'; CREATE TABLE #DateList(iDate DATE,iDayName VARCHAR(10)) WHILE (@StartDate<=@EndDate) BEGIN                 INSERT #DateList(iDate,iDayName) VALUES(@StartDate,DATENAME(DW,@StartDate))                 SET @StartDate=CAST(DATEADD(DAY,1,@StartDate) AS DATE) END SELECT iDate AS [Date], iDayName AS [DayName] FROM #DateList

Alternative to Like Condition

DECLARE @Acc TABLE ( ID  int, AccountName Varchar(200) ) Insert into @Acc (ID,AccountName)  Values (1,'Switin Kotian'),(2,'Jitesh Shiyal'),(3,'Ramesh Bangera'),(4,'Shrikant Chavan'),(5,'Dipti Patil'),(6,'Bhavin Asher'),(7,'Smruti Kotian') Select * From @Acc Select * From @Acc Where AccountName Like 'S%' Select * From @Acc Where CharIndex('S',AccountName) = 1 Select * From @Acc Where Left(AccountName,1) = 'S' Select * From @Acc Where SUBSTRING(AccountName,1,1) = 'S'

User Defined Table Type

Drop Proc spGetColumnDetailsForTable  GO Drop Type CustParam --User defined Type {Table} Create Type CustParam as Table ( Name varchar(100), ID int ) Go -- SP to have UDT as Parameter . Readonly Keyword is Compulsary when UDT is used Create Proc spGetColumnDetailsForTable  @Para CustParam  READONLY as -- Below Syntax when you need to read Values from UDT --This return only First Value in Data Set --Declare @Name as Varchar(100) --Select @Name=Name  From @Para --Select @Name --This return only First Value in Data Set --Select * From sys.objects  objs  Where  objs.name in ( @Name) -- UDT is Used as Table to Hit join Select * From sys.objects  objs   inner join @Para Pr on Pr.id  = objs.object_id GO --- Sample for using UDT  Declare @T as CustParam Insert into  @T (Name,ID) Select name,object_id From Sys.Objects Where name Like 'T%' ---Select * from @T Exec spGetColumnDetailsForTab...