Not able to capture the error in store procedure in sql server

the body of my SP as follow...when i trying to run it then i am getting error. i just do not understand why error thrown...where the actual error is. please help me.

Create table #jobs (id int identity(1,1),Specialist varchar(50),JID int)  
insert into #jobs values('ANA', 1) 
insert into #jobs values('ANA', 5) 
insert into #jobs values('ANA', 9) 
insert into #jobs values('ANA', 7) 
insert into #jobs values('ANA', 6)   

insert into #jobs values('GEN', 2) 
insert into #jobs values('GEN', 3) 
insert into #jobs values('GEN', 11) 
insert into #jobs values('GEN', 13) 
insert into #jobs values('GEN', 45)  
insert into #jobs values('GEN', 23) 
insert into #jobs values('GEN', 28)    
insert into #jobs values('GEN', 41) 
insert into #jobs values('GEN', 49) 
insert into #jobs values('GEN', 52) 

insert into #jobs values('robin', 12) 
insert into #jobs values('robin', 15) 
insert into #jobs values('robin', 17) 

declare @i smallint, @j smallint = 0
; with cte as (select COUNT(*) cnt FROM #jobs group by Specialist) select @i=MAX(cnt) from cte

declare @sql nvarchar(max)
set @sql = N'
;with cte as (
        select distinct Specialist from #jobs
)
select
        Specialist '

while @i > @j
begin
        set @j = @j + 1;
        set @sql = @sql + N'
        ,(
       开发者_StackOverflow中文版         select id from (
                        select 
                                rn = row_number() over (partition by Specialist order by id),
                                id
                        from #jobs
                        where Specialist = cte.Specialist
                ) t
                where rn = ' + cast(@j as varchar(3)) + '
        ) as id
        '
end

set @sql = @sql + N' from cte '
print @sql
exec sp_executesql @sql

you cannot Assign default values to Local Variables ... so just remove the "= 0" from this line :

declare @i smallint, @j smallint = 0 ; with ct....

so it reads...

declare @i smallint, @j smallint; with ct....

HTH. Dave

PS... don't forget to Drop your Temp Table when you finished with it...

Change

declare @i smallint, @j smallint = 0

to

DECLARE @i SMALLINT, @j SMALLINT
SET @j = 0