I believe the following code snippet would do what you're wanting and without using a cursor.

-------------------------------------------------------
DECLARE @VNO INT
DECLARE @NTOCVNO VARCHAR(4)

SELECT TOP 1
@VNO = ISNULL(RIGHT(GLT_MF.VNO,4), 0)
@NTOCVNO = DBO.NTOC(+ 1),
@PRD = LEFT(@PRD,2) + @NTOCVNO
FROM (SELECT
@BRNCODE BRNCode,
@VTYPE VType,
@FISNo FISNo) PP
LEFT JOIN GLTRAN_MF GLT_MF
ON GLT_MF.BRNCODE = PP.BRNCode
AND GLT_MF.VTYPE = PP.VType
WHERE LEFT(GLT_MF.VNO,5) = (PP.FISNO + LEFT(@PRD, 2))
ORDER BY GLT_MF.VNO DESC
-------------------------------------------------------

Kynan C



-----Original Message-----
From: Muhammad Siddique [mailto:mssqldba-ezmlmshield-x90355570.[Email address protected]
Sent: Thursday, March 29, 2007 1:24 AM
To: LazyDBA Discussion
Subject: Next Number - Urgent

Dear All,

I have made below routine for getting next number where cursor is
created.
Is there any way short way like if exist(select..) through I can get
next number?
Please help.

DECLARE @FISPRD AS VARCHAR(6)
SET @FISPRD = @FISNO + LEFT(@PRD, 2)
DECLARE @VNORETURN INT
DECLARE @VNO INT
DECLARE @NTOCVNO VARCHAR(4)
SET @VNORETURN = 0
--DECLARE CURSOR
DECLARE VNO_CURSOR CURSOR LOCAL SCROLL STATIC
FOR
SELECT RIGHT(VNO,4) FROM GLTRAN_MF WHERE BRNCODE = @BRNCODE AND VTYPE =
@VTYPE AND
LEFT(VNO,5) = @FISPRD ORDER BY VNO DESC
OPEN VNO_CURSOR
FETCH NEXT FROM VNO_CURSOR INTO @NTOCVNO
IF @@CURSOR_ROWS = 0
SET @NTOCVNO = '0'
CLOSE VNO_CURSOR
DEALLOCATE VNO_CURSOR
--CLOSE CURSOR
SET @VNO = @NTOCVNO
SELECT @NTOCVNO = DBO.NTOC(@VNO+1)
SET @PRD = LEFT(@PRD,2) + @NTOCVNO

Regards,
Siddique

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




MS Sql Server LazyDBA home page