定义存储过程:
CREATE PROCEDURE countnumbers
@number1 int output
AS
Declare @SQL NVarchar(500)
Select @SQL='select @number1=count(stu_no) from ing where total_score>=90
Exec sp_executesql @SQL
GO
c#中调用存储过程的代码:
//执行存储过程
string cnnString = "Data Source=XIAO;Initial Catalog=ingonline;Integrated Security=True";
SqlConnection con = new SqlConnection(cnnString);
SqlCommand com = new SqlCommand();
con.Open();
com.CommandType = CommandType.StoredProcedure;
//给出存储过程的名字
com.CommandText = "countnumbers";
//输出参数
com.Parameters.Add("@number1", SqlDbType.Int);
com.Parameters["@number1"].Direction = ParameterDirection.Output;
com.Connection = con;
com.ExecuteNonQuery();
label9.Text = com.Parameters["@number1"].Value.ToString().Trim();
出错信息:必须声明变量@number1
请各位高手帮忙
CREATE PROCEDURE countnumbers
@number1 int output
AS
Declare @SQL NVarchar(500)
Select @SQL='select @number1=count(stu_no) from ing where total_score>=90
Exec sp_executesql @SQL
GO
c#中调用存储过程的代码:
//执行存储过程
string cnnString = "Data Source=XIAO;Initial Catalog=ingonline;Integrated Security=True";
SqlConnection con = new SqlConnection(cnnString);
SqlCommand com = new SqlCommand();
con.Open();
com.CommandType = CommandType.StoredProcedure;
//给出存储过程的名字
com.CommandText = "countnumbers";
//输出参数
com.Parameters.Add("@number1", SqlDbType.Int);
com.Parameters["@number1"].Direction = ParameterDirection.Output;
com.Connection = con;
com.ExecuteNonQuery();
label9.Text = com.Parameters["@number1"].Value.ToString().Trim();
出错信息:必须声明变量@number1
请各位高手帮忙
CREATE PROCEDURE countnumbers
@number1 int output
AS
BEGIN
select @number1=count(stu_no) from ing where total_score>=90
END
AS
begin
Declare @sql nvarchar(4000)
set @sql='select @number1=count(stu_no) from ing where total_score>=90'
Execute sp_executesql @sql,N'@number1 int output'
return
end
可以 先试试你的存储过程能不能正常运行
@number1 int output
AS
Declare @SQL NVarchar(500)
Select @SQL='select @a=count(stu_no) from ing where total_score>=90
exec sp_executesql @sqls,N'@a int output',@number1 output
select @number1
GO--动态SQL语法
3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
@number1 int output
AS
Declare @SQL NVarchar(500)
Select @SQL='select @a=count(stu_no) from ing where total_score>=90 '
exec sp_executesql @sqls,N'@a int output',@number1 output
select @number1
GO
@number1 int output
AS
--Declare @SQL NVarchar(500)
Select @number1='select count(stu_no) from ing where total_score>=90'
--Exec sp_executesql @SQL
GO