cant use master..spt_values

ok..
then in a sql server create a procedure for inserting into your table name
like below

create proc SP_GeneratePin

@kit nvarchar(6)
.
.
.
as

begin
insert into yourtable(columns,..,..) values (param1,param2)

declare @iden 
@iden=@@identity

if(@kit='scalar')

update yourtable table pin= 'sc' + convert(char,@iden) where youridentityid=@iden

else if(@kit='scalar')

update yourtable table pin= 'abc' + convert(char,,@iden) where youridentityid=@iden 

end

let me know if there is any error come across

You can something like:


1. Have a column that has identity specification as true and increment value 1.
2. now since this column will always have unique values you can either pad it to append it to some string so that you get the PIN as per your requirements.

master..spt_values

may not be a right choice for you. It is an undocument procedure meant for internal use. Such procedures may be dropped/modified in future releases without any notification.

Use rownumber function. Check this link
http://msdn.microsoft.com/en-us/library/ms186734.aspx[^]

If you are keen on using spt_values see this link http://sqldbpool.com/2010/12/15/master-spt_values-system-table/[^]