September 18, 2011 at 8:38 pm
Hi I have to create a while loop that returns productname, categorid and unitprice and compares the categoryid to 4. The results should print the product name, categoryid and unitprice if the categoryid is less than 4.
I am so confused as to how to write the while loop for this. Can anyone help please? This is what I came up with so far and based off of wrtiting a query to get the same results with the select statement.
While(Select productname, categoryid, unitprice
From Production.Products)
Begin
(Select productname, categoryid, unitprice
From Production.Products
Where production.products.categoryid < 4)
September 19, 2011 at 12:09 am
Hi,
What is the objective to write while loop here?
Can you elaborate it little bit more?
Shatrughna
September 19, 2011 at 12:56 am
USE AdventureWorks2008R2
GO
DECLARE @inccount int
SET @inccount = 1
While ((SELECT COUNT(*) FROM Production.Product P RIGHT JOIN Production.ProductSubcategory SC
ON SC.ProductSubcategoryID = P.ProductSubcategoryID
JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = SC.ProductCategoryID
WHERE PC.ProductCategoryID < 4)>=@inccount)
BEGIN
Declare @name varchar(100)
,@prodcatid int
,@listprice int
Select @name = Name
, @prodcatid = ProductCategoryID
, @listprice = ListPrice
from (Select ROW_NUMBER() over(order by pc.ProductCategoryID ASC)as rownumber, p.Name as Name
,PC.ProductCategoryID as ProductCategoryID ,ListPrice FROM Production.Product P RIGHT JOIN Production.ProductSubcategory SC
ON SC.ProductSubcategoryID = P.ProductSubcategoryID
JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = SC.ProductCategoryID
WHERE PC.ProductCategoryID < 4) as d
Where rownumber = @inccount
Print 'Product name = '+' '+@name+' '+'Product_category_id = '+' '+convert(varchar,@prodcatid)+' '+'Listprice = '+' '+convert(varchar,@listprice)
Set @inccount = @inccount+1
end
September 19, 2011 at 5:35 pm
Thank you for your help, I have now figured it out and understand it with your help. I appreciate it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply