HI all
From my order table i want to retrieve one customer all orders at a time.I dont want to retrieve other orders.I am not passing any parameter to procedure.
Below giving my Order table structure
From above table we can see in userd id column 1 and 2 User ordered the item. When i execute procedure i want to get all orders by User 1 should retrieve and changing all his orderstatus from 0 to 1.when i execute second time i want to retrieve user2 orders.
How can i achieve this?
When i execute procedure i want to get all orders by User 1 should retrieve and changing all his orderstatus from 0 to 1.
These two should be really easy to implement, but not with SP without any parameter.
binustrat
when i execute second time i want to retrieve user2 orders.
I'm not sure, but i don't think SP is stateful or it can know how many times it was executed.
binustrat
How can i achieve this?
(I won't say this is impossible, but i really suggest you could change the desgin though.)
For the current requirement, i think you can add a table to let your SP has memory so that the "execute second time" can be meaningful. (To be honest, I think this is unreliable.)
Please refer to below code:
create table [order](Id int identity(3,1),OrderDate date,DeliveryDate date,UserId int,ItemId int,Qty int,OrderStatus int,DeliveredBy varchar(10),IsPackedBy int)
insert into [order] values
('2020-06-16',null,1,1,2,0,null,0),
('2020-06-16',null,1,2,2,0,null,0),
('2020-06-16',null,1,3,4,0,null,0),
('2020-06-16',null,2,2,3,0,null,0),
('2020-06-16',null,2,3,4,0,null,0),
('2020-06-16',null,2,4,2,0,null,0)
select * from [order]
create table SPMemory(Id int identity(1,1),Memory varchar(10))
select COUNT(*) from SPMemory
go;
create proc StateProc
as
update [order] set OrderStatus=1 where UserId = ((select COUNT(*) from SPMemory) + 1);
select * from [order] where UserId = ((select COUNT(*) from SPMemory) + 1);
insert into SPMemory values(' ')
exec StateProc
drop table [order]
drop table SPMemory
drop proc StateProc
The usual option is to just pass the UserId value to your SP so it can be used in the WHERE clause to select those rows.
Not directly related but this is the only table you have ? Usually you have a table for each order and a table for its items.
Edit: or you mean the SP should return all rows with the 0 status for the user with the oldest command? What happens if this user ordered something few days agao and then just few hours ago ?
Member
35 Points
156 Posts
Rerieve one user all rows from table
Jun 16, 2020 05:22 AM|binustrat|LINK
HI all
From my order table i want to retrieve one customer all orders at a time.I dont want to retrieve other orders.I am not passing any parameter to procedure.
Below giving my Order table structure
Id OrderDate DeliveryDate UserId ItemId Qty OrderStatus DeliveredBy IsPackedBy
3 2020-06-16 NULL 1 1 2 0 NULL 0
4 2020-06-16 NULL 1 2 2 0 NULL 0
5 2020-06-16 NULL 1 3 4 0 NULL 0
6 2020-06-16 NULL 2 2 3 0 NULL 0
7 2020-06-16 NULL 2 3 4 0 NULL 0
8 2020-06-16 NULL 2 4 2 0 NULL 0
From above table we can see in userd id column 1 and 2 User ordered the item. When i execute procedure i want to get all orders by User 1 should retrieve and changing all his orderstatus from 0 to 1.when i execute second time i want to retrieve user2 orders. How can i achieve this?
Contributor
3140 Points
983 Posts
Re: Rerieve one user all rows from table
Jun 16, 2020 06:55 AM|Yang Shen|LINK
Hi binustrat,
These two should be really easy to implement, but not with SP without any parameter.
I'm not sure, but i don't think SP is stateful or it can know how many times it was executed.
(I won't say this is impossible, but i really suggest you could change the desgin though.)
For the current requirement, i think you can add a table to let your SP has memory so that the "execute second time" can be meaningful. (To be honest, I think this is unreliable.)
Please refer to below code:
create table [order](Id int identity(3,1),OrderDate date,DeliveryDate date,UserId int,ItemId int,Qty int,OrderStatus int,DeliveredBy varchar(10),IsPackedBy int) insert into [order] values ('2020-06-16',null,1,1,2,0,null,0), ('2020-06-16',null,1,2,2,0,null,0), ('2020-06-16',null,1,3,4,0,null,0), ('2020-06-16',null,2,2,3,0,null,0), ('2020-06-16',null,2,3,4,0,null,0), ('2020-06-16',null,2,4,2,0,null,0) select * from [order] create table SPMemory(Id int identity(1,1),Memory varchar(10)) select COUNT(*) from SPMemory go; create proc StateProc as update [order] set OrderStatus=1 where UserId = ((select COUNT(*) from SPMemory) + 1); select * from [order] where UserId = ((select COUNT(*) from SPMemory) + 1); insert into SPMemory values(' ') exec StateProc drop table [order] drop table SPMemory drop proc StateProcBest Regard,
Yang Shen
All-Star
48510 Points
18071 Posts
Re: Rerieve one user all rows from table
Jun 16, 2020 07:03 AM|PatriceSc|LINK
Hi,
The usual option is to just pass the UserId value to your SP so it can be used in the WHERE clause to select those rows.
Not directly related but this is the only table you have ? Usually you have a table for each order and a table for its items.
Edit: or you mean the SP should return all rows with the 0 status for the user with the oldest command? What happens if this user ordered something few days agao and then just few hours ago ?
All-Star
123252 Points
10024 Posts
Moderator
Re: Rerieve one user all rows from table
Jun 16, 2020 02:57 PM|limno|LINK
You need to pass parameter @UserId to update your table in the stored procedure.
Update yourtable
Set OrderStatus=1
Where Userid=@UserId
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm