AutoGenerate using StoredProcedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

AutoGenerate using StoredProcedure?

Author

Topic

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-16 : 03:41:13

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 has some cities.So as per my requirement If i select City as Mumbai and submit output should be Mum1.If selected Calcutta it should be Cal1 and so on..My Following code Does Gives The required number But How can I generate cityname as well.



DECLARE @NewNo Numeric(9)
SET  @NewNo = 0
SET NOCOUNT ON
BEGIN TRANSACTION

       UPDATE AUTOREGNO WITH (ROWLOCK) SET LastAutoNo = LastAutoNo + 1
         
        where TableId = @intTableId
       
        SELECT  @NewNo =  (SELECT LastAutoNo 
                       FROM AUTOREGNO
                       where TableId = @intTableId)
           
          SELECT @NewNo as 'NewNoGenerated'

COMMIT TRANSACTION

Hope u got my question.Plz help me..

Thanks.....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 03:47:38

[code]
SELECT 'Mum' + convert(varchar(9), @NewNo) as 'NewNoGenerated'
[/code]

KH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 03:49:47

[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 CityName + CONVERT(VARCHAR, @NewNo) AS 'NewNoGenerated'
FROM MyCityTable
WHERE MyCityTable.MyCityIDColumn = @intTableID

COMMIT TRANSACTION[/code]

Peter Larsson
Helsingborg, Sweden

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-16 : 03:52:35

Thanks Peso..
It really worked...Thanks

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-16 : 09:38:49

Oops,
I still could only generate only number.This is my TableName.
CITIES and its columns are id,city


SELECT	Mumbai + CONVERT(VARCHAR, @NewNo) AS 'NewNoGenerated'
	FROM	cities
	WHERE	cities.ID = @intTableID

But I could'nt generate the required Output.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 10:06:09

Should be like this ?


SELECT	City + CONVERT(VARCHAR, @NewNo) AS 'NewNoGenerated'
	FROM	cities
	WHERE	cities.ID = @intTableID

KH

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-16 : 10:16:23

Yeah KH,
I did Try the Code mentioned by u But I could only get the Output as 0.Is it something to do with my FrontEnd Code in .net Am i required to pass any parameter values.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-16 : 10:30:58

can you post your full code ?

KH

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-17 : 01:02:26

ok,This is my code..


ALTER      PROCEDURE amcsp_AUTOREGNO
(
@intTableId as numeric(3) = null
)
as

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	City + CONVERT(VARCHAR,@NewNo) AS 'NewNoGenerated'
	FROM	Cities
	WHERE	Cities.id = @intTableID


	
COMMIT TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 01:07:33

Since you return CityName plus a sequence number, the return value is treated as text.
Make sure the parameter in front end application is defined as adVarChar, not numeric.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-21 : 08:43:22

Do you need something like this?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources