insert cobination string and date

You can check the following store procedure

CREATE PROCEDURE [dbo].[Check]
	@UserInput nvarchar(50),
	@Last int
AS
DECLARE @Result nvarchar(20)
DECLARE @pad_characters VARCHAR(10)



SET @pad_characters = '0000000000'


SET @Result = @UserInput+SUBSTRING(convert(nvarchar,Datepart(Year,getdate())) ,3 ,2)+ RIGHT(@pad_characters+convert(nvarchar(10),@Last),10)


SeLECT @Result

Exec [dbo].[Check] 'BCA ',1

Hope be helpful,
Theingi Win

FLetz assume the key column (BCA11000000001) name as DataKey. Letz create the table as:

CREATE TABLE dbo.MyTable
(
    DataKey int NOT NULL PRIMARY KEY,
    OtherKey varchar(50),
    ....
) 

SQL maintains DataKey column as identity primary column, starting from 1. Expected key is constructed using SELECT command as:

SELECT 'BCA'+ substring(year(getdate()),1,2) + Cast(Datakey as varchar(10)

It will result the expected code BCA11000000001

let test this:

Create Procedure InsertData @code varchar(50)
As
Begin
    Declare @year varchar(2)
    Declare @number int
    Declare @formatedNumber varchar(20)
    Declare @value varchar(50)
    Select @number=MAX(CAST(RIGHT(code,10) As int)) from TableName
    Set @number=ISNULL(@number,0) + 1
    Set @formatedNumber=RIGHT('000000000'+CAST(@number as varchar(10)),10)
    Set @year=RIGHT(CAST(YEAR(GETDATE()) As varchar(4)),2)
    Set @value=@code + @year + @formatedNumber
    Insert into TableName(code) values(@value)
End