April 4, 2005 at 6:45 am
When the following stored procedure is parsed in QA, I receive the following error:
"A RETURN statement with a return value cannot be used in this context."
Does anyone see any obvious mistake here? TIA!
DECLARE
@return_code INTEGER
SELECT @return_code = 0
DELETE
FROM documents
WHERE document_type_uid = 2
AND document_uid NOT IN (SELECT document_uid FROM actiongrams)
AND document_uid NOT IN (SELECT document_uid FROM actiongram_trees)
AND document_uid NOT IN (SELECT parent_document_uid FROM document_attachments)
AND document_uid NOT IN (SELECT child_document_uid FROM document_attachments)
AND document_uid NOT IN (SELECT document_uid FROM document_notes)
AND document_uid NOT IN (SELECT document_uid FROM folders_documents)
AND document_uid NOT IN (SELECT document_uid FROM work_items)
AND document_uid NOT IN (SELECT document_uid FROM work_item_tracking)
SELECT @return_code = @@ERROR
IF (@return_code <> 0)
BEGIN
RAISERROR('[%d] msp_DocumentMgt_AutoPurgeDocumentsTemporary: Error purging unused temporary documents.',
15,
1,
@return_code)
RETURN @return_code
END
April 4, 2005 at 7:31 am
sounds like you have highlighted the contents of the procedure for parsing. You can't use return if the Create procedure OR alter procedure statements are not present.
HTH
* Noel
April 4, 2005 at 7:34 am
I tried the code in a stored proc and it returned the error as expected without any furter message. The problem comes when you try to return a value from within query analyser. As there's no real caller, there's nowhere to return the value to.
Just use this code in your sp and you'll be fine.
On anoter note, have you tried converting your not in to not exists in your query? I think it would run much, much faster, especially if there's a lot of data to check.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply