Simple Text-Based Report MS SQL Server 2008 using PRINT

Question

I am nearly finished with my first assignment but the last question has me stumped.

It asks:

Produce a faulty product report that displays the product returned on the 11/07/2010 due to Damaged Exterior

The expected output for this is:

FAULTY PRODUCT REPORT

--------------------------------------

Product Name: Desk

Customer Name: Simon, Kernel

Reason: Damaged Exterior

(1 rows(s) affected)

---END OF REPORT---

I found out about SQL PRINT now I seem to nearly have it all down to a T:

DECLARE @ProductName varchar(30)
SET @ProductName =(SELECT MIN(Products.ProductName) FROM Products)

DECLARE @CustomerName varchar(75)
SET @CustomerName =(SELECT MIN(Customers.FirstName+', '+Customers.Surname) 
FROM Customers,CustomerReturns,Products
WHERE CustomerReturns.ReturnDate='2012/07/11'
AND Products.ProductID=CustomerReturns.ProductID
AND Customers.CustomerID=CustomerReturns.CustomerID)

DECLARE @Reason varchar(30)
SET @Reason =(SELECT MIN(CustomerReturns.Reason) 
FROM CustomerReturns,Products,Customers
WHERE CustomerReturns.Reason='Damaged Exterior')

PRINT 'FAULTY PRODUCT REPORT';
PRINT '';
PRINT '---------------------';
PRINT 'Product Name: '+@ProductName;
PRINT 'Customer Name: '+@CustomerName;
PRINT 'Reason: '+@Reason;
PRINT '';
PRINT '---END OF REPORT---';

And my output:

FAULTY PRODUCT REPORT

--------------------------------------

Product Name: Desk

Customer Name: Simon, Kernel

Reason: Damaged Exterior

---END OF REPORT---

As you can see I am still missing the:

(1 rows(s) affected)

How would i make this appear without ofcourse cheating and just printing it? (because then it will stay thay way no matter how many results get returned)

EDIT:

I found doing this: SELECT @ProductName... produces the 1row(s) affected, is this correct?:

PRINT 'FAULTY PRODUCT REPORT';
PRINT '';
PRINT '---------------------';
PRINT 'Product Name: '+@ProductName;
PRINT 'Customer Name: '+@CustomerName;
PRINT 'Reason: '+@Reason;
SELECT @ProductName,@CustomerName,@Reason
PRINT '';
PRINT '---END OF REPORT---';

EIDT2:

Changed it to this:

DECLARE @PName varchar(35)
DECLARE @CName varchar(75)
DECLARE @RName varchar(75)
SELECT @PName=Products.ProductName,
 @CName=Customers.FirstName+', '+Customers.Surname ,
 @RName=CustomerReturns.Reason

FROM Products,Customers,CustomerReturns

WHERE CustomerReturns.ReturnDate='2012/09/28'
AND CustomerReturns.Reason='Damaged Exterior'
AND Products.ProductID=CustomerReturns.ProductID
AND Customers.CustomerID=CustomerReturns.CustomerID
PRINT @PName
PRINT @CName
PRINT @RName

but still not affected rows thingy

Any help is appreciated thank you

Brian Knight · Accepted Answer · 2012-08-20 21:00:48Z

2

Take a look at using the @@ROWCOUNT function after your query. This function returns the number of rows affected by the last statement. Read more about it here.

Using your second edit above, you could do:

DECLARE @PName varchar(35) 
DECLARE @CName varchar(75) 
DECLARE @RName varchar(75)
DECLARE @rowCount int 
SELECT @PName=Products.ProductName, 
 @CName=Customers.FirstName+', '+Customers.Surname , 
 @RName=CustomerReturns.Reason 

FROM Products,Customers,CustomerReturns 

WHERE CustomerReturns.ReturnDate='2012/09/28' 
AND CustomerReturns.Reason='Damaged Exterior' 
AND Products.ProductID=CustomerReturns.ProductID 
AND Customers.CustomerID=CustomerReturns.CustomerID

SET @rowCount = @@ROWCOUNT

PRINT @PName 
PRINT @CName 
PRINT @RName 
--Use @rowCount in your report.

Note that you have to obtain the row count immediately after you execute the query. This way, no matter how many rows you get, you'll have the right number - avoiding the "cheat" scenario you mentioned.

Hope this helps.

answered Aug 20, 2012 at 21:00

Brian Knight

5,04130 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Kroukamp Over a year ago

Thank you this looks more like what i'd be needing. You the best :P

mikurski · Accepted Answer · 2012-08-20 19:48:25Z

1

SELECT, UPDATE, INSERT and DELETE all use the Messages tab to output the number of rows affected by the operation. (The NOCOUNT flag may affect this, but I haven't done any research to back this up).

You've already identified that a SELECT statement is one way for the (1 rows(s) affected) text output to appear; however, there's more than one way to generate that output.

Your current query seems to fit the majority of what the assignment asks for - this might be a good time to take it to your instructor and ask for clarification about what they want.

answered Aug 20, 2012 at 19:48

mikurski

1,3637 silver badges20 bronze badges

2 Comments

mikurski Over a year ago

As I mentioned originally: the (1 rows(s) affected) line is a standard output used by SELECT, UPDATE, INSERT, DELETE, etc. The problem is that you're working backwards from the solution text and trying to figure out how to produce the line (1 rows(s) affected). Since there's multiple ways to get that line, the only way to really resolve your question is to ask someone who's seen the original query, like your instructor.

David Kroukamp Over a year ago

Thank you I will mark this as solved/ the answer if no other answer of help comes

Collectives™ on Stack Overflow

Simple Text-Based Report MS SQL Server 2008 using PRINT

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related