Last post Jun 16, 2020 02:57 PM by limno
Jun 16, 2020 05:22 AM|binustrat|LINK
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?
Jun 16, 2020 06:55 AM|Yang Shen|LINK
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.
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.
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
select * from [order]
create table SPMemory(Id int identity(1,1),Memory varchar(10))
select COUNT(*) from SPMemory
create proc StateProc
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(' ')
drop table [order]
drop table SPMemory
drop proc StateProc
Jun 16, 2020 07:03 AM|PatriceSc|LINK
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 ?
Jun 16, 2020 02:57 PM|limno|LINK
You need to pass parameter @UserId to update your table in the stored procedure.