basic cursor help ?

Question

Hi
Write a script that creates a cursor for a result set that consists of the ProductName and ListPrice columns for each product with a list price that's greater than $700. The rows in this result set should be sorted in descending sequence by list price. Then, the script should print the product name and list price for each product so it looks something like this: Gibson SG, $2517.00 Gibson Les Paul, $1199.00
Thank you

Sunny Sharma · Accepted Answer

Hi Sunil,
Here you go:
----------------------------------------------
SET NOCOUNT ON DECLARE @ProductName VARCHAR(50) DECLARE @ListPrice NUMERIC(10,2) DECLARE PRODUCTS_CUR CURSOR STATIC FOR  SELECT ProductName, ListPrice FROM PRODUCTS WHERE ListPrice>700 ORDER BY ListPrice DESC OPEN PRODUCTS_CUR IF @@CURSOR_ROWS > 0  BEGIN   FETCH NEXT FROM PRODUCTS_CUR INTO @ProductName,@ListPrice  WHILE @@Fetch_status = 0  BEGIN  PRINT @ProductName +', $'+CONVERT(VARCHAR,@ListPrice)  FETCH NEXT FROM PRODUCTS_CUR INTO @ProductName,@ListPrice  END END CLOSE PRODUCTS_CUR DEALLOCATE PRODUCTS_CUR SET NOCOUNT OFF  ---------------------------------------------------
PRODUCTS is the name of the referenced table.
Cheers !!

SUNIL GUTTA · Answer

yeah thanks guys u made my day :) cheers both of you .. further doubts will be back at you guys ur the best

Jignesh Trivedi · Answer

hi sunil,
I think this can be achieved with out cursor.... as u know cursor is costly in term of memory consumption..
like
SELECT ProductName + ' $' + Cast(ListPrice AS VARCHAR(20)) AS Result FROM PRODUCTS WHERE ListPrice>700 ORDER BY ListPrice DESC
hope this will help you.

Sunny Sharma · Answer

Let me know if it works or not.