Pause Stored Procedure for User Input

[majorbroncosfan]

I am trying to set up a stored procedure that will enable my users to print out one page at a time on the screen and to wait for them to press a key for additional output. Is there any way to do this?

 

[samshiell]

Unless there's something wierdly and undocumentedly obscure the answer is : No, there is no such thing

Of course someone will probably prove me wrong.


Sam

 

[SQLSister]

You can't pause the stored procedure. What you can do is create the stored procedure such that it takes the parameter values which indicate which page the person wants and run it every time the person presses the button.

There might be less network traffic if you send the whole recordset and then let the user interface handle the paging. This of course would depend on the size of the recordset and the likelihood that the user will be going through all the pages.

 

[majorbroncosfan]

Can I do this via SQL itself or do I need to do it via some other method?

 

[donutman]

SQLSister gave you the answer -- "every time the person presses the button." I.e. program modification required.
-Karl

 

[bertrandkis]

Can you post your stored procedure here and I may show you an alternative way.

 

[majorbroncosfan]

Thank you in advance. I want to be able to print to screen after only a certain number of records within the cursor.

CREATE PROCEDURE PACSDaily
AS
DECLARE @study_id char(30),
@pat_name char(32),
@ris_pat_id char(32),
@exam_date datetime,
@act_ward varchar(32),
@institution varchar(32),
@organ_name varchar(32),
@study_descr varchar(64),
@image_cnt int,
@exam_case_id varchar(32),
@arch_len int,
@exam_time datetime,
@rep_rad varchar(64),
@modality_code varchar(16),
@msg1 varchar(255)

DECLARE get_daily CURSOR
FOR
SELECT
LTRIM(RTRIM(e.study_id)),
RTRIM(p.pat_name),
RTRIM(p.ris_pat_id),
e.exam_date,
RTRIM(p.act_ward),
RTRIM(e.institution),
RTRIM(e.organ_name),
RTRIM(e.study_descr),
e.image_cnt,
RTRIM(e.exam_case_id),
e.arch_len,
e.exam_time,
RTRIM(e.rep_rad),
RTRIM(e.modality_code)
FROM examination e,patient p
WHERE p.pat_ckey = e.pat_ckey
AND e.exam_date = CONVERT(char(10),GETDATE(),102)
AND p.act_ward LIKE ' '
ORDER BY p.ris_pat_id

OPEN get_daily

WHILE (@@sqlstatus = 0)
BEGIN
FETCH get_daily INTO
@study_id,
@pat_name,
@ris_pat_id,
@exam_date,
@act_ward,
@institution,
@organ_name,
@study_descr,
@image_cnt,
@exam_case_id,
@arch_len,
@exam_time,
@rep_rad,
@modality_code

PRINT '-------------------------------------------------------------------------------------------------------'
SELECT @msg1 = SPACE(1) + 'study_id: ' + SPACE(5) + ISNULL(CONVERT(CHAR(35),@study_id), SPACE(35)) + CHAR(10) +
SPACE(1) + 'pat_name: ' + SPACE(5) + ISNULL(CONVERT(CHAR(35), @pat_name),SPACE(35)) + CHAR(10) +
SPACE(1) + 'ris_pat_id: ' + SPACE(3) + ISNULL(CONVERT(CHAR(35),@ris_pat_id),SPACE(35)) + CHAR(10) +
SPACE(1) + 'exam_date: ' + SPACE(4) +ISNULL(CONVERT(CHAR(35),@exam_date,102),SPACE(35)) + CHAR(10) +
SPACE(1) + 'act_ward: ' + SPACE(5) + ISNULL(CONVERT(CHAR(35),@act_ward),SPACE(35)) + CHAR(10)

PRINT @msg1

SELECT @msg1 = SPACE(1) + 'institution: ' + SPACE(2) + ISNULL(CONVERT(CHAR(35),@institution), CHAR(35)) + CHAR(10) +
SPACE(1) + 'organ_name: ' + SPACE(3) + ISNULL(CONVERT(CHAR(35),@organ_name),SPACE(35)) + CHAR(10) +
SPACE(1) + 'study_descr: ' + SPACE(2) + ISNULL(CONVERT(CHAR(35),@study_descr),SPACE(35)) + CHAR(10) +
SPACE(1) + 'image_cnt: ' + SPACE(4) + ISNULL(CONVERT(CHAR(35),@image_cnt),SPACE(35)) + CHAR(10) +
SPACE(1) + 'exam_case_id: ' + SPACE(1) + ISNULL(CONVERT(CHAR(35),@exam_case_id),SPACE(35)) + CHAR(10)

PRINT @msg1

SELECT @msg1 = SPACE(1) + 'arch_len: ' + SPACE(5) + ISNULL(CONVERT(CHAR(35),@arch_len),SPACE(35)) + CHAR(10) +
SPACE(1) + 'exam_time: ' + SPACE(4) + ISNULL(CONVERT(CHAR(35),@exam_time,108),SPACE(35)) + CHAR(10) +
SPACE(1) + 'rep_rad: ' + SPACE(6) + ISNULL(CONVERT(CHAR(35),@rep_rad),SPACE(35)) + CHAR(10) +
SPACE(1) + 'modality_code: ' + ISNULL(CONVERT(CHAR(35),@modality_code),SPACE(35)) + CHAR(10) + CHAR(13)
PRINT @msg1
PRINT '-------------------------------------------------------------------------------------------------------'
END

CLOSE get_daily
DEALLOCATE CURSOR get_daily