当前位置：首页 → 问答吧 → 必须声明标量变量 "@CourseID"。

必须声明标量变量 "@CourseID"。

时间：2011-11-21

来源：互联网

C# code

 int a = 1;
        Params1[0] = DB.MakeInParam("@CourseID", SqlDbType.Int, 4, a);               //试卷编号      
        DataSet ds1 = DB.GetDataSet("Proc_PaperDetail",Params1);
        GridView2.DataSource = ds1;
        GridView2.DataBind();

        SqlParameter[] Params2 = new SqlParameter[1];
        int b = 2;
        Params2[0] = DB.MakeInParam("@CourseID", SqlDbType.Int, 4, b);               //试卷编号      
        DataSet ds2 = DB.GetDataSet("Proc_PaperDetail", Params2);
        GridView5.DataSource = ds2;

存储过程代码：
SQL code

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[Proc_PaperDetail]
        (@CourseID        [int])
AS
begin 
    declare @sql nvarchar(1000)
if @CourseID=1
 begin    
    set @sql='select * from MultiProblem where CourseID=@CourseID'
    exec sp_executesql @sql        
 end
else if @CourseID=2
 begin    
    set @sql='select * from MultiProblem where CourseID=@CourseID'
    exec sp_executesql @sql        
 end
else if @CourseID=3
 begin    
    set @sql='select * from MultiProblem where CourseID=@CourseID'
    exec sp_executesql @sql        
 end
else if @CourseID=4
 begin    
    set @sql='select * from MultiProblem where CourseID=@CourseID'
    exec sp_executesql @sql        
 end
else
 begin    
    set @sql='select * from MultiProblem where CourseID=@CourseID'
    exec sp_executesql @sql        
 end
end

作者: snlixing 发布时间: 2011-11-21

SQL code

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[Proc_PaperDetail]
        (@CourseID        [int])
AS
begin 
    declare @sql nvarchar(1000)
if @CourseID=1
 begin    
    set @sql='select * from MultiProblem where CourseID='+rtrim(@CourseID)
    exec sp_executesql @sql        
 end
else if @CourseID=2
 begin    
    set @sql='select * from MultiProblem where CourseID='+rtrim(@CourseID)
    exec sp_executesql @sql        
 end
else if @CourseID=3
 begin    
    set @sql='select * from MultiProblem where CourseID='+rtrim(@CourseID)
    exec sp_executesql @sql        
 end
else if @CourseID=4
 begin    
    set @sql='select * from MultiProblem where CourseID='+rtrim(@CourseID)
    exec sp_executesql @sql        
 end
else
 begin    
    set @sql='select * from MultiProblem where CourseID='+rtrim(@CourseID)
    exec sp_executesql @sql        
 end
end

作者: roy_88 发布时间: 2011-11-21

SQL code

ALTER PROCEDURE [dbo].[Proc_PaperDetail]
        (@CourseID        [int])
AS
begin 
    declare @sql nvarchar(1000) 
    set @sql='select * from MultiProblem where CourseID='+rtrim(@CourseID) 
    exec sp_executesql @sql
end
go
ALTER PROCEDURE [dbo].[Proc_PaperDetail]
        (@CourseID int)
AS
begin 
    declare @sql nvarchar(1000) 
    set @sql='select * from MultiProblem where CourseID=@CourseID'
    exec sp_executesql @sql,N'@CourseID int',@CourseID 
end
    go

改为以上两种方法

作者: roy_88 发布时间: 2011-11-21

SQL code

参照用法

--动态语句语法
/******************************************************************************************************************************************************
动态语句语法:exec\sp_executesql语法

整理人：中国风(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/
动态语句语法:

--方法1查询表改为动态
select * from sysobjects
exec('select ID,Name from sysobjects')
exec sp_executesql N'select ID,Name from sysobjects'--多了一个N为unicode

--方法2:字段名，表名，数据库名之类作为变量时,用动态SQL 
declare @FName varchar(20)
set @FName='ID'
exec('select '+@FName+' from sysobjects where '+@FName+'=5' )


declare @s varchar(1000)
set @s=N'select '+@FName+' from sysobjects where '+@FName+'=5'
exec sp_executesql @s--会报错


declare @s nvarchar(1000)--改为nvarchar
set @s=N'select '+@FName+' from sysobjects where '+@FName+'=5'
exec sp_executesql @s--成功


--方法3:输入参数

declare @i int,@s nvarchar(1000)
set @i=5
exec('select ID,Name from sysobjects where ID='+@i)

set @s='select ID,Name from sysobjects where ID=@i'
exec sp_executesql @s,N'@i int',@i--此处输入参数要加上N

--方法4:输出参数

declare @i int,@s nvarchar(1000)
set @s='select @i=count(1) from sysobjects'

--用exec
exec('declare @i int '+@s+' select @i')--把整个语句用字符串加起来执行

--用sp_executesql
exec sp_executesql @s,N'@i int output',@i output--此处输出参数要加上N
select @i


--方法5:输入输出

--用sp_executesql
declare @i int,@con int,@s nvarchar(1000)
set @i=5
select @s='select @con=count(1) from sysobjects where ID>@i'
exec sp_executesql @s,N'@con int output,@i int',@con output ,@i 
select @con

--用exec
declare @i int,@s nvarchar(1000)
set @i=5
select @s='declare @con int select @con=count(1) from sysobjects where ID>'+rtrim(@i)+' select @con'
exec(@s)

作者: roy_88 发布时间: 2011-11-21

答非所问；
LZ应该去C#版问下，不过，我使用的时候以前不带@ 运行正常的。

作者: gold_water 发布时间: 2011-11-21

表名或者字段名位变量的时候需要用动态SQL。

作者: fredrickhu 发布时间: 2011-11-21

SQL code


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[Proc_PaperDetail]
        (@CourseID        [int])
AS
begin 
    declare @sql nvarchar(1000)
if @CourseID=1
 begin    
    set @sql='select * from MultiProblem where CourseID=@CourseID'
    exec sp_executesql @sql,N'@CourseID int',@CourseID  --sp_executesql存储过程参数第一个需要unicode类型的SQL语句字符串，第二个参数是字符串类型，里面定义了SQL语句里的变量和类型，后面的是要传进去的变量
 end
else if @CourseID=2
 begin    
    set @sql='select * from MultiProblem where CourseID=@CourseID'
    exec sp_executesql @sql,N'@CourseID int',@CourseID

 end
else if @CourseID=3
 begin    
    set @sql='select * from MultiProblem where CourseID=@CourseID'
    exec sp_executesql @sql,N'@CourseID int',@CourseID
 end
else if @CourseID=4
 begin    
    set @sql='select * from MultiProblem where CourseID=@CourseID'
    exec sp_executesql @sql ,N'@CourseID int',@CourseID       
 end
else
 begin    
    set @sql='select * from MultiProblem where CourseID=@CourseID'
    exec sp_executesql @sql ,N'@CourseID int',@CourseID
 end
end

作者: pengxuan 发布时间: 2011-11-21

if @CourseID=1
begin
set @sql='select * from MultiProblem where CourseID=@CourseID'
exec sp_executesql @sql

这些if else有何用处？怎么判断都是执行@sql='select * from MultiProblem where CourseID=@CourseID' 。何必多这个判断。

作者: smilysoft 发布时间: 2011-11-21

SQL code


alter procedure [dbo].[proc_paperdetail] ( @courseid int )
as 
    begin 
        declare @sql nvarchar(4000)
        set @sql = 'select * from multiproblem where courseid='
            + ltrim(@courseid)   
        exec (@sql)        
    end