转换 nvarchar 过程时错误转换失败

Question

表视图组

NO_induk  |  Gaji_bulan
-----------------------                     
200012    |      012017                                                   
200012    |      022017                      
200012    |      122017               
200006    |      012017           
200006    |      022017           
200006    |      122017              
2000AA    |      012017          
2000AA    |      022017          
2000AA    |      122017             
ALTER PROCEDURE [dbo].[prcgroup]
@no_induk1 nvarchar(50),
@no_induk2 nvarchar(50),
@bulan1 nvarchar(6),
@bulan2 nvarchar(6)
AS
BEGIN
DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
SELECT @cols = @cols + QUOTENAME(gaji_bulan) + ',' FROM (select distinct gaji_bulan from tblgaji_detail ) as tmp order by gaji_bulan
select @cols = substring(@cols, 0, len(@cols))  
set @query = 
'SELECT * from 
(
select no_induk,nama_pelamar,kodept,namapt,kodetp,ket, nilai_ahir, gaji_bulan from View_group where
no_induk>='+@no_induk1+' and no_induk<='+@no_induk2+' 
and gaji_bulan>='+@bulan1+' and gaji_bulan<='+@bulan2+' 
) src
pivot 
(
max(nilai_ahir) for gaji_bulan in (' + @cols + ')
) piv'
execute(@query)
END

如果我像这样执行过程，它运行良好：

exec prcgroup '200006','200006','012017','122017'

如果我像这样执行它，我有一个问题：

exec prcgroup '200006','2000AA','012017','122017'

错误 Msg 245，级别 16，状态 1，第 1 行 将 nvarchar 值"2000AA"转换为数据类型 int 时转换失败。

我希望它像这样运行：

prcgroup '200006','2000AA','012017','122017'

Answer 1

您正在比较字符串，因此必须用 SingleQuote('( 装饰它。因此，在>=条件之后，您必须使用'''+@no_induk2+'''而不是'+@no_induk2+'. 更新了您的查询，如下所示：

set @query = 
'SELECT * from 
(
select no_induk,nama_pelamar,kodept,namapt,kodetp,ket, nilai_ahir, gaji_bulan from View_group where
no_induk>='''+@no_induk1+''' and no_induk<='''+@no_induk2+''' 
and gaji_bulan>='''+@bulan1+''' and gaji_bulan<='''+@bulan2+''' 
) src
pivot 
(
max(nilai_ahir) for gaji_bulan in (''' + @cols + ''')
) piv'