I have a steps counter with increment 1 after

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 2008 Forums

Transact-SQL (2008)

I have a steps counter with increment 1 after

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-05-24 : 15:10:43

In the following sp, it builds row id's which are steps increment of 1.
upto 10 it is good, after 10 instead of 11, it is again showing 10.
here is my scritp within SP. which i use:Cast is the one it is causing issue.


	CREATE TABLE #Steps(Step varchar(20) null)

	DECLARE @i Int, @Max Int

	SET @i = 1

	IF @Edit = 0 
		INSERT INTO #Steps VALUES(null)

	IF EXISTS(SELECT 1 FROM dbo.Tab_WorkflowActivity WHERE ModuleRecordID = @ModId And ModuleName = @ModuleName)
		BEGIN
			INSERT INTO #Steps SELECT DISTINCT Step FROM dbo.Tab_WorkflowActivity WHERE ModuleRecordID = @ModId And ModuleName = @ModuleName;
			SELECT @Max = CAST(MAx(Step) As Int) FROM #Steps;
			INSERT INTO #Steps VALUES(@Max + 1);
		END
	ELSE
		BEGIN
			INSERT INTO #Steps VALUES('1');
		END

	SELECT * from #Steps

Here are the results, it shows 10 after 10 instead of 11.
Step
-----
NULL
1
2
3
4
5
6
7
8
9
10
10 it should be 11.

Can you please tell me what am i doing wrong.

Thanks a lot for the helpful info.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-05-24 : 15:44:07

9 varchar > 10 varchar hence you will always be adding 1 + 9 = 10

select * From #Steps order by Step asc

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 15:48:15

why do you need to generate this manually? why not make it an identity field to generate values automatically? or if sequence has to be based on some other column value, use ROW_NUMBER() function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-05-24 : 17:13:09

Visakh,

I am populating teh result to a dropdownlist box on teh front end.

if the max number is 9 then fill dropdown upto 10 numbers.

if teh highest id in the table is 10, then fill the results to 11.

starting from NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11...

Because i don't want to provide more than 1 increment to the users that way the users willl select only the nu,bers which are in dropdown list box.

Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 10:09:47

thats ok. but why cant you use query based on ROW_NUMBER() to generate this and populate dropdown?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources