无法在sql server中捕获存储过程中的错误

Question

我的SP的主体如下。。。当我试图运行它时，我会出错。我只是不明白为什么会出现错误。。。实际错误在哪里。请帮帮我。

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'
        ,(
                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

Answer 1

您不能将默认值分配给局部变量。。。所以只需从这行删除"=0"：

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

所以上面写着。。。

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

HTH。Dave

PS。。。完成后不要忘记放下临时表…

Answer 2

更改

declare @i smallint, @j smallint = 0

至

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