how to update in storeprocedure

Question

USE [Agri]

GO

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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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 );

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 .

venkata kumar · Answer

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 Trivedi · Answer

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 subramanian · Answer

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 kumar · Answer

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 (ProductNameDateBillNoCustomerNameAddressBrandName QuantityProductPriceRequirednoPriceVatTotalAmounttype) values(@Product@Date@BillNo @CustomerName @Address @BrandName  @Quantity @ProductPrice @Requiredno @Price @Vat @TotalAmount@type); insert into samle (ProductNameDateBillNoCustomerNameAddressBrandName QuantityProductPriceRequirednoPriceExtraVatCorrectPricetype) 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 subramanian · Answer

CREATE TABLE [dbo].[sampl]( [cid] [int] IDENTITY(11) 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(11) 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 samplsamle. in pr table purno is 100. in samplsamle the required no is 50. i need in or table (100-50=50)

Jignesh Trivedi · Answer

hi In SQL null + 50 = null i think you have same problem... try update pr set purno= isnull(purno0)-isnull(Requiredno0) where BrandName=@BrandName hope this will help you.

venkata kumar · Answer

send mee procedure and table structure. give mee proper details

selvi subramanian · Answer

its not working please check it ...in update it never reduce the purno..

venkata kumar · Answer

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 (ProductNameDateBillNoCustomerNameAddressBrandName QuantityProductPriceRequirednoPriceVatTotalAmounttype) values(@Product@Date@BillNo @CustomerName @Address @BrandName  @Quantity @ProductPrice @Requiredno @Price @Vat @TotalAmount@type); insert into samle (ProductNameDateBillNoCustomerNameAddressBrandName QuantityProductPriceRequirednoPriceExtraVatCorrectPricetype) 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