can not insert record using stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

can not insert record using stored procedures

Author

Topic

jim65
Starting Member

35 Posts

Posted - 2006-03-17 : 13:49:18

I have a stored procedures to insert data, but it doesn't work. looks like loop has a problem. does someone have a idea. Thanks

CREATE PROCEDURE dbo.asp_Insert_ALL_BorrowerfromCDB (@UserID varchar(7)) AS

set nocount on

DECLARE @RowCnt INT
DECLARE @CustNum bigINT

SELECT TOP 1 @CustNum = CDB.dbo.tblBusLineHierarchy.PrimOff
FROM dbo.adBorrower RIGHT OUTER JOIN
CDB.dbo.tblCustomer INNER JOIN
CDB.dbo.tblBusLineHierarchy ON CDB.dbo.tblCustomer.PrimOff = CDB.dbo.tblBusLineHierarchy.PrimOff ON
dbo.adBorrower.CustNum = CDB.dbo.tblCustomer.CustNum
WHERE dbo.adBorrower.LenderEnumber = @UserID and (dbo.adBorrower.BorrowerID IS NULL)

ORDER BY CDB.dbo.tblCustomer.CustNum

SET @RowCnt = @@ROWCOUNT
--print @RowCnt
WHILE @RowCnt <> 0
BEGIN

INSERT INTO adBorrower
(BorrowerName, BorrowerMainAdress, BorrowerZip, ClosingDate, LenderEnumber, ProposedTCC, BankID, CustNum)
SELECT CustNam, CustAddr, CustZip, EstablishDt, @UserID, NetExposure, BankNum, dbo.adBorrower.CustNum
FROM dbo.adBorrower RIGHT OUTER JOIN
CDB.dbo.tblCustomer INNER JOIN
CDB.dbo.tblBusLineHierarchy ON CDB.dbo.tblCustomer.PrimOff = CDB.dbo.tblBusLineHierarchy.PrimOff ON
dbo.adBorrower.CustNum = CDB.dbo.tblCustomer.CustNum
WHERE dbo.adBorrower.LenderEnumber = @UserID

/* loop */

SELECT TOP 1 @CustNum = CDB.dbo.tblBusLineHierarchy.PrimOff
FROM dbo.adBorrower RIGHT OUTER JOIN
CDB.dbo.tblCustomer INNER JOIN
CDB.dbo.tblBusLineHierarchy ON CDB.dbo.tblCustomer.PrimOff = CDB.dbo.tblBusLineHierarchy.PrimOff ON
dbo.adBorrower.CustNum = CDB.dbo.tblCustomer.CustNum
WHERE (dbo.adBorrower.LenderEnumber = @UserID) and (CDB.dbo.tblBusLineHierarchy.PrimOff > @CustNum) and (dbo.adBorrower.BorrowerID IS NULL)

ORDER BY CDB.dbo.tblCustomer.CustNum

SET @RowCnt = @@ROWCOUNT

END
GO

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-17 : 14:06:30

U want to insert only or u want to do more things (Why do u have comments - select statements all over)?

Just start with a simple insert and expand that, little by little

jim65
Starting Member

35 Posts

Posted - 2006-03-17 : 14:21:32

select statement is the value what I need. is it wrong?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-17 : 14:58:33

If u want to insert from 1 table to another
(u will insert all records found from the select part, into the destination tbl. u don't have to do it in a loop)

Insert into tblDestination (<fields list>)
Select <fields list> from Source where <Condition>

if u want to use a Parameter in a query, U can do it as

Select <fields list> from Source where urcheckingfiled = @param

If this doesn't help
Tell us what u want to do

From what table u want to select records and to what table u want to insert

jim65
Starting Member

35 Posts

Posted - 2006-03-17 : 15:15:23

you mean the SP looks like, it don't work also

CREATE PROCEDURE dbo.asp_Insert_ALL_BorrowerfromCDB (@UserID varchar(7)) AS

set nocount on

INSERT INTO adBorrower
(BorrowerName, BorrowerMainAdress, BorrowerZip, ClosingDate, LenderEnumber, ProposedTCC, BankID, CustNum)
SELECT CustNam, CustAddr, CustZip, EstablishDt, @UserID, NetExposure, BankNum, dbo.adBorrower.CustNum
FROM CDB.dbo.tblCustomer INNER JOIN
CDB.dbo.tblBusLineHierarchy ON CDB.dbo.tblCustomer.PrimOff = CDB.dbo.tblBusLineHierarchy.PrimOff LEFT OUTER JOIN
dbo.adBorrower ON CDB.dbo.tblCustomer.CustNum = dbo.adBorrower.CustNum
WHERE dbo.adBorrower.LenderEnumber = @UserID
GO

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-17 : 15:19:54

Select & Execute the following & c whether it gives any result (Make sure u assign userID to the appropriate place):

Declare @UserID varchar(7)

Set @UserID = '<Whatever u pass as parameter>'

SELECT CustNam, CustAddr, CustZip, EstablishDt, @UserID, NetExposure, BankNum, dbo.adBorrower.CustNum
FROM CDB.dbo.tblCustomer INNER JOIN
CDB.dbo.tblBusLineHierarchy ON CDB.dbo.tblCustomer.PrimOff = CDB.dbo.tblBusLineHierarchy.PrimOff LEFT OUTER JOIN
dbo.adBorrower ON CDB.dbo.tblCustomer.CustNum = dbo.adBorrower.CustNum
WHERE dbo.adBorrower.LenderEnumber = @UserID

jim65
Starting Member

35 Posts

Posted - 2006-03-17 : 15:35:05

when I used this, it still doesn't work

CREATE PROCEDURE dbo.asp_Insert_ALL_BorrowerfromCDB (@UserID varchar(7)) AS

set nocount on

Declare @UserID1 varchar(7)

Set @UserID1 = @UserID

INSERT INTO adBorrower
(BorrowerName, BorrowerMainAdress, BorrowerZip, ClosingDate, LenderEnumber, ProposedTCC, BankID, CustNum)
SELECT CustNam, CustAddr, CustZip, EstablishDt, @UserID, NetExposure, BankNum, dbo.adBorrower.CustNum
FROM dbo.adBorrower RIGHT OUTER JOIN
CDB.dbo.tblCustomer INNER JOIN
CDB.dbo.tblBusLineHierarchy ON CDB.dbo.tblCustomer.PrimOff = CDB.dbo.tblBusLineHierarchy.PrimOff ON
dbo.adBorrower.CustNum = CDB.dbo.tblCustomer.CustNum
WHERE dbo.adBorrower.LenderEnumber = @UserID1
GO

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-17 : 16:36:54

jim65,

Can u do what I suggested above (first read it carefully)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources