Print Statement in Stored Procedure

[Wim van Rosmalen]

Hi there,

I've upgraded MS-Access 2002 to a MS-Access Project (adp), so now I have to
deal with more sophisticated queries (may I call them so?) like stored
procedures. I have a form with a combobox for selections and a textbox to
enter a certain value. Let us say I call the combobox @select and the
textbox @find. The combobox always shows the first of the items to select.
Now I want to return a message if nothing is found, or if nothing has been
entered at all. The stored procedure reads for instance:

ALTER PROCEDURE OPC
@select nvarchar(20), @find nvarchar (100)

AS

SET NOCOUNT ON

IF
@find IS NULL
BEGIN
PRINT 'You didn't enter any value'
RETURN
END

IF
@select = 'Author'
BEGIN
SELECT *
FROM Books
WHERE Author LIKE '%' + @find + '%'
ORDER BY Author, Title, Publisher, Year
END

[And so on]

RETURN

This works correctly when I enter something, but when I don't enter any
value, a message box pops up saying that the stored procedure has been
executed, but no records were found. I want to see an error message like
above. But when I have a S.P. like this:

ALTER PROCEDURE StoredProcedure1

AS

DECLARE @msg varchar(255)
-- If 53000 is changed into 3000, titles beginning with TE are displayed.
-- If 53000 (or any high number) is not changed, titles beginning with TA
are displayed.

IF (SELECT COUNT(Nr)
FROM Books
WHERE Title LIKE 'Ta%' AND Nr BETWEEN 2000 AND 53000) > 0
BEGIN
SET NOCOUNT ON
SET @msg = 'Error Message Nr. One'
PRINT @msg
SELECT title
--If the name "Books" is changed, Error Message Nr. One is displayed
(related to a high number;
--related to a low number an empty table is displayed).
FROM Books
WHERE title LIKE 'Ta%'
END
ELSE
BEGIN
SET NOCOUNT ON
SET @msg = 'Error Message Nr. Two'
PRINT @msg
SELECT title
--If the name "Books" is changed, Error Message Nr. Two is pops up (related
to a low number;
--related to a high number titles beginning with TA are displayed).
FROM Books
WHERE title LIKE 'Te%'
END

..... the PRINT statement is correctly returned.
I am advised to use RAISERROR instead of PRINT, but that doesn't explain why
the same configuration doesn't always return the PRINT attribute. If anybody
knows, I'll be most grateful. Thank you.


Wim van Rosmalen