SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

To KILL All the process in the current databases

ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE mydb SET MULTI_USER
GO

Concat string Example using CTE

WITH cte(Centerid,PatientId,patientName,RefDocName,PhoneNo ,visitid,VisitDate,StudyName,StudyState) as (
  Select
       P.CenterId,
       p.externalID as PatientId,
        P.FirstName as patientName,
        R.FirstName as RefDocName,
        P.Phone1 as PhoneNo,
        V.visitid,
        V.VisitDate,
        bm.billingname as StudyName,
        CASE when s.StudyState=4 THEN 'Inprogress'
             when s.StudyState=3 THEN 'Completed' End as StudyState
  from TblPatient
 
  as P JOIN TblVisits as V On V.Patientid=P.patientid and V.CenterId=P.CenterId
  JOIN TblStudy as S On S.Visitid=V.visitid and S.Centerid=V.Centerid
  JOIN TblBillingMaster as BM On BM.Billingid=S.Studytypeid and Bm.CenterId=S.CenterId
  JOIN tblReferralDoctor as R ON R.ReferalID=v.ReferalID and R.CenterId=V.CenterId
 
  Where P.Obsolete=0 and V.Obsolete=0 AND S.StudyState In(3,4) and bm.ModalityTypeID!=12 )

  

SELECT CenterId,PatientId,patientName,RefDocName,PhoneNo,visitid,VisitDate,StudyState as Status, STUFF((SELECT ',' + b.StudyName from cte b Where a.Centerid=B.Centerid and a.visitid=b.visitid  FOR XML PATH(''), TYPE ).value('.','varchar(max)') ,1,1, '') AS StudyName
FROM cte a
    GROUP BY    a.CenterId,a.PatientId,a.patientName,a.RefDocName,a.PhoneNo,a.visitid,a.VisitDate,a.StudyState
    
 

Delete Duplicate Rows Example

Delete A FROM (
     SELECT ROW_NUMBER()OVER(PARTITION BY PatientId ORDER BY LASTMODIFIEDDATE   DESC) RANK1,PatientId
     FROM TblPatient WHERE CenterId = 10 ) A  WHERE RANK1 > 1

Alter Example

--ADD Column
IF NOT EXISTS(Select * from INFORMATION_SCHEMA.COLUMNS
where Table_name='tblsample' and Column_name='MobileNo')
BEGIN
ALTER TABLE tblsample add MobileNo bigint NULL
END

--Create Table
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS         
WHERE TABLE_NAME = 'TblReportMaster' )       
BEGIN       
CREATE TABLE [dbo].[TblReportMaster](       
 [ReportID] [int] NULL,       
 [ReportDisplayName] [nvarchar](100) NULL,       
 [SPName] [nvarchar](100) NULL       
) ON [PRIMARY]       
END

--DROP COLUMN
IF  EXISTS(Select * from INFORMATION_SCHEMA.COLUMNS where Table_name='tblsample' and Column_name='SEX')
BEGIN
ALTER TABLE tblsample
DROP COLUMN SEX
END

--Set Primary key and Foreign Key
IF EXISTS(Select * from INFORMATION_SCHEMA.COLUMNS where Table_name='tblsample' and Column_name='SEX')
BEGIN
ALTER TABLE salesman
ADD CONSTRAINT prim_sales PRIMARY KEY (no),
CONSTRAINT foreign_sales FOREIGN KEY(product_no)
REFERENCES product
END

With Query Example

 with sam as 
(
 select * from TblSample
  )
  select * from  sam as s join TblSample1 as Sn on sn.id=s.id

OutPut
ID	Name	Age	Sex	ID	Name	Age	Sex
1	vino	23	Male	1	vino	23	Male
2	karthick	25	Male	2	karthick	25	Male
3	Rajesh	24	Male	3	Rajesh	24	Male

Insert Query Example

CREATE PROC  [dbo].[Tblsample_insert]
(
 @Name varchar(40),
 @Age int,
 @Sex varchar(45)
 )
AS
BEGIN
INSERT INTO [LINQ].[dbo].[TblSample]
           ([Name]
           ,[Age]
           ,[Sex])
     VALUES
           (@NAME,
            @Age,
            @Sex)
END