how to update in storeprocedure

selvi subramanian

13y1.6k

USE [Agri]

/****** Object: StoredProcedure [dbo].[in_dm] Script Date: 09/14/2013 11:16:44 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

create PROCEDURE [dbo].[idm]

(@Product varchar(15),@Date DateTime ,@BillNo varchar(30) ,

@CustomerName varchar(50),@Address varchar(50) ,

@BrandName varchar(50) ,@Quantity varchar(20),

@ProductPrice varchar(20) , @Requiredno varchar(50),

@Price varchar(50), @Extra varchar(20) ,

@Vat varchar(50),@TotalAmount varchar(50),

@CorrectPrice varchar(50),@type varchar(10)

)

BEGIN

insert into sampl (ProductName,Date,BillNo,CustomerName,Address,BrandName,

Quantity,ProductPrice,Requiredno,Price,Vat,TotalAmount,type)

values(@Product,@Date,@BillNo ,@CustomerName ,@Address ,@BrandName ,

@Quantity ,@ProductPrice ,@Requiredno ,@Price ,@Vat ,@TotalAmount,@type);

insert into samle (ProductName,Date,BillNo,CustomerName,Address,BrandName,

Quantity,ProductPrice,Requiredno,Price,Extra,Vat,CorrectPrice,type) values

(@Product ,@Date,@BillNo ,@CustomerName ,@Address ,@BrandName ,@Quantity ,

@ProductPrice ,@Requiredno ,@Price ,@Extra ,

@Vat,@CorrectPrice,@type );

END

if @type='s'

begin

update pr set purno=purno-Requiredno where BrandName=@BrandName

end

===================================================================

need to update the table pr reduce the requiredno from purno (but requiredno is not inn the table pr) the required no is in the table sampl and samle .

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

venkata kumar13y ago
hi
try the fallowing

declare @purno int
set @purno=(select top 1 purno from pr where brandname=@brandname)
declare @Reqno int
set @reqno=(select top 1 requiredno from samp where brandname=@brandname)
Jignesh Trivedi13y ago
hi,

I think, problem with these two line
declare @purno int
set @purno=(select purno from samp where brandname=@brandname)
declare @Reqno int
set @reqno=(select requiredno from samp where brandname=@brandname)
Instead of this try
declare @purno int
Select @purno = purno from samp where brandname=@brandname
declare @Reqno int
select @Reqno =requiredno from samp where brandname=@brandname
hope this will help you.
selvi subramanian13y ago
hi sir Jignesh Trivedi it throw this error.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.(but it is inserted)
venkata kumar13y ago
hi
try this

create PROCEDURE [dbo].[idm]
(@Product varchar(15),@Date DateTime ,@BillNo varchar(30) ,
@CustomerName varchar(50),@Address varchar(50) ,
@BrandName varchar(50) ,@Quantity varchar(20),
@ProductPrice varchar(20) ,@Requiredno varchar(50),
@Price varchar(50),@Extra varchar(20) ,
@Vat varchar(50),@TotalAmount varchar(50),
@CorrectPrice varchar(50),@type varchar(10)
)
AS
BEGIN
insert into sampl (ProductName,Date,BillNo,CustomerName,Address,BrandName,
Quantity,ProductPrice,Requiredno,Price,Vat,TotalAmount,type)
values(@Product,@Date,@BillNo ,@CustomerName ,@Address ,@BrandName ,
@Quantity ,@ProductPrice ,@Requiredno ,@Price ,@Vat ,@TotalAmount,@type);

insert into samle (ProductName,Date,BillNo,CustomerName,Address,BrandName,
Quantity,ProductPrice,Requiredno,Price,Extra,Vat,CorrectPrice,type) values
(@Product ,@Date,@BillNo ,@CustomerName ,@Address ,@BrandName ,@Quantity ,
@ProductPrice ,@Requiredno ,@Price ,@Extra ,
@Vat,@CorrectPrice,@type );

if @type='s'
begin
declare @purno int
set @purno=(select purno from pr where brandname=@brandname)
declare @Reqno int
set @reqno=(select requiredno from samp where brandname=@brandname)
update pr set purno=@purno-@Reqno where BrandName=@BrandName
end
END
selvi subramanian13y ago
CREATE TABLE [dbo].[sampl](
[cid] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](max) NULL,
[Date] [date] NULL,
[BillNo] [varchar](30) NULL,
[CustomerName] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[BrandName] [varchar](50) NULL,
[Quantity] [varchar](20) NULL,
[ProductPrice] [varchar](20) NULL,
[Requiredno] [varchar](50) NULL,
[Price] [varchar](59) NULL,
[Vat] [varchar](50) NULL,
[TotalAmount] [float] NULL,
[type] [varchar](10) NULL
)

next table

CREATE TABLE [dbo].[samle](
[pno] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](max) NULL,
[Date] [varchar](15) NULL,
[BillNo] [varchar](50) NULL,
[CustomerName] [varchar](50) NULL,
[Address] [varchar](max) NULL,
[BrandName] [varchar](50) NULL,
[Quantity] [varchar](50) NULL,
[ProductPrice] [varchar](50) NULL,
[Requiredno] [varchar](50) NULL,
[Price] [varchar](50) NULL,
[Extra] [varchar](50) NULL,
[Vat] [varchar](50) NULL,
[CorrectPrice] [varchar](50) NULL
)

pr table

CREATE TABLE [dbo].[pr](
[productname] [varchar](30) NULL,
[brandname] [varchar](30) NULL,
[weight] [varchar](30) NULL,
[price] [float] NULL,
[purno] [int] NULL,
[type] [varchar](30) NULL
)

procedure is above . need to reduce from the pr table if inserted in sampl,samle.

in pr table purno is 100. in sampl,samle the required no is 50. i need in or table (100-50=50)
Jignesh Trivedi13y ago
hi,
In SQL,
null + 50 = null
i think you have same problem...
try
update pr set purno= isnull(purno,0)-isnull(Requiredno,0) where BrandName=@BrandName
hope this will help you.
venkata kumar13y ago
send mee procedure and table structure.
give mee proper details
selvi subramanian13y ago
its not working please check it ...in update it never reduce the purno..
venkata kumar13y ago
hi
try fallowing
create PROCEDURE [dbo].[idm]
(@Product varchar(15),@Date DateTime ,@BillNo varchar(30) ,
@CustomerName varchar(50),@Address varchar(50) ,
@BrandName varchar(50) ,@Quantity varchar(20),
@ProductPrice varchar(20) ,@Requiredno varchar(50),
@Price varchar(50),@Extra varchar(20) ,
@Vat varchar(50),@TotalAmount varchar(50),
@CorrectPrice varchar(50),@type varchar(10)
)
AS
BEGIN
insert into sampl (ProductName,Date,BillNo,CustomerName,Address,BrandName,
Quantity,ProductPrice,Requiredno,Price,Vat,TotalAmount,type)
values(@Product,@Date,@BillNo ,@CustomerName ,@Address ,@BrandName ,
@Quantity ,@ProductPrice ,@Requiredno ,@Price ,@Vat ,@TotalAmount,@type);

insert into samle (ProductName,Date,BillNo,CustomerName,Address,BrandName,
Quantity,ProductPrice,Requiredno,Price,Extra,Vat,CorrectPrice,type) values
(@Product ,@Date,@BillNo ,@CustomerName ,@Address ,@BrandName ,@Quantity ,
@ProductPrice ,@Requiredno ,@Price ,@Extra ,
@Vat,@CorrectPrice,@type );

if @type='s'
begin
declare @purno int
set @purno=(select purno from samp where brandname=@brandname)
declare @Reqno int
set @reqno=(select requiredno from samp where brandname=@brandname)
update pr set purno=@purno-@Reqno where BrandName=@BrandName
end
END