SQL Easy Learning

-- Delete in batches declare @ rc1 int ; --record count declare @ rc2 int ; --record count declare @ rc3 int ; --record count set @ rc1 = 1 ; set @ rc2 = 1 ; set @ rc3 = 1 ; while (@ rc1 > 0 OR @ rc2 > 0 OR @ rc3 > 0 ) begin --deleting by sets of 10,000 --1. delete top ( 10000 ) from Employee where Employeerecordid in ( '1' , '2' , '3' , '4' ) set @ rc1 = @@ ROWCOUNT ; --2. delete top ( 10000 ) from employeedegree where Employeerecordid in ( '1' , '2' , '3' , '4' ) set @ rc2 = @@ ROWCOUNT ; --3. delete top ( 10000 ) from employeeexperiance where Employeerecordid in ( '1' , '2' , '3' , '4' ) set @ rc3 = @@ ROWCOUNT ; end

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_SearchTables]  @Tablenames VARCHAR(500) ,@SearchStr NVARCHAR(60) ,@GenerateSQLOnly Bit = 0 AS /*     Parameters and usage     @Tablenames        -- Provide a single table name or multiple table name with comma seperated.                          If left blank , it will check for all the tables in the database     @SearchStr        -- Provide the search string. Use the '%' to coin the search.                          EX : X%--- will give data staring with X                              %X--- will give data ending with X                             ...

Create FUNCTION [dbo].[IsWeekEnd]                (@dtDate DATETIME) RETURNS BIT AS   BEGIN     DECLARE  @IsWeekEnd BIT         SELECT @IsWeekEnd = CASE ((Datepart(dw,@dtDate) + @@DATEFIRST - 7)%7)                           WHEN 1 THEN 1                           WHEN 0 THEN 1                           ELSE 0                         END         RETURN @IsWeekEnd   END

CREATE function [dbo].[FiscalWeek]  (@startMonth varchar(2), @myDate datetime)  returns int  as  begin  declare @firstWeek datetime  declare @weekNum int  declare @year int  set @year = datepart(year, @myDate)+1  --Get 4th day of month of next year, this will always be in week 1  set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)  --Retreat to beginning of week  set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)  while @myDate < @firstWeek --Repeat the above steps but for previous year   begin    set @year = @year - 1    set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)    set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)   end  set @weekNum = (@year*100)+((datediff(day, @firstweek, @myDate)/7)+1)  return @weekNum  end  

Create function [dbo].[GetTenure] (@dateOfJoin datetime) Returns Varchar(200) AS Begin DECLARE @today datetime, @thisYearJoinDay datetime DECLARE @years int, @months int, @days int set @today = GETDATE() set @thisYearJoinDay = DATEADD(year, DATEDIFF(year, @dateOfJoin, @today), @dateOfJoin) set @years = DATEDIFF(year, @dateOfJoin, @today) - (CASE WHEN @thisYearJoinDay > @today THEN 1 ELSE 0 END) set @months = MONTH(@today - @thisYearJoinDay) - 1 set @days = DAY(@today - @thisYearJoinDay) - 1 return cast(@years as varchar) +'-'+ cast(@months as varchar)+'-'+cast(@days as varchar) --SELECT @years as years, @months as months, @days as days End

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[EmailReport] @ReportName AS VARCHAR(10) , @Country AS VARCHAR(4000) , @Date  AS DATE , @FromDate AS DATE , @ToDate AS DATE , @Schedule  AS VARCHAR(4000) , @Mailrecipients as varchar(4000) AS BEGIN DECLARE @Datestring varchar(100) SET @Datestring = CONVERT(VARCHAR(10), @Date, 101) PRINT @Datestring DECLARE @FileName AS VARCHAR(4000) DECLARE @string AS VARCHAR(4000) DECLARE @Columns AS VARCHAR(4000) DECLARE @MailSubject AS VARCHAR(4000) DECLARE @MailBody AS VARCHAR(4000) SET @MailBody = 'Attached is the ' + @Schedule + ' Report. Please do not Reply back as this is a System Generated Email.' IF (@ReportName = 'Employee Details') BEGIN -- Generate the Report SET @FileName = 'E:\Employee\SQLJob\EmployeeRecord'+Replace (@Country,' ','')+ CONVERT(VARCHAR(10), @Date, 112)+ '.CSV' -- Command Shell for Rep...