Back again with more stored procedure troubles
-
Have I mentioned that I hate stored procedures? Because I do. With a passion. I can figure out everything BUT these stored procedures, and my teacher keeps shoving them down my throat.
Okay, so I'm supposed to create a stored procedure that returns the company names from the Customer and Supplier tables in this database within the specified city. No problem, right?
Well, I also have to make it print a message if no city is entered (that went fine), and when the entered city doesn't match anything in the database - which therein lies the problem. This is the code I tried to use:
CREATE PROC KRCityContacts@city varchar(20) = NULL,
@rowcount INT OUTPUT
AS
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
SET @rowcount = @@ROWCOUNT
IF @rowcount = 0
PRINT 'No Suppliers or Customers in the city of ' + @city
END
ELSE
PRINT 'Please enter a city.'
GO
But executing that only returns this error:
Msg 201, Level 16, State 4, Procedure KRCityContacts, Line 0
Procedure or function 'KRCityContacts' expects parameter '@rowcount', which was not supplied.
So what am I doing wrong now? Why must these stored procedures be so difficult? Sorry if I sound frustrated, but... quite frankly, I am very much so.
-
You didn't write how you run the procedure, but it looks like you don't pass it the output parameter. When you use the procedure do you pass to it 1 parameter or 2 parameters?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
This is how I executed the procedure:
EXEC KRCityContacts 'Asheville'But I only want to show the results, not the actual row count - I just want to test it as a condition so I can have the proper statement print when said condition returns true. How should I be executing this instead?
-
@rowcount should be a variable within the procedure not a parameter of the procedure.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
In that case this is the problem. You can pass it a parameter for @rowcount or you can modify the procedure, so it won't have this output parameter.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
CREATE PROC KRCityContacts@city varchar(20) = NULL
AS
DECLARE @rowcount int
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
SET @rowcount = @@ROWCOUNT
IF @rowcount = 0
PRINT 'No Suppliers or Customers in the city of ' + @city
END
ELSE
PRINT 'Please enter a city.'
GO
EXEC KRCityContacts 'Greenville'
I just tried that, but it literally returns nothing. I want it to print a message, 'No Suppliers or Customers in the city of ' + @city, when no rows are returned, but that's not happening for some reason.
-
Looks fine...
What does this return?
DECLARE @city varchar(20)SELECT @City = 'Greenville'
SELECT CompanyName
FROM Customer
WHERE CompanyName IS NOT NULL
AND City = @city
UNION
SELECT CompanyName
FROM Supplier
WHERE City = @city
Also, where are you checking to see if it prints something out?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The @city needs to stay as a parameter entered manually into the procedure. It's part of the instructions I was given.
Also, right here is where I'm trying to test the condition to make it print that particular message:
SELECT CompanyNameFROM Customer
WHERE CompanyName IS NOT NULL
AND City = @city
UNION
SELECT CompanyName
FROM Supplier
WHERE City = @city
SET @rowcount = @@ROWCOUNT --This is me trying to set the variable to check rows returned
IF @rowcount = 0 --Here is the conditional statement
PRINT 'No Suppliers or Customers in the city of ' + @city --and here is the attempt to print message
-
What does the query I posted return please?
I wasn't asking where in the code you were doing the IF, I can read T-SQL fine. Where are you looking when you run the procedure, to see if the message has printed out or not? What tool are you using and where in that tool are you checking?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It still returns the same thing... that is, nothing.
-
And I am using Microsoft SQL Server Management Studio. I am looking for the results to appear in the output window, where they always do.
-
Ok, so we've confirmed that there are no rows for the city Greenville.
Which tab of the output are you looking at for the printed results?
Screenshot?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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?
-
The key thing that Gail is asking, is what are you using to run the query? If you are using a .NET application that you are writing to run the query the PRINT message won't get returned to a DataSet/RecordSet, etc..., you need to use the SQLConnection.InfoMessage event (adding a handler) to see the messages returned. If you are using SQL Server Management Studio to run the query then the PRINT message shows in the messages tab, not the results tab.
Edit: Posted this after the OP found the message in the messages tab.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply