October 22, 2014 at 10:29 am
Jack Corbett (10/22/2014)
katland226 (10/22/2014)
Well, I just realized that the message is indeed printing... But instead of printing the message in the results tab, it instead prints in the messages tab.Okay, so it's working, but that's not exactly where I need it to be printed. Unless, it should be fine this way?
I think for your requirements it will probably be okay. It isn't the way I'd handle it in an application, but for calling it within SSMS it is okay for informational messages. In a real-world application I'd let the application calling the procedure create the message when no rows are returned.
Alright, then. Thank you all very much for your help. I really do appreciate it.
October 22, 2014 at 11:44 am
In a real system you'd never do either of those prints in a stored proc. The application would check that a city has been selected before calling the proc and the application would check the row count and return a useful message to the user if no rows are returned.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2014 at 11:59 am
You hate stored procs? Why? Did you think that there is a really good reason why your teacher is shoving them down your throat?
That being said, he may not be giving you the why.
Change the PRINT to SELECT. See of that gives you what you want, or expect.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 23, 2014 at 3:33 am
katland226 you might hate stored procedures now and I hope one day you will understand them and favour them in your development as I do, but I also hope you appreciate that right here and now you have some of the top SQL gurus in the whole World giving you advice. Wow!
October 23, 2014 at 7:30 am
Don't feel bad about hating stored procedures, there are others that prefer not to use them. 🙂 That being said they do have a purpose and aren't that much different. Just write your SQL to do what you want, then add the code to create the stored procedure, then execute it.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 24, 2014 at 7:22 am
You also can consider writing your SP this way:
CREATE PROC KRCityContacts
@city varchar(20) = NULL,
@OUTMSG VARCHAR(100) OUTPUT
AS
SET @OUTMSG = ''
IF @city IS NOT NULL
BEGIN
SELECT CompanyName
FROM Customer
WHERE CompanyName IS NOT NULL
AND City = @city
UNION
SELECT CompanyName
FROM Supplier
WHERE City = @city
IF @@rowcount = 0
SET @OUTMSG = 'No Suppliers or Customers in the city of ' + @city
END
ELSE
SET @OUTMSG = 'Please enter a city.'
GO
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply