declare @s as varchar(1000)
set @s = ''
declare @i as int
set @i = 1
while @i <= 10
begin
  set @s = @s + 'XM' + right('00' + cast(@i as varchar),3) + ','
  set @i = @i + 1
end
set @s = substring(@s , 1 , len(@s) - 1)

print @s

/*
XM001,XM002,XM003,XM004,XM005,XM006,XM007,XM008,XM009,XM010
*/

------解决方案--------------------
SQL code

declare @xm varchar(50)
select @xm='XM'

select top 1000 id=identity(int,1,1) into #temp
from sysobjects,syscolumns

select @xm+right(1000+id,3) as id
from #temp

drop table #temp

------解决方案--------------------
SQL code

select *,rn='XM'+right(1000+row_number() over (order by getdate()),3)
from(
select dept_code,title,dept_sid from view_hum_dept where full_name like '%sanan%'
union all
select @deptcode,drbm,drbm_sid=@deptsid from sa_ygddtzd
)t