Error in Auto increment of Name

[rjohn2001]

Hi


I have written following stored procedure to insert viewName in to my Views table

CREATE PROCEDURE spaddView
(
@ViewName varchar(20)

)
AS

DECLARE @ErrorCode int
SELECT @ErrorCode = @@Error

SET @ViewName=''

DECLARE @NewviewId int
SET @NewviewId=0

SELECT ViewId FROM Views WHERE RTrim(Name)=RTrim(@ViewName) 
IF @@ROWCOUNT > 0
BEGIN
	SET @NewviewId=0
	RETURN 0
END

IF @ErrorCode = 0
BEGIN
	BEGIN TRANSACTION
	

	INSERT INTO Views(ViewId,Name)
	SELECT 1 + COALESCE(MAX(ViewId),0),@ViewName
	FROM Views

SELECT @ErrorCode = @@Error
END
IF @ErrorCode = 0
	BEGIN	
	
	DECLARE @viewcount int	
	SELECT @viewcount=ISNULL(max(CAST(SUBSTRING(Name,5,len(Name)) AS Integer)),0)+1
	FROM Views
	WHERE Name LIKE 'View%'
	
	SELECT @NewviewId=ViewId FROM Views WHERE RTrim(Name)=RTrim(@ViewName) 

	SET @ViewName='View'+cast(@viewcount as varchar(8))

	UPDATE Views 
	SET Name='View'+cast(@viewcount as varchar(8))
	WHERE ViewId=@NewviewId

	COMMIT TRANSACTION
	END
ELSE
	BEGIN
	SET @NewviewId=0
	ROLLBACK TRANSACTION
	END
GO

This gives me a problem if the name contains some thing like 'View of John'


i am trying to insert names in to the views table as
view1
view2
...etc

using this stored procedure.

Every time when i make a new insert what i am doing is, i am incrementing the integer value of the previouse name


but the problem is if there is alread any name in the views table as 'View of John' which gives me a problem

can any one give me any idea what is the best way to solve this problem

i wanted to add names as view1,view2 etc and also allowing other name which can contain name as in our example('view of John') etc

hope i am clear with my problem ..

thanks for any help

 

[rjohn2001]

hi guys

i my self wanted to make the problem simple so any genius can help me out in fixing this problem


i have names in my table as view1,view2 etc, so what i wanted to do is i want to find the next max name sequence which obviously 3 so my new name will be view3


the problem is if some one created a name as 'view of john' then i am running in to problem by using
this statement

SELECT @viewcount=ISNULL([COLOR=#ff0000]max(CAST(SUBSTRING(Name,5,len(Name)) AS Integer)),0)+1[/color]

FROM Views
WHERE Name LIKE 'View%'
	
SET @ViewName='View'+cast(@viewcount as varchar(8))

thanks in advance

john

 

[rjohn2001]

hi all i fixed this problem my self..

posting here incase if any one want to have a look at.

SELECT @ViewName= 'View'+ISNULL(CAST(MAX(dt.value)+1 AS VARCHAR(50)),1)
	FROM (
		SELECT SUBSTRING(Name,5,len(Name)) as value
		FROM Views
		WHERE Name LIKE 'View%' AND ISNUMERIC(SUBSTRING(Name,5,len(Name)))=1
	) as dt