|
This is exactly what I'm looking for, thanks very much for the help. It makes a lot of sense, but it's not something I would have thought of.
Thanks again!
- Aaron
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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))
|
|
|
|
|
hi
well I just need to build a stored proc to make a lot of insertions at one place for the user, the thing is that user can give the required quantity and the data would get inserting by matching the number of rows equal to taht of this quantity (it ould already be chked that the number is less or equal to existing Quantity or not). so the data gets inserted into this other table.
I think may be I'd need to have cursors here coz I need to access each row of the extracted data indivually. But I'm not too fond of using them though.
but even if I have to use them can u plz give me a sample code for that in TSQL
the tables are as follows
BranchBillDetail(purchID, billid) <--insertion in this
vu_unsoldproducts(purchID, modelid, prodlineid,manid, etc...) searching done on this
thanks in advance
Rocky
|
|
|
|
|
--This should solve your problems if I understand your request.
--How to do it w/o a cursor
DECLARE @RowCount INT
DECLARE @DataTable TABLE(
RowNum INT IDENTITY(1,1)
DataColumn1
,DataColumn2
,DataColumn3
ETC
)
SELECT @RowCount = Count(*) FROM [Data Source]
WHILE @RowCount > 0
BEGIN
INSERT YADAYADA
WHERE RowNum = @RowCount
SET @RowCount = @RowCount - 1
END
--Let me know if this helps
|
|
|
|
|
yea it has given me some idea to move on u know. I'm a bit out of touch with that project for a few days now and I'll certainly look into it again to get the job done..
thanks a lot
I really appreciate it!
Rocky
|
|
|
|
|
Is it possible to write an INSERT statement to add a row to a table with just a single column which is an identity column ?
e.g
Create Table Amit (Id int Identity(1,1))
Then what would be my insert statement.
Note:
Please note i don't want to use set auto_identity amit On 
Plase reply....
Amit
|
|
|
|
|
INSERT INTO Amit DEFAULT VALUES
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi I have a MS Access Database locked with password. However i found its password. But Whenever i open it i see the encrypted data. But when i open it in Wordpad it display the Encrypted data with Decrypted data. Please tell me where is the Decrypted data is located in in MDB file. Or is there any possibility to hide the data.
|
|
|
|
|
use "Access Password Recovery" soft can reset your passowrd,and then enjoy it!
to get soft,use the key "ms access password" search google.com
|
|
|
|
|
i have table contain tow date getdate and expiredDate how can i get the deferent between thes dates
|
|
|
|
|
well if u r using Oracle 9i u can simply subtract the two dates to get the number of days b/w them.
and in SQL Server I think there are some functions like DateDiff()
go chk it out urself... u can use the books online given in the sql server menu. and search over these type of contents or use index or whatever u know what I mean!
Hope it helps...
Rocky
|
|
|
|
|
Hi friends,
Please tell me what is Connection Pooling and Object Pooling.
Thanks in advance.
Thanks and Best Regards,
R. Sangeetha Priya
Prya
|
|
|
|
|
I am analyzing some access database queries and came across this...
UPDATE BCQOH <br />
LEFT JOIN BCQOH_w_Cost ON BCQOH.[2ndItemNumber] = BCQOH_w_Cost.[2ndItemNumber] <br />
SET BCQOH.Cost = [BCQOH_w_Cost]![Cost];
Is the exclamation mean not equal? If so is this just an easier way to write it than a WHERE table1.field NOT EQUAL to table2.field?
|
|
|
|
|
Unless BCQOH.Cost is a boolean field, I would think that its just another form of the '.' operator. In Access the notation used is [table]![field].
Just guessing...
>>>-----> MikeO
|
|
|
|
|
I was playing with SQL the other day and ran across a way to automatically create XML Documents with just T-SQL and Built in funtionality of SQL Server 2005. It goes like this;
SELECT * FROM [Table]
FOR XML AUTO, ELEMENTS
Then you click on the results. A new window opens with your Schema / Data in place.
I thought it was cool, and I hope it helps someone.
|
|
|
|
|
Hi,
I have posted a couple of questions on the forum this week, and no one has been able to help me the way I need help.
I have a database on my local machine, I need to take that exact database and upload it to the hosting server, without loosing any data or PK and FK relations, and I need the Identity Specification to remain the way that I set it.
If I import it to the hosting server then all the Identity Specifications are set to No again. I get huge errors when trying to import the data from my local ASP Membership tables to the hosting servers tables. Because of all the PK and FK constraints.
Please can some one help. I am really frustrated.
I am using SQL Server 2005.
Regards
ma se
|
|
|
|
|
Use Backup & Restore. You will have to establish security since you are changing domain/workstation. See BOL.
|
|
|
|
|
Hello c'pians,
I have got two SQL databases on seperate machines (master/slave). I am able to connect to both the databases using Enterprise Manager. Both databases
have identical tables. What i would like to do is write a trigger which fires when a new row is inserted into the master db and inserts the same data into the slave database.
What i would like to know is, is it possible to write a trigger which can insert data into a completly seperate database?
|
|
|
|
|
It is possible and fairly straight forward. I am doing this from memory as I only have SQL Server 2005 in front of me (so forgive if directions are slightly off). Open Enterprise Manager, create a linked server on the master DB to the slave DB. Then you will be able to access the slave DB in your trigger basically as if you were acessig another (local) DB. I hope this helps.
|
|
|
|
|
Yes, use linked server. Steps would be
a) Create Linked server for your slave on master server by using enterprise mgr or sp_addlinkedserver.
b) Write Trigger on master table...and do somothing like as below
Insert into Slave.Table values(..............)
where slave is linked server name.....
Simple........
Amit
|
|
|
|
|
hey guys... cheers ever so much for this.. I will be looking into this first thing monday morning.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
