asked on 8/9/2015

Insert Record if no records found - SQL

I need to insert a record if no records exist. In my example below, It returned no records; therefore I need it to return the current date for the created, for CreatedBy it can be 'N/A' and Description equal to 'No Correspondence Notes'.

DECLARE @PATIENTVISITID INT = 695

SELECT
	  pc.Created
	, ISNULL ( NULLIF ( REPLACE ( pc.CreatedBy , CHAR ( 0 ) , '' ) , '' ) , 'System' ) AS CreatedBy 
	, CASE
		 WHEN pc.Description = '**long**' THEN pc.DescriptionLong
		 ELSE pc.Description
	  END AS Description
FROM PatientCorrespondence pc
WHERE pc.PatientVisitID = @PATIENTVISITID;

Open in new window

Microsoft SQL Server Microsoft SQL Server 2008 SQL

Last Comment

Jeff S

8/10/2015

Mike Eghtebas

8/9/2015

Is PatientVisitID PK? If so, then we need to add two more new lines to suppress identity effect.

Try:

DECLARE @PATIENTVISITID INT
As
Begin
DECLARE @Created dateTime = GEtDate();
DECLARE @CreatedBy varchar(50)= 'N/A';
DECLARE @Description varchar(50) ='No Correspondence Notes';
  
SELECT
	  pc.Created
	, ISNULL ( NULLIF ( REPLACE ( pc.CreatedBy , CHAR ( 0 ) , '' ) , '' ) , 'System' ) AS CreatedBy 
	, CASE
		 WHEN pc.Description = '**long**' THEN pc.DescriptionLong
		 ELSE pc.Description
	  END AS Description
FROM PatientCorrespondence pc
WHERE pc.PatientVisitID = @PATIENTVISITID;
)

if @@ROWCOUNT = 0
Begin

 Insert Into PatientCorrespondence (Created, CreatedBy, [Description], PatientVisitID) Values
                        (@Created, @CreatedBy, @Description, @PATIENTVISITID); 
End
End