How can we use prefix as zero in SQL?

How can we use zero's in prefix of the number column feedback_refno like

s开发者_运维问答elect @refno = '0001'

i need to insert this value into that column feedback_refno but it is inserting like 1 only..but i need those prefix before those 1

I have tried like this

declare @refno int
select  max(feedback_refno)+1 from EDK_Customer_Feedback(nolock)

if not exists(select feedback_refno from EDK_Customer_Feedback(nolock))
Begin 
    select @refno = '0001'
end     
else
Begin
    select @refno
End
insert into EDK_Customer_Feedback values(@refno)

I need the result like 0002 then 0003 like that but it is giving like 2 then 3..

Any suggestion?

try this

SELECT RIGHT('000'+ CONVERT(varchar,feedback_refno),4) AS NUM FROM EDK_Customer_Feedback;

@refnois of type int, so leading zeros wont work. If you change it to varchar(4) you can use these two answers:

In SQL Server 2000 how do you add 0's to beginning of number to fill nchar(n)

In SQL Server 2000 how do you add {n} number of 0's to a nchar(n)?