February 16, 2004 at 9:36 am
Hi All,
I've seem to have a brain cramp and can't get this to stored procedure to work:
My problem is I can't remember how to reset the variables to iterate to the next value for example:
IF @acctno != @last_acctno
IF @i != @last_i
How do I evaluate the previous value with the new value??
Thanks in Advance
CREATE PROCEDURE spc_storedprocedure
(
@TableName VARCHAR(20),
@TopValue VARCHAR(10)
)
AS
DECLARE @Error INT
SET NOCOUNT ON
BEGIN
DECLARE
@acctno INT,
@i VARCHAR(14),
@no FLOAT
DECLARE
@sSQL VARCHAR(8000),
@sCursor VARCHAR(8000),
@last_acctno INT,
@last_i VARCHAR(10)
SET @sSQL = ''
SET @sSQL = 'SELECT DISTINCT
acctno,
i,
no
FROM '
+ RTRIM(@TableName) +
' ORDER BY acctno, i;'
SET @sCursor = ''
SET @sCursor = 'DECLARE VARCursor CURSOR FOR ' + @sSQL
EXEC(@sCursor)
OPEN VARCursor
FETCH NEXT FROM VARCursor INTO @acctno,@i,@no
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF @acctno != @last_acctno
IF @i != @last_i
PRINT @acctno
-- Will be executing another sp passing params vars
FETCH NEXT FROM VARCursor INTO @acctno, @i, @no
END
CLOSE VARCursor
DEALLOCATE VARCursor
END
SET @Error = @@ERROR
IF @Error <> 0
BEGIN
RETURN @Error
END
RETURN @Error
GO
February 16, 2004 at 11:25 am
Base don what I see so far, for your first time through you are going to execute your code in the IF's. After executing you need to Set @last_acctno = @acctno and Set @last_i = @i then do your FETCH_NEXT. Like this:
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF @acctno != @last_acctno
BEGIN
IF @i != @last_i
BEGIN
PRINT @acctno
-- Will be executing another sp passing params vars
Set @last_acctno = @acctno
Set @last_i = @i
END
END
FETCH NEXT FROM VARCursor INTO @acctno, @i, @no
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 16, 2004 at 11:28 am
Thanks,
This works
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply