小错误！

Question

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROC [dbo].[PROC_SCORE] 
@Class_ID varchar(10), 
@Subject_Name nchar(10), 
@Sortmethod int 
As
declare @order varchar(50)
Set @order=          
      case  @Sortmethod 
              when 0 then   ' order by Student_ID '
              when 1 then   ' order by Student_ID DESC '
              when 2 then   ' order by ToTal_Score,Student_ID  '
              when 3 then   ' order by ToTal_Score DESC,Student_ID DESC  '
      end   exec ('Select Student_ID,Student_Name,Class_ID,Subject_Name,Term_Middle,Term_End,Peacetime_Score,ToTal_Score ,
[code=SQL]Style_Subject 
from  View_GetAllScore where  Class_ID= '+ @Class_ID+' and Subject_Name= '+@Subject_Name+' order by Student_ID  ')
[/code]
exec语句有错吗，where  Class_ID= '+ @Class_ID+' and Subject_Name= '+@Subject_Name+'这句呢？

Answer 1

exec语句有错吗，?你的exec在哪儿？
如果有错的话，建议先print一下，看看执行的sql是什么

Answer 2

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROC [dbo].[PROC_SCORE] 
@Class_ID varchar(10), 
@Subject_Name nchar(10), 
@Sortmethod int 
As
declare @order varchar(50)
Set @order=          
      case  
              when @Sortmethod=0 then   ' order by Student_ID '
              when @Sortmethod=1 then   ' order by Student_ID DESC '
              when @Sortmethod=2 then   ' order by ToTal_Score,Student_ID  '
              when @Sortmethod=3 then   ' order by ToTal_Score DESC,Student_ID DESC  '
      end   exec (' Select Student_ID,Student_Name,Class_ID,Subject_Name,Term_Middle,Term_End,Peacetime_Score,ToTal_Score ,Style_Subject 
from  View_GetAllScore where  Class_ID= '+ @Class_ID+' and Subject_Name= '+@Subject_Name+' order by Student_ID  ')

Answer 3

昏！ALTER PROC [dbo].[PROC_SCORE] 
@Class_ID varchar(10), 
@Subject_Name nchar(10), 
@Sortmethod int 
As
declare @order varchar(50)
Set @order=          
      case  @Sortmethod 
              when 0 then   ' order by Student_ID '
              when 1 then   ' order by Student_ID DESC '
              when 2 then   ' order by ToTal_Score,Student_ID  '
              when 3 then   ' order by ToTal_Score DESC,Student_ID DESC  '
      end   exec ('Select Student_ID,Student_Name,Class_ID,Subject_Name,Term_Middle,Term_End,Peacetime_Score,ToTal_Score ,Style_Subject 
from  View_GetAllScore 
where  Class_ID = ' +@Class_ID+ ' and ' +'Subject_Name=' +@Subject_Name + @order)

Answer 4

你的@order没用进去set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROC [dbo].[PROC_SCORE] 
@Class_ID varchar(10), 
@Subject_Name nchar(10), 
@Sortmethod int 
As
declare @order varchar(50)
Set @order=          
      case  
              when @Sortmethod=0 then   ' order by Student_ID '
              when @Sortmethod=1 then   ' order by Student_ID DESC '
              when @Sortmethod=2 then   ' order by ToTal_Score,Student_ID  '
              when @Sortmethod=3 then   ' order by ToTal_Score DESC,Student_ID DESC  '
      end   exec ('Select Student_ID,Student_Name,Class_ID,Subject_Name,Term_Middle,Term_End,Peacetime_Score,ToTal_Score ,Style_Subject 
from  View_GetAllScore where  Class_ID= '+ @Class_ID+' and Subject_Name= '+@Subject_Name+@order)

Answer 5

case when加个else保险一些，否则@order可能为null，导致语句失败。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROC [dbo].[PROC_SCORE] 
@Class_ID varchar(10), 
@Subject_Name nchar(10), 
@Sortmethod int 
As
declare @order varchar(50)
Set @order=          
      case  
              when @Sortmethod=0 then   ' order by Student_ID '
              when @Sortmethod=1 then   ' order by Student_ID DESC '
              when @Sortmethod=2 then   ' order by ToTal_Score,Student_ID  '
              when @Sortmethod=3 then   ' order by ToTal_Score DESC,Student_ID DESC  '
              else ''
      end   exec ('Select Student_ID,Student_Name,Class_ID,Subject_Name,Term_Middle,Term_End,Peacetime_Score,ToTal_Score ,Style_Subject 
from  View_GetAllScore where  Class_ID= '+ @Class_ID+' and Subject_Name= '+@Subject_Name+@order)

Answer 6

'p' 附近有语法错误。
我传的参数是
10752p    <-有错误 Class_ID= '+ @Class_ID+'  ？
'ARMj技术'
0

Answer 7

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROC [dbo].[PROC_SCORE] 
@Class_ID varchar(10), 
@Subject_Name nchar(10), 
@Sortmethod int 
As
declare @order varchar(50)
Set @order=          
      case  
              when @Sortmethod=0 then   ' order by Student_ID '
              when @Sortmethod=1 then   ' order by Student_ID DESC '
              when @Sortmethod=2 then   ' order by ToTal_Score,Student_ID  '
              when @Sortmethod=3 then   ' order by ToTal_Score DESC,Student_ID DESC  '
              else ''
      end   print('Select Student_ID,Student_Name,Class_ID,Subject_Name,Term_Middle,Term_End,Peacetime_Score,ToTal_Score ,Style_Subject 
from  View_GetAllScore where  Class_ID= '''+ @Class_ID+''' and Subject_Name= ''''+@Subject_Name+@order)改成上面的就可以了，因为你传的是字符串，生成的sql要加单引号

Answer 8

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROC [dbo].[PROC_SCORE] 
@Class_ID varchar(10), 
@Subject_Name nchar(10), 
@Sortmethod int 
As
declare @order varchar(50)
Set @order=          
      case  
              when @Sortmethod=0 then   ' order by Student_ID '
              when @Sortmethod=1 then   ' order by Student_ID DESC '
              when @Sortmethod=2 then   ' order by ToTal_Score,Student_ID  '
              when @Sortmethod=3 then   ' order by ToTal_Score DESC,Student_ID DESC  '
              else ''
      end   print('Select Student_ID,Student_Name,Class_ID,Subject_Name,Term_Middle,Term_End,Peacetime_Score,ToTal_Score ,Style_Subject 
from  View_GetAllScore where  Class_ID= '''+ @Class_ID+''' and Subject_Name= '''+@Subject_Name+''''+@order)