February 22, 2015 at 4:51 am
here i am having table proudct
CREATE TABLE Products
(
ProductID int identity(0,1),
ProductName varchar(255),
Stock int,
Active int
)
which is having data like this
Productid productname stock active
1 margo 20 1
2 ser 30 1
3 sera 50 1
4 vire 45 1
5 closeup 32 1
how i am just trying strore procedure to update all stock column which i need in a single query
productidstock
1 50
3 70
5 100
GO
CREATE PROCEDURE productUpdate
@ProductId nvarchar(50)=(1,3,5),
@Stock nvarchar(50) =(50,70,100)
AS
SET NOCOUNT ON;
UPDATE product
SET
Stock=@Stock
WHERE
ProductId=@ProductId;
February 22, 2015 at 6:03 am
You can only update multiple rows in a single statement if they have matching criteria. For example, there's a ShipByDate and they all have the same value, then the WHERE clause can be used to modify them. But, they can only be updated to a single value in a single statement.
In your example, you have three disparate rows with three disparate values. You will have to have three different update statements, one for each.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 22, 2015 at 11:49 pm
Create a new Table Type with Columns ProductId and Stock.
Pass this table type as parameter to the SP.
Then join your main table and this parameter table to update your main table.
____________________________________________________________
APViewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply