INTO statement - SQL Server Forums

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

Transact-SQL (2000)

INTO statement

Author

Topic

Krisabi
Starting Member

2 Posts

Posted - 2004-08-13 : 06:21:38

I'm having problems with the stored procedure below because i'm not able to get the resultset or return the result of this procedure through or to vb (visual basic 6.0) and need help as to how to go about it.
Thanks in advance

CREATE PROCEDURE sp_InterestByCertificateNo

@CertificateNo varchar(15),
@sDate smalldatetime,
@eDate smalldatetime

AS

DECLARE @IntDate smalldatetime,
@FaceValue money,
@Rate decimal,
@Interest money,
@Count int,
@Amount money

CREATE TABLE #InterestFlow
(

InterestDate smalldatetime,
FaceValue money,
Rate decimal,
Interest money,
Balance money

)

SET @Count = 0

DECLARE rsINT CURSOR FOR
SELECT InterestDate,FaceValue,Rate,Interest
FROM InterestAccruals WHERE certificateno=@CertificateNo
AND InterestDate BETWEEN @sDate AND @eDate

OPEN rsINT

FETCH NEXT FROM rsINT INTO @IntDate, @Facevalue, @Rate, @Interest

WHILE @@FETCH_STATUS = 0
BEGIN

IF @Count = 0
BEGIN

INSERT INTO #InterestFlow
(InterestDate, FaceValue, Rate, Interest, Balance)
VALUES(@IntDate, @FaceValue, @Rate,0 , @FaceValue)

SET @Amount = @FaceValue + @Interest

INSERT INTO #InterestFlow
(InterestDate, FaceValue, Rate, Interest, Balance)
VALUES(@IntDate, 0, 0, @Interest, @Amount)

END
ELSE
BEGIN

SET @Amount = @Amount + @Interest

INSERT INTO #InterestFlow
(InterestDate, FaceValue, Rate, Interest, Balance)
VALUES(@IntDate, 0, 0, @Interest, @Amount)

END

SET @Count = @Count + 1
FETCH NEXT FROM rsINT INTO @IntDate, @Facevalue, @Rate, @Interest

END

CLOSE rsINT
DEALLOCATE rsINT

SELECT InterestDate, FaceValue, Rate, Interest, Balance FROM #InterestFlow

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-13 : 06:26:22

Try this -


CREATE PROCEDURE sp_InterestByCertificateNo

@CertificateNo varchar(15),
@sDate smalldatetime,
@eDate smalldatetime

AS

SET NOCOUNT ON 

DECLARE @IntDate smalldatetime,
@FaceValue money,
@Rate decimal,
@Interest money,
@Count int,
@Amount money




CREATE TABLE #InterestFlow
(

InterestDate smalldatetime,
FaceValue money,
Rate decimal,
Interest money,
Balance money


) 


SET @Count = 0

DECLARE rsINT CURSOR FOR
SELECT InterestDate,FaceValue,Rate,Interest
FROM InterestAccruals WHERE certificateno=@CertificateNo
AND InterestDate BETWEEN @sDate AND @eDate


OPEN rsINT

FETCH NEXT FROM rsINT INTO @IntDate, @Facevalue, @Rate, @Interest

WHILE @@FETCH_STATUS = 0
BEGIN

IF @Count = 0
BEGIN 

INSERT INTO #InterestFlow
(InterestDate, FaceValue, Rate, Interest, Balance)
VALUES(@IntDate, @FaceValue, @Rate,0 , @FaceValue) 


SET @Amount = @FaceValue + @Interest

INSERT INTO #InterestFlow
(InterestDate, FaceValue, Rate, Interest, Balance)
VALUES(@IntDate, 0, 0, @Interest, @Amount) 


END
ELSE
BEGIN


SET @Amount = @Amount + @Interest

INSERT INTO #InterestFlow
(InterestDate, FaceValue, Rate, Interest, Balance)
VALUES(@IntDate, 0, 0, @Interest, @Amount) 


END



SET @Count = @Count + 1
FETCH NEXT FROM rsINT INTO @IntDate, @Facevalue, @Rate, @Interest

END

CLOSE rsINT 
DEALLOCATE rsINT

SET NOCOUNT OFF

SELECT InterestDate, FaceValue, Rate, Interest, Balance FROM #InterestFlow

And if it works, consider making SET NOCOUNT ON part of your policy for stored procedures.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources