Back again with more stored procedure troubles

Question

Back again with more stored procedure troubles

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

katland226 SSC Enthusiast Points: 127 More actions · Answer 1

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael L John One Orange Chip Points: 27060 More actions · Answer 3

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/

P Jones SSChampion Points: 12361 More actions · Answer 4

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!

below86 SSChampion Points: 11538 More actions · Answer 5

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.

tatiana.golfieri SSC-Addicted Points: 426 More actions · Answer 6

Hi! Try this to call it. Then you can use @rc or not.

DECLARE @CITY VARCHAR(20), @rc INT

EXEC KRCityContacts 'Asheville', @rc

SELECT @rc

tatiana.golfieri SSC-Addicted Points: 426 More actions · Answer 7

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