TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Nathan
NA
24
15.4k
If statement in SQL Server not working
Feb 17 2014 2:35 AM
Dear Sir,
I am in try to set a condition in my sql query. I want to execute this query if satisfy two conditions. Please give the condition to be set in the form.cs also to this query. Here the query:-
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Delete_New_invPurchase](
@as_ID bigint,
@upduserid nvarchar(20),
@modidate datetime,
@as_preDeliveryStatus bit,
@as_isAudited
bit
)
AS
BEGIN
IF @as_preDeliveryStatus=1 AND @as_isAudited=0
--will this condition work?, if not please correct to the correct syntax
BEGIN
DECLARE
@rowCount INT,
@i int,
@PID bigint,
@batchno nvarchar(20),
@STOCKLTYPE smallint,
@STOCKGID int,
@STOCK float,
@QTY float,
@FREEQTY float,
@STOCKcompcode nvarchar(3),
@STOCKdivcode nvarchar(3),
@STOCKyearcode nvarchar(5)
DECLARE
@tempTable table
(
RowID int not null primary key identity(1,1),
PID bigint,
batchno nvarchar(20),
LTYPE smallint,
GID int,
STOCK float,
QTY float,
FREEQTY float,
compcode nvarchar(3),
divcode nvarchar(3),
yearcode nvarchar(5)
)
INSERT INTO @tempTable(PID, batchno, QTY, FREEQTY, LTYPE, STOCK, GID, compcode, divcode, yearcode)
SELECT
INVPURCHASEITEMS.PID,
INVPURCHASEITEMS.batchno,
INVPURCHASEITEMS.qty,
INVPURCHASEITEMS.FREEQTY,
INVSTOCK.LTYPE,
INVSTOCK.QTY AS STOCK,
INVSTOCK.GID,
INVSTOCK.Compcode,
INVSTOCK.DivCode,
INVSTOCK.Yearcode
FROM INVPURCHASEITEMS
INNER JOIN INVSTOCK ON INVPURCHASEITEMS.PID = INVSTOCK.PID
INNER JOIN INVPURCHASE ON INVPURCHASE.ID = INVPURCHASEITEMS.PURCHASEID
WHERE INVPURCHASEITEMS.PURCHASEID = @as_ID AND INVPURCHASEITEMS.isdeleted = 'False'
AND INVPURCHASE.LTYPE = INVSTOCK.LTYPE AND INVPURCHASE.YEARCODE = INVSTOCK.YEARCODE
AND INVPURCHASE.GID = INVSTOCK.GID AND INVPURCHASEITEMS.batchno = INVSTOCK.batchno
SET @rowCount = @@ROWCOUNT
SET @i = 0
WHILE (@i < @rowCount)
BEGIN
SET @i=@i+1
SELECT
@PID = PID,
@batchno = batchno,
@STOCKLTYPE = LTYPE,
@STOCKGID = GID,
@STOCK = STOCK,
@QTY = QTY,
@FREEQTY = FREEQTY,
@STOCKcompcode = compcode,
@STOCKdivcode = divcode,
@STOCKyearcode = yearcode
FROM @tempTable
WHERE RowID=@i
IF (@STOCK - (@QTY + @FREEQTY)) > 0
BEGIN
UPDATE INVSTOCK SET qty = @STOCK - (@QTY + @FREEQTY)
WHERE PID = @PID AND Batchno = @batchno AND LTYPE = @STOCKLTYPE AND GID = @STOCKGID
AND Compcode = @STOCKcompcode AND DivCode = @STOCKdivcode AND Yearcode = @STOCKyearcode
END
ELSE
BEGIN
UPDATE INVSTOCK SET qty = 0
WHERE PID = @PID AND Batchno = @batchno AND LTYPE = @STOCKLTYPE AND GID = @STOCKGID
AND Compcode = @STOCKcompcode AND DivCode = @STOCKdivcode AND Yearcode = @STOCKyearcode
END
END
END
UPDATE INVPURCHASE SET isdeleted = 'True', upduserid = @upduserid, modidate = getdate()
WHERE ID = @as_ID
UPDATE INVPURCHASEITEMS SET isdeleted = 'True'
WHERE PURCHASEID = @as_ID
END
Reply
Answers (
4
)
SQL Database is corrupted....... plz help.
how to find overtime from sql query