create a procedure

Hello everyone, i need some help(((
i am creating a procedure as this :
--
CREATE procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
close od;
deallocate od;
--
everything is ok, the command completed successfuly.
1-but when i run the procedure by doing :
--
exec automated_mark
--
the procedure run, but there is no update.
2- when i open the microsoftsql management studio, and i do right-
click on the percedure,, execute Stored procedure : then the procedure
run, and tha table is updated. BUT when i alter my procedure, and i
change the "update products set Instock= '10000' " with the "update
products set Instock= '2222' ", and i do right-click on the
percedure,, execute Stored procedure THEN the procedure run, BUT NO
UPDATE.
why and how to solve the problem?
thank for your help.@.@.fetch_status is a global value for each connection. So you cannot depend
on @.@.fetch_status being zero when the stored proc is executed. If you have
previously looped thru a cursor with this connection, doing a
while(@.@.fetch_status=0), then when it left that loop, it was non zero, so it
is still non zero and you loop will not be executed at all. Instead of
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
you want to code the cursor loop like:
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
update products set Instock= '10000' where prodid = @.nom1;
fetch next from od into @.nom1,@.nom2
end
Then you are not depending on @.@.fetch_status being zero before your first
fetch.
The other problem with this stored proc is that it uses a cursor. Cursor's
are almost always an ineffecient way to process data in SQL. It is almost
always much better and faster to use a set based solution. In your case, a
better stored proc would look like:
CREATE procedure [dbo].[automated_mark]
with recompile
as
update products
set Instock='10000'
where prodid in (select ProdID from Orders where Orders.Qty > 20);
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:13ae24ed-37ea-4dbb-91f5-d278bb4f6b77@.d62g2000hsf.googlegroups.com...
> Hello everyone, i need some help(((
> i am creating a procedure as this :
> --
> CREATE procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> while (@.@.fetch_status=0)
> begin
> fetch next from od into @.nom1,@.nom2
> update products set Instock= '10000' where prodid = @.nom1;
> end
> close od;
> deallocate od;
> --
> everything is ok, the command completed successfuly.
> 1-but when i run the procedure by doing :
> --
> exec automated_mark
> --
> the procedure run, but there is no update.
> 2- when i open the microsoftsql management studio, and i do right-
> click on the percedure,, execute Stored procedure : then the procedure
> run, and tha table is updated. BUT when i alter my procedure, and i
> change the "update products set Instock= '10000' " with the "update
> products set Instock= '2222' ", and i do right-click on the
> percedure,, execute Stored procedure THEN the procedure run, BUT NO
> UPDATE.
> why and how to solve the problem?
> thank for your help.|||thank you very much Tom.
its now working.thanks.
Now, about the set based solution, i agree with you when i have an
update to do.
But, what if instead an update i have an insert to do, like this :
--
ALTER procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
fetch next from od into @.nom1,@.nom2
end
close od;
deallocate od;
--
is there a better way to do that?
thanks again.|||On Feb 29, 4:14=A0am, noumian <n.nou...@.gmail.com> wrote:
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> =A0 =A0 =A0 =A0 for select Orders.ProdID,Orders.Qty from Orders where Orde=rs.Qty > 20
> open od
> =A0 =A0 =A0 =A0 fetch next from od into @.nom1,@.nom2
> =A0 =A0 =A0 =A0 while (@.@.fetch_status=3D0)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 begin
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 insert into sales (prodid,=qty,action) values (@.nom1,@.nom2,'sold');
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 fetch next from od into @.n=om1,@.nom2
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.
What about:
INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
dbo.Orders WHERE Orders.Quantity > 20|||insert into sales (prodid,qty,action)
select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> fetch next from od into @.nom1,@.nom2
> while (@.@.fetch_status=0)
> begin
> insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> fetch next from od into @.nom1,@.nom2
> end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.|||On Feb 29, 4:39 pm, Sean <ColdFusion...@.gmail.com> wrote:
> On Feb 29, 4:14 am, noumian <n.nou...@.gmail.com> wrote:
>
> > thank you very much Tom.
> > its now working.thanks.
> > Now, about the set based solution, i agree with you when i have an
> > update to do.
> > But, what if instead an update i have an insert to do, like this :
> > --
> > ALTER procedure [dbo].[automated_mark]
> > with recompile
> > as
> > declare @.nom1 nvarchar(50)
> > declare @.nom2 nvarchar(50)
> > declare od cursor
> > for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> > open od
> > fetch next from od into @.nom1,@.nom2
> > while (@.@.fetch_status=0)
> > begin
> > insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> > fetch next from od into @.nom1,@.nom2
> > end
> > close od;
> > deallocate od;
> > --
> > is there a better way to do that?
> > thanks again.
> What about:
> INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
> dbo.Orders WHERE Orders.Quantity > 20
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?|||On Feb 29, 5:56 pm, "Tom Cooper"
<tomcoo...@.comcast.no.spam.please.net> wrote:
> insert into sales (prodid,qty,action)
> select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
> Tom
> "noumian" <n.nou...@.gmail.com> wrote in message
> news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
> > thank you very much Tom.
> > its now working.thanks.
> > Now, about the set based solution, i agree with you when i have an
> > update to do.
> > But, what if instead an update i have an insert to do, like this :
> > --
> > ALTER procedure [dbo].[automated_mark]
> > with recompile
> > as
> > declare @.nom1 nvarchar(50)
> > declare @.nom2 nvarchar(50)
> > declare od cursor
> > for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> > open od
> > fetch next from od into @.nom1,@.nom2
> > while (@.@.fetch_status=0)
> > begin
> > insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> > fetch next from od into @.nom1,@.nom2
> > end
> > close od;
> > deallocate od;
> > --
> > is there a better way to do that?
> > thanks again.
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?|||On Feb 29, 10:22=A0am, noumian <n.nou...@.gmail.com> wrote:
> On Feb 29, 5:56 pm, "Tom Cooper"
>
>
> <tomcoo...@.comcast.no.spam.please.net> wrote:
> > insert into sales (prodid,qty,action)
> > select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 2=0;
> > Tom
> > "noumian" <n.nou...@.gmail.com> wrote in message
> >news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...=
> > > thank you very much Tom.
> > > its now working.thanks.
> > > Now, about the set based solution, i agree with you when i have an
> > > update to do.
> > > But, what if instead an update i have an insert to do, like this :
> > > --
> > > ALTER procedure [dbo].[automated_mark]
> > > with recompile
> > > as
> > > declare @.nom1 nvarchar(50)
> > > declare @.nom2 nvarchar(50)
> > > declare od cursor
> > > for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> > > open od
> > > fetch next from od into @.nom1,@.nom2
> > > while (@.@.fetch_status=3D0)
> > > begin
> > > insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> > > fetch next from od into @.nom1,@.nom2
> > > end
> > > close od;
> > > deallocate od;
> > > --
> > > is there a better way to do that?
> > > thanks again.
> CHECK MY insert: =A0 insert into sales (prodid,qty,action) values
> (@.nom1,@.nom2,'sold');
> what about the values of my column action... Hide quoted text -
> - Show quoted text -
Argh, sorry it's friday... a very long... friday.
INSERT INTO Sales(prodid, qty, action) SELECT prodid, qty, 'Sold' as
Action FROM Orders WHERE qty > 20
That should do the trick.|||ok, thanks very much.