sql query techniques( inner join in update,Trigger,Capitalise the word first letter,get all procedures created and modified dates)

(1)Inner join while update table records
================================================================

UPDATE im
SET  im.keyword = gm.keyword
FROM  lkp_specialitiesinfo im
INNER JOIN doctors gm ON im.SpecialityId=gm.SpecialityId
WHERE gm.keyword is not null and gm.SpecialityId not  like '%,%'

(2)Curssor
================================================================

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[RProc_GetDoctorSlootsTimesStartEndTimesByDocId] --628485
@UserId int
AS
BEGIN
DECLARE @DOCTORID INT

DECLARE DOCTORCURSOR CURSOR FOR SELECT UserId FROM Doctors where HasConsultaionDates=1 AND HasActiveSlots=1
AND UserId in(@UserId)
OPEN DOCTORCURSOR
FETCH NEXT FROM DOCTORCURSOR INTO @DOCTORID
WHILE (@@FETCH_STATUS=0)                                      
BEGIN

FETCH NEXT FROM DOCTORCURSOR INTO @DOCTORID
END
CLOSE DOCTORCURSOR                                      
DEALLOCATE DOCTORCURSOR
END

(3)Capitalist word first letter
=======================================================================

CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 1

WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END

    SET @Index = @Index + 1
END

RETURN @OutputString

END

(4) Read XML Data
=======================================================================

 ALTER PROCEDURE [dbo].[Proc_Insert_BatchData]          
(                    
@XMLBatchData XML                    
)                    
AS                    
BEGIN              
               
INSERT INTO trans_Dataloaderbatchdata (BatchId,SiteName,SiteId,VisitName,VisitId,SubjectName,          
SubjectId,PageName,PageId,ItemId,ItemName,ItemValue,PanelId,CycleId,TrialId,StatusId,  
--CreatedBy,CreatedIp,GeneratedControlName,Rownum,Colnum,PreviousItemValue)                    
CreatedBy,CreatedIp,GeneratedControlName,Rownum,Colnum)
SELECT T.Item.query('./BatchId').value('.', 'INT') BatchId,            
T.Item.query('./SiteName').value('.', 'VARCHAR(100)') SiteName,                    
T.Item.query('./SiteId').value('.', 'INT') SiteId,            
T.Item.query('./VisitName').value('.', 'VARCHAR(100)') VisitName,                
T.Item.query('./VisitId').value('.', 'INT') VisitId,          
T.Item.query('./SubjectName').value('.', 'VARCHAR(100)') SubjectName,                    
T.Item.query('./SubjectId').value('.', 'INT') SubjectId  ,          
T.Item.query('./PageName').value('.', 'VARCHAR(100)') PageName,          
T.Item.query('./PageId').value('.', 'INT') PageId  ,              
T.Item.query('./ItemId').value('.', 'INT') ItemId  ,              
T.Item.query('./ItemName').value('.', 'VARCHAR(500)') ItemName  ,            
T.Item.query('./ItemValue').value('.', 'VARCHAR(500)') ItemValue  ,      
T.Item.query('./PanelId').value('.', 'INT') PanelId  ,        
T.Item.query('./CycleId').value('.', 'INT') CycleId  ,      
T.Item.query('./TrialId').value('.', 'INT') TrialId  ,          
T.Item.query('./StatusId').value('.', 'INT') StatusId  ,          
T.Item.query('./CreatedBy').value('.', 'INT') CreatedBy   ,              
T.Item.query('./CreatedIP').value('.', 'VARCHAR(50)') CreatedIP  ,    
T.Item.query('./GeneratedControlName').value('.', 'VARCHAR(500)') GeneratedControlName,  
T.Item.query('./Rownum').value('.', 'VARCHAR(50)') Rownum  ,    
T.Item.query('./Colnum').value('.', 'VARCHAR(50)') Colnum
--, T.Item.query('./PreviousItemValue').value('.', 'VARCHAR(500)') PreviousItemValue
FROM @XMLBatchData.nodes('/Rows/Row') AS T(Item)                    
RETURN 0                    

END    

(5)Dynamic sql
================================================================

DECLARE @dynSql NVARCHAR(max)

set @dynSql='                              
  Insert into '+ @PanelTable + ' (TrialId, SiteId, CycleId,  VisitId, PageId, PanelId, SubjectId,RowNum,CreatedBy,CreatedOn,
  CreatedIP,VisitNo,VisitCode,CreatedSiteTime,'+@ItemColumns+')                                      
  values( '+ Convert(varchar(20),@TrialId)+','+Convert(varchar(20),@SiteId)+','+Convert(varchar(20),@CycleId)+','+Convert(varchar(20),@VisitId)+',
  '+Convert(varchar(20),@PageId)+','+Convert(varchar(20),@PenelId)+','+Convert(varchar(20),@SubjectId)+','+Convert(varchar(20),@RowNo)+',
  '+Convert(varchar(20),@userid)+',getdate(),'''+Convert(varchar(100),@CreatedIP)+''','''+Convert(varchar(100),@VisitNo)+''',
  '''+Convert(varchar(100),@VisitCode)+''',dbo.fn_GetSiteTime('+Convert(varchar(20),@SiteId)+'),'+@ItemValues+')'

EXEC sp_executesql @dynSql

(6)Triggers after insert and after update
=================================================================

ALTER TRIGGER [dbo].[InsertDoctorInfoOnDoctor] ON [dbo].[DoctorDetails]
FOR INSERT
AS
DECLARE @USERID AS INT=0
select @USERID=i.UserId from inserted i;
IF(@USERID!='0')
BEGIN
END

ALTER TRIGGER [dbo].[UpdateDoctorInfoOnDoctor] ON [dbo].[DoctorDetails]
FOR UPDATE
AS
DECLARE @USERID AS INT=0
select @USERID=i.UserId from inserted i;
IF(@USERID!='0')
BEGIN


END

(7)Query Performance
=====================================================================

SET @LowerBand  = (@CurrentPage - 1) * @PageSize
SET @UpperBand  = (@CurrentPage * @PageSize) + 1
WITH tempProfile AS(
SELECT
[dbo].[ICUsersAppointmentsInfo_Report].[ICAppointmentId] AS ICAppointmentId,
ROW_NUMBER() OVER (ORDER BY [dbo].[ICUsersAppointmentsInfo_Report].[AutoId] DESC) AS RowNumber    
FROM [dbo].[ICUsersAppointmentsInfo_Report]
where [dbo].[ICUsersAppointmentsInfo_Report].[HospitalId]=1
)

SELECT * FROM  tempProfile WHERE  RowNumber > CONVERT(VARCHAR,@LowerBand) AND RowNumber < CONVERT(VARCHAR, @UpperBand)


(8)Get procedures created date and modifies dates
=====================================================================
select name,create_date,modify_date
from sys.procedures
order by create_date desc

(9)Generate Class from database table
=====================================================================

declare @TableName sysname = 'TableName'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'string'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
            then '?'
            else ''
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + '
}'

print @Result