Print Attributes in Stored Procedure

[Wim van Rosmalen]

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 correct 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. It's
as simple as

ALTER PROCEDURE Hello
AS
BEGIN
PRINT 'Hello'
END
and nothing is seen. Does anybody know? Thank you.

Wim

[Simon Hayes]

"Wim van Rosmalen" <wv******@tisca li.nl> wrote in message
news:41******** **************@ dreader2.news.t iscali.nl...

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 correct 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. It's
as simple as

ALTER PROCEDURE Hello
AS
BEGIN
PRINT 'Hello'
END
and nothing is seen. Does anybody know? Thank you.

Wim

I'm not at all sure, but if your code works as expected in Query Analyzer,
but you're running from an ADP, then perhaps Access is not handling the
PRINT output correctly - it's returned as an informational error (see "Using
PRINT" in Books Online). You might want to investigate how Access handles
this specific output - posting in an Access newsgroup might be useful.

Simon

[Erland Sommarskog]

Wim van Rosmalen (wv******@tisca li.nl) writes:

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:
...
IF @find IS NULL
BEGIN
PRINT 'You didn't enter any value'
RETURN
END
...

This works correct 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. It's as simple as

SQL Server is not Access, so a PRINT statement does not by itself outupt
something anywhere. It is up to the client to pick it up. And then you
will have to code for it in the client. From Access, you usually access
SQL Server from ADO, and ADO is not very good making messages avilable
anyway.

You may want to try to use RAISERROR instead:

RAISERROR('You didn''t enter any value', 11, 1)

this causes ADO to raise an error as well.

Disclaimer: I don't know Access or ADP, but I'm speaking from general
experience of ADO programming.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Wim van Rosmalen]

Thanks Erland! I knew about RAISERROR, but in a S.P. like this, PRINT is
returned by the same configuration (MSDE, which doesn't have a
query-analyser):

ALTER PROCEDURE StoredProcedure 1
AS
SET NOCOUNT ON

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 RecordTable
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 "RecordTabl e" is changed, Error Message Nr. One is displayed
(related to a high number;.
--related to a low number an empty table is displayed).
FROM RecordTable
WHERE title LIKE 'Ta%'
END
ELSE
BEGIN
SET NOCOUNT ON
SET @msg = 'Error Message Nr. Two'
PRINT @msg
SELECT title
--If the name "RecordTabl e" is changed, Error Message Nr. Two is displayed
(related to a low number;.
--related to a high number titles beginning with TA are displayed).
FROM RecordTables
WHERE title LIKE 'Te%'
END

Best regards,

Wim van Rosmalen

"Erland Sommarskog" <es****@sommars kog.se> schreef in bericht
news:Xn******** **************@ 127.0.0.1...

Wim van Rosmalen (wv******@tisca li.nl) writes:

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:
...
IF @find IS NULL
BEGIN
PRINT 'You didn't enter any value'
RETURN
END
...

This works correct 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. It's as simple as

SQL Server is not Access, so a PRINT statement does not by itself outupt
something anywhere. It is up to the client to pick it up. And then you
will have to code for it in the client. From Access, you usually access
SQL Server from ADO, and ADO is not very good making messages avilable
anyway.

You may want to try to use RAISERROR instead:

RAISERROR('You didn''t enter any value', 11, 1)

this causes ADO to raise an error as well.

Disclaimer: I don't know Access or ADP, but I'm speaking from general
experience of ADO programming.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Wim van Rosmalen (wv******@tisca li.nl) writes:

Thanks Erland! I knew about RAISERROR, but in a S.P. like this, PRINT is
returned by the same configuration
Yes, ADO is inconsistent in how it handles PRINT statements. It does
slightly better on errors.
(MSDE, which doesn't have a query-analyser):

You can use the command-line tool OSQL to run the queries. There you
will always see the PRINT statements.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Wim van Rosmalen]

O.K. Erland, thanks again!

Wim

"Erland Sommarskog" <es****@sommars kog.se> schreef in bericht
news:Xn******** **************@ 127.0.0.1...

Wim van Rosmalen (wv******@tisca li.nl) writes:

Thanks Erland! I knew about RAISERROR, but in a S.P. like this, PRINT is
returned by the same configuration

Yes, ADO is inconsistent in how it handles PRINT statements. It does
slightly better on errors.

(MSDE, which doesn't have a query-analyser):

You can use the command-line tool OSQL to run the queries. There you
will always see the PRINT statements.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Malcolm Cook]

For MS verbiage on the topic, take a look at

http://support.microsoft.com/default...NoWebContent=1
"Wim van Rosmalen" <wv******@tisca li.nl> wrote in message
news:41******** **************@ dreader2.news.t iscali.nl...

O.K. Erland, thanks again!

Wim

"Erland Sommarskog" <es****@sommars kog.se> schreef in bericht
news:Xn******** **************@ 127.0.0.1...

Wim van Rosmalen (wv******@tisca li.nl) writes:

Thanks Erland! I knew about RAISERROR, but in a S.P. like this, PRINT is returned by the same configuration

Yes, ADO is inconsistent in how it handles PRINT statements. It does
slightly better on errors.

(MSDE, which doesn't have a query-analyser):

You can use the command-line tool OSQL to run the queries. There you
will always see the PRINT statements.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp