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 · Answer

Hi Sunil Here you go: ---------------------------------------------- SET NOCOUNT ON DECLARE @ProductName VARCHAR(50) DECLARE @ListPrice NUMERIC(102) 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 !!

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.

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