Click here to Skip to main content
15,894,337 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
ALTER PROCEDURE [dbo].[InsertAlbum_sp]
		(
			@Member_Id varchar(10),
			@albumTitle varchar(50)
		)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @newId varchar(10)
    declare @length int
	if(select MAX(album_Id) from album_tb)<>'null'
	begin
	
		set @length=(select len(max(album_Id)) from album_tb)
		set @newId=(select SUBSTRING(max(album_Id),1,@length)+1 from album_tb)
		set @newId='A'+RIGHT(cast(@newId as varchar(10)),1)
	end
	else
	begin
		set @newId='A1'
	end

	insert into album_tb (album_Id,Member_Id,albumTitle) values (@newId,@Member_Id,@albumTitle)
	select @newId
END
Posted
Updated 6-May-13 20:16pm
v2

If you want to store integer data, use integer data types, not varchar, in first place. It looks like the trend to work with strings representing data instead of data itself became a real curse of the beginners these days.

—SA
 
Share this answer
 
No surprises, the message is clear, you are trying to convert 'A1' that is a NOT valid representation of an integer number (unless it is an hexadecimal representation, in that case you have to explicitely convert it as such), to an int.
 
Share this answer
 
v2
Comments
Libin C Jacob 7-May-13 3:02am    
Actually I want o return the value fro @newId. It is a varchar.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900