Basically, I am trying to create a t-sql that send email alerts to me. I have attached the queries below. Basically, the query starts at SELECT and end at Tat 0 is what I want to execute and return those data via email. When I run that query alone, it returns it fine. I suspect its something inside this whole t-sql statement that is not correct. I'm kind of a db newb. Any clarification would be extremely helpful.
Declare @dict int
,@tableHTML NVARCHAR(MAX)
,@studyDate varchar(25)
,@patientID varchar(25)
,@patient varchar(50)
,@report varchar(max)
,@subj varchar(100)
,@dr varchar(50)
,@approve varchar(25)
,@inst varchar(50)
if @dict > 0
begin
declare cursorName CURSOR for
SELECT PATIENTS.FirstName, PATIENTS.Lastname, STUDIES.study_datetime, STUDY_LIST_VIEW.tat, PATIENTS.Patient_ID
FROM opalrad.dbo.INSTITUTIONS INSTITUTIONS, opalrad.dbo.PATIENTS PATIENTS, opalrad.dbo.STATUSES STATUSES, opalrad.dbo.STUDIES STUDIES, opalrad.dbo.studies_calc studies_calc, opalrad.dbo.STUDY_LIST_VIEW STUDY_LIST_VIEW, opalrad.dbo.TRANSCRIPTION_TEXT TRANSCRIPTION_TEXT
WHERE STUDIES.study_id = studies_calc.study_id AND STUDIES.institution_id = INSTITUTIONS.institution_id AND TRANSCRIPTION_TEXT.STUDY_ID = STUDIES.study_id AND TRANSCRIPTION_TEXT.STUDY_ID = studies_calc.study_id AND PATIENTS.Patient_ID = STUDIES.patient_id AND STUDY_LIST_VIEW.INSTITUTION_ID = INSTITUTIONS.institution_id AND STUDY_LIST_VIEW.PATIENT_ID = PATIENTS.Patient_ID AND STUDY_LIST_VIEW.study_id = STUDIES.study_id AND STUDY_LIST_VIEW.study_id = studies_calc.study_id AND ((INSTITUTIONS.institution_name='TEST') AND (STUDY_LIST_VIEW.tat=0))
OPEN cursorName
FETCH NEXT FROM cursorName
INTO @studyDate, @patientID, @patient
While @@FETCH_STATUS = 0
Begin
set @tableHTML = '<html><body><H2>PRI Tat 3</H2><p><b> Patient Name: </b>'+ @patient + '</p><p><b>Patient ID: </b>' + @patientID +
'</p><p><b>Study Date: </b>' + @studyDate
set @subj = '**** PRI Tat 3 **** ' + @patient + ' ' + @studyDate
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TRGEmail',
@recipients = 'test@test.com',
@body_format='HTML',
@body = @tableHTML,
@subject = @subj ;
FETCH NEXT FROM cursorName
INTO @studyDate, @patientID, @patient
End
CLOSE cursorName
end
GO
Comparison QUERY THAT WORK
Declare @dict int
,@tableHTML NVARCHAR(MAX)
,@studyDate varchar(25)
,@patientID varchar(25)
,@patient varchar(50)
,@subj varchar(100)
,@draftDate varchar(50)
,@doctor varchar(50)
SELECT
@dict = count([status_id])
FROM [opalrad].[dbo].[STUDIES]
WHERE status_id =1009
if @dict > 0
begin
declare cursorName CURSOR for
select Patient_id_dicom,
PATIENTS.Lastname + ', ' + PATIENTS.FirstName as patient,
study_datetime,
USER_NAME,
Dt
from opalrad.dbo.STUDIES,
opalrad.dbo.PATIENTS,
opalrad.dbo.USERS,
opalrad.dbo.STUDY_STATUS_LOG
where STUDIES.status_id =1009 and
STUDIES.study_id = STUDY_STATUS_LOG.STUDY_ID and
STUDIES.patient_id = PATIENTS.Patient_ID and
STUDY_STATUS_LOG.USER_ID = USERS.USER_ID and
USERS.USER_ID in (23) and
STUDY_STATUS_LOG.STATUS_ID = 1009
OPEN cursorName;
FETCH NEXT FROM cursorName
INTO @patientID, @patient, @studyDate, @doctor, @draftDate
While @@FETCH_STATUS = 0
Begin
set @tableHTML = '<html><body>Please Investigate DRAFT Studies<p><b>Patient: </b>' + @patient + '</p><p><b>Patient ID: </b>' + @patientID +
'</p><p><b> Study Date: </b>' + @studyDate + '</p><p><b>Doctor: </b>' + @doctor + '</p><p><b>Draft Time: </b>' + @draftDate + '</p>'
set @subj = 'DRAFT Studies Needs Investigating ' + @patient + ' ' + @studyDate
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TRGEmail',
@recipients = 'test@test.com',
@body_format='HTML',
@body = @tableHTML,
@importance ='High',
@subject = @subj ;
FETCH NEXT FROM cursorName
INTO @patientID, @studyDate, @patient, @doctor, @draftDate
End
CLOSE cursorName;
end
GO
T-sql email almost working.....
Posted - 01/23/2013 : 10:21:04
