Can anyone do this SProc with a single select

[amurgett]

The following SQL has two identical select statements, the second of which is needed as the last thing in the stored proc (the 2nd select) is sent back to Crystal Reports (Crystal sees the last thing processed in the SP).

Is there a way of doing the following with a single select with a call within the sql to the insert statement, possibly using a user defined function (SQL S 2000 used)...

CREATE PROCEDURE dbo.uspCRYInsertTest
@ReportID INT
AS

-- Drop the the table for testing purposes
DELETE FROM tbl_cry_assessor

--Insert the assessor_id into the table
INSERT INTO tbl_cry_assessor SELECT assessor_id, getdate() FROM tbl_Crystal_HoldingData WHERE Report_ID > @ReportID

--Return the records for the report.
SELECT * FROM tbl_Crystal_HoldingData WHERE Report_ID > @ReportID
GO

 

[tlbroadbent]

No, you can't perform an insert and return a result set with a select in the same SQL statement. I can't understand why you would want to do this. You are performing three distinct functions in the SP. Is there a problem with this process that prompted your request? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tlbroadbent]

Actually, I don't see the purpose of the delete and insert statements as you select from tbl_Crystal_HoldingData rather than tbl_cry_assessor. So I guess you could just eliminate those statments and execute only the select query. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.