www.webdeveloper.com
Results 1 to 2 of 2

Thread: Autogenerate number using storedprocedure?

  1. #1
    Join Date
    Jul 2006
    Posts
    33

    Autogenerate number using storedprocedure?

    Hi,
    I am trying to AutoGenerate a number.Although I could generate the number But I want the output to be string + number format.I have a dropdown which contains name of Cities.So as per my requirement If i select City as Mumbai and submit, output should be Mumbai1.If selected Calcutta it should be Calcutta1 and so on..My Following code Does Gives The required number But How can I generate cityname as well.My table name is CITIES and it consists of id & city column.The values are 1-Mumbai
    2-Calcutta
    3-Bangalore

    Code:
    DECLARE @NewNo INT
    
    SET NOCOUNT ON
    
    BEGIN TRANSACTION
    	SELECT	@NewNo = LastAutoNo
    	FROM	AUTOREGNO
    	WHERE	TableId = @intTableId
    
    	IF @NewNo IS NULL
    		BEGIN
    			SELECT	@NewNo = 1
    
    			INSERT	@AUTOREGNO
    				(
    					LastAutoNo,
    					TableID,
    				)
    			SELECT	@NewNo,
    				@intTableID
    		END
    	ELSE
    		BEGIN
    			SELECT	@NewNo = @NewNo + 1
    
    			UPDATE	AUTOREGNO WITH (ROWLOCK)
    			SET	LastAutoNo = @NewNo
    		        WHERE	TableId = @intTableId
    		END
    
    	SELECT	Mumbai + CONVERT(VARCHAR, @NewNo)  AS 'NewNoGenerated'
    	FROM	cities
    	WHERE	cities.ID = @intTableID
    
    COMMIT TRANSACTION
    Could someone rectify the error plz...
    Thanks..

  2. #2
    Join Date
    Feb 2003
    Posts
    2,745
    fix the final portion:
    Code:
    SELECT	city + CONVERT(VARCHAR(255), @NewNo)  AS 'NewNoGenerated'
    FROM	cities
    WHERE	cities.ID = @intTableID

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles