Answer Posted / manoj kaushik

/* Same as previous example, this time using a
** cursor. Each update commits as it is made.
*/
create procedure increase_price_cursor
as
declare @price money

/* declare a cursor for the select from titles */
declare curs cursor for
select price
from titles
for update of price

/* open the cursor */
open curs

/* fetch the first row */
fetch curs into @price

/* now loop, processing all the rows
** @@sqlstatus = 0 means successful fetch
** @@sqlstatus = 1 means error on previous fetch
** @@sqlstatus = 2 means end of result set reached
*/
while (@@sqlstatus != 2)
begin
/* check for errors */
if (@@sqlstatus = 1)
begin
print "Error in increase_price"
return
end

/* next adjust the price according to the
** criteria
*/
if @price > $60
select @price = @price * 1.05
else
if @price > $30 and @price <= $60
select @price = @price * 1.10
else
if @price <= $30
select @price = @price * 1.20

/* now, update the row */
update titles
set price = @price
where current of curs

/* fetch the next row */
fetch curs into @price
end

/* close the cursor and return */
close curs
return

What is union and union all keyword in sql and what are their differences?

1085

---

What is an escape character in sql?

1008

---

Can we join same table in sql?

998

---

What is rank dense_rank and partition in sql?

1006

---

Explian rowid, rownum?

994

---

What is the non-clustered index in sql?

1048

---

When can we use the where clause and the having clause?

1009

---

What are the different ways to optimize a sql query?

928

---

Explain the savepoint statement.

1079

---

What is the trigger in sql?

929

---

What is a left inner join?

992

---

how many sql dml commands are supported by 'mysql'? : Sql dba

1097

---

Which command is used to delete a trigger?

1306

---

What is break?

1181

---

What is difference between group by and partition by?

1039

---