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.

Counting Groups

I'm trying to count the # of times a group header prints, but am having
little luck. If I create a data set with :
"Declare @.StartCount int
Set @.StartCount = 1
Select @.StartCount as Start"
Then pull the sum of the field into the group header, the very first number
it starts off with is 7, not 1. If I just pull the field itself, it returns
1 every time the group header repeats itself. If I don't use the set
statement, but set the input as a parameter with a default of 1, I get 1
every time the group repeats itself.
So, I went search on how to declare a variable as a counter and found a post
titled "Line Item Count" which contains part of the information I'm looking
for, but no details. David Siebert said:
"You could use custom code (under report properties) to increment a variable
for each row."
Unfortunately, not knowing VB very well, I have no idea how to implement
this. I tried doing a Dim statement in that window, then putting a textbox
on the line in question with an expression of "=Count(Counter + 1)", but I
keep getting a "Counter not declared" error message when I try to preview.
Can anyone help me out?
Thanks in advance,
Catadmin
--
MCDBA, MCSA
Random Thoughts: I only thought I was going crazy. I forgot I was already
there.Not sure why you are doing this but... anyway.
Put this code in your code section of report properties:
Public Shared gintCnt As Integer ' This will hold the count.
Public Shared Function IncDispCnt() As String
' This function will increment count and output result.
gintCnt += 1
Return gintCnt.ToString
End Function
Public Shared Function DisplayCnt() As String
' This function will output result.
Return gintCnt.ToString
End Function
Public Shared Function IncCnt() As String
' This function will increment count.
gintCnt += 1
Return ""
End Function
Public Shared Function ResetCnt() As String
' This function will increment count.
gintCnt = 0
Return ""
End Function
I gave you a few options there. Add a textbox to the header you want to
count and place this in the expression:
=Code.IncCnt()
In the page footer add a textbox and place this in the expression:
=Code.DisplayCnt()
That should do it.
--
Message posted via http://www.sqlmonster.com|||oops, the comment for ResetCnt should be:
' This function will reset the global count.
--
Message posted via http://www.sqlmonster.com|||> Not sure why you are doing this
I'm doing this because I've got a group that repeats its headers halfway
down the page. I just want the headers at the top of the page, so I'm using
the counter to trigger the visibility property on that particular header. If
the counter > 1 on a page, then hide. Else show. Then I reset the counter
at the page footer.
Your code worked perfectly! I just could figure it out to save my life. Of
course, I wasn't looking at using functions either.
Thank you so very very very much. You taught me something valuable today.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Patrick P via SQLMonster.com" wrote:
but... anyway.
> Put this code in your code section of report properties:
> Public Shared gintCnt As Integer ' This will hold the count.
> Public Shared Function IncDispCnt() As String
> ' This function will increment count and output result.
> gintCnt += 1
> Return gintCnt.ToString
> End Function
> Public Shared Function DisplayCnt() As String
> ' This function will output result.
> Return gintCnt.ToString
> End Function
> Public Shared Function IncCnt() As String
> ' This function will increment count.
> gintCnt += 1
> Return ""
> End Function
> Public Shared Function ResetCnt() As String
> ' This function will increment count.
> gintCnt = 0
> Return ""
> End Function
> I gave you a few options there. Add a textbox to the header you want to
> count and place this in the expression:
> =Code.IncCnt()
> In the page footer add a textbox and place this in the expression:
> =Code.DisplayCnt()
> That should do it.
> --
> Message posted via http://www.sqlmonster.com
>