writing a stored procedure which makes multiple insertions-- need help
-
hi can anyone plz help me debug this procedure and tel me if there is any other software that helps us write and debug stored procs in a much convinient way I'm using SQL server 2000 by the way. and this is the error... -------------------------------------------------- Error 156: Incorrect syntax near the keyword 'SET'. --------------------------------------------------- CREATE PROCEDURE dbo.BranchBillDetailInsertCombo ( @BillID numeric(18), @Quantity int, @Serial varchar(30), @prodLineID numeric(18), @ManID numeric(18), @ModelID numeric(18), @PresentAT numeric(18), @Error_code INT OUTPUT ) AS DECLARE @counter int DECLARE @availableQty int DECLARE @PurchID numeric(18) DECLARE purch_cursor CURSOR FOR SELECT purchid FROM vu_unsoldproducts WHERE prodLineID = @prodLineID AND Manid = @Manid AND ModelID = @ModelID AND Upper(Serial) = Upper(Serial) AND presentAT = @presentAT Select @avialbleQty = (select count(*) AvailableQty FROM vu_unsoldproducts Group by serial, prodlineid, manid, modelid HAVING prodlineid = @prodLineID AND manid = @manid AND modelid = @modelID AND UPPER(serial) = UPPER(@Serial) SET @Error_code = 0 -- means there is no error in executing the stored procedure IF @Quantity >@availableQty BEGIN SET @ERROR_CODE = -1 --return -- dubious statement... I dont know the syntax for that. END ELSE BEGIN OPEN purch_cursor FETCH NEXT FROM purch_cursor INTO @PurchID Insert INTO branchBillDetail(BillID, purchID) Values (@BillID, @purchID) SET @counter = 1 WHILE (@@FETCH_STATUS = 0) OR (@Counter < @Quantity) BEGIN SET @counter = @counter + 1 -- @counter++ FETCH NEXT FROM purch_cursor INTO @PurchID Insert InTo branchBillDetail(BillID, purchID) Values (@BillID, @purchID) END CLOSE purch_cursor DEALLOCATE purch_Cursor END Rocky
-
hi can anyone plz help me debug this procedure and tel me if there is any other software that helps us write and debug stored procs in a much convinient way I'm using SQL server 2000 by the way. and this is the error... -------------------------------------------------- Error 156: Incorrect syntax near the keyword 'SET'. --------------------------------------------------- CREATE PROCEDURE dbo.BranchBillDetailInsertCombo ( @BillID numeric(18), @Quantity int, @Serial varchar(30), @prodLineID numeric(18), @ManID numeric(18), @ModelID numeric(18), @PresentAT numeric(18), @Error_code INT OUTPUT ) AS DECLARE @counter int DECLARE @availableQty int DECLARE @PurchID numeric(18) DECLARE purch_cursor CURSOR FOR SELECT purchid FROM vu_unsoldproducts WHERE prodLineID = @prodLineID AND Manid = @Manid AND ModelID = @ModelID AND Upper(Serial) = Upper(Serial) AND presentAT = @presentAT Select @avialbleQty = (select count(*) AvailableQty FROM vu_unsoldproducts Group by serial, prodlineid, manid, modelid HAVING prodlineid = @prodLineID AND manid = @manid AND modelid = @modelID AND UPPER(serial) = UPPER(@Serial) SET @Error_code = 0 -- means there is no error in executing the stored procedure IF @Quantity >@availableQty BEGIN SET @ERROR_CODE = -1 --return -- dubious statement... I dont know the syntax for that. END ELSE BEGIN OPEN purch_cursor FETCH NEXT FROM purch_cursor INTO @PurchID Insert INTO branchBillDetail(BillID, purchID) Values (@BillID, @purchID) SET @counter = 1 WHILE (@@FETCH_STATUS = 0) OR (@Counter < @Quantity) BEGIN SET @counter = @counter + 1 -- @counter++ FETCH NEXT FROM purch_cursor INTO @PurchID Insert InTo branchBillDetail(BillID, purchID) Values (@BillID, @purchID) END CLOSE purch_cursor DEALLOCATE purch_Cursor END Rocky
there has two mistake in your code: 1.missing a right bracket at the end of "Select @avialbleQty = (..." 2.the var @availableQty is not equal definition :) The code after DEBUG: CREATE PROCEDURE dbo.BranchBillDetailInsertCombo ( @BillID numeric(18), @Quantity int, @Serial varchar(30), @prodLineID numeric(18), @ManID numeric(18), @ModelID numeric(18), @PresentAT numeric(18), @Error_code INT OUTPUT ) AS DECLARE @counter int DECLARE @availableQty int DECLARE @PurchID numeric(18) DECLARE purch_cursor CURSOR FOR SELECT purchid FROM vu_unsoldproducts WHERE prodLineID = @prodLineID AND Manid = @Manid AND ModelID = @ModelID AND Upper(Serial) = Upper(Serial) AND presentAT = @presentAT Select @availableQty = (select count(*) AvailableQty FROM vu_unsoldproducts Group by serial, prodlineid, manid, modelid HAVING prodlineid = @prodLineID AND manid = @manid AND modelid = @modelID AND UPPER(serial) = UPPER(@Serial)) SET @Error_code = 0 -- means there is no error in executing the stored procedure IF @Quantity >@availableQty BEGIN SET @ERROR_CODE = -1 --return -- dubious statement... I dont know the syntax for that. END ELSE BEGIN OPEN purch_cursor FETCH NEXT FROM purch_cursor INTO @PurchID Insert INTO branchBillDetail(BillID, purchID) Values (@BillID, @purchID) SET @counter = 1 WHILE (@@FETCH_STATUS = 0) OR (@Counter < @Quantity) BEGIN SET @counter = @counter + 1 -- @counter++ FETCH NEXT FROM purch_cursor INTO @PurchID Insert InTo branchBillDetail(BillID, purchID) Values (@BillID, @purchID) END CLOSE purch_cursor DEALLOCATE purch_Cursor END
-
there has two mistake in your code: 1.missing a right bracket at the end of "Select @avialbleQty = (..." 2.the var @availableQty is not equal definition :) The code after DEBUG: CREATE PROCEDURE dbo.BranchBillDetailInsertCombo ( @BillID numeric(18), @Quantity int, @Serial varchar(30), @prodLineID numeric(18), @ManID numeric(18), @ModelID numeric(18), @PresentAT numeric(18), @Error_code INT OUTPUT ) AS DECLARE @counter int DECLARE @availableQty int DECLARE @PurchID numeric(18) DECLARE purch_cursor CURSOR FOR SELECT purchid FROM vu_unsoldproducts WHERE prodLineID = @prodLineID AND Manid = @Manid AND ModelID = @ModelID AND Upper(Serial) = Upper(Serial) AND presentAT = @presentAT Select @availableQty = (select count(*) AvailableQty FROM vu_unsoldproducts Group by serial, prodlineid, manid, modelid HAVING prodlineid = @prodLineID AND manid = @manid AND modelid = @modelID AND UPPER(serial) = UPPER(@Serial)) SET @Error_code = 0 -- means there is no error in executing the stored procedure IF @Quantity >@availableQty BEGIN SET @ERROR_CODE = -1 --return -- dubious statement... I dont know the syntax for that. END ELSE BEGIN OPEN purch_cursor FETCH NEXT FROM purch_cursor INTO @PurchID Insert INTO branchBillDetail(BillID, purchID) Values (@BillID, @purchID) SET @counter = 1 WHILE (@@FETCH_STATUS = 0) OR (@Counter < @Quantity) BEGIN SET @counter = @counter + 1 -- @counter++ FETCH NEXT FROM purch_cursor INTO @PurchID Insert InTo branchBillDetail(BillID, purchID) Values (@BillID, @purchID) END CLOSE purch_cursor DEALLOCATE purch_Cursor END
Hey thanks a lot man I really appreciate it, well its just that at the moment I'm not feeling at home with this syntax u know:~ but is there any other tool in which we can easily see our mistakes like for example .net environment helps alot in that regard. thanks again Rocky
-
Hey thanks a lot man I really appreciate it, well its just that at the moment I'm not feeling at home with this syntax u know:~ but is there any other tool in which we can easily see our mistakes like for example .net environment helps alot in that regard. thanks again Rocky
hi! I wrote sql server stored procedure since 2002,as a matter of experience,the best way to reduce bugs and errors is keep good habit for coding. for example,code with EditPlus text-editor rather than sql server query analyzer,and use the syntax file & auto-completion for *.sql file type. of course,one important factor is has clear structure of application,and then,produce high-quality code.:) there is some code of mine: /* '############################################################################## '## Copyright (C) 1998-2006 Yahao SoftWorks '## '## PROJ： FFS '## SUBJ： get common present data '## '## Code by Yahao '############################################################################## '## C/U-Date: 2006-10-09 */ -- Remove the existing Stored Procedure -- if (exists (select * from sysobjects where name = 'usp_m_getcPresentDetailData' and type = 'P')) drop proc usp_m_getcPresentDetailData go CREATE PROCEDURE usp_m_getcPresentDetailData ( ---- In: op id ---- @inCmdID smallint, ---- In: obj id ---- @inOpItemID int, ---- In: obj name ---- @inOpItemName varchar(60), ---- Out: record amount ---- @otcPresentAmount int OUTPUT, ---- Ret ---- @ReturnValue int OUTPUT ) WITH ENCRYPTION AS SET NOCOUNT ON DECLARE @intRetValue As int,@intErrCode As int DECLARE @intcPresentAmount As int IF @@NESTLEVEL = 1 BEGIN SET @ReturnValue = 99 RAISERROR ('illegal call',16, 1) RETURN END ---- var init ---- SET @intErrCode = 0 SET @intcPresentAmount = 0 ---- RS1: present data ---- SELECT PresentName, StockAmount, AvgStockPrice, OptionSet, CatID, StockStatusTID, Remark, CreateOn, Status FROM onenb_shop.dbo.CommonPresentData WHERE PresentID = @inOpItemID ---- return data ---- SET @otcPresentAmount = @intcPresentAmount SET @ReturnValue = 0 SET NOCOUNT OFF
-
Hey thanks a lot man I really appreciate it, well its just that at the moment I'm not feeling at home with this syntax u know:~ but is there any other tool in which we can easily see our mistakes like for example .net environment helps alot in that regard. thanks again Rocky
Good style: SELECT @availableQty = ( SELECT COUNT(*) As AvailableQty FROM vu_unsoldproducts GROUP BY serial, prodlineid, manid, modelid HAVING prodlineid = @prodLineID AND manid = @manid AND modelid = @modelID AND UPPER(serial) = UPPER(@Serial) ) Bad style: Select @availableQty = (select count(*) AvailableQty FROM vu_unsoldproducts Group by serial, prodlineid, manid, modelid HAVING prodlineid = @prodLineID AND manid = @manid AND modelid = @modelID AND UPPER(serial) = UPPER(@Serial))