求:复合查找的存储程序给我 ,比如把下面代码写成存储语句: DataView CreateDataSource()
{
string nowDSN=ConfigurationSettings.AppSettings["data"];
SqlConnection myConnection=new SqlConnection(nowDSN);
str1="SELECT COUNT(*) as co from qarpt where section='"+Session["section"]+"' ";
string sql2="select * from qarpt where section='"+Session["section"]+"' ";string sql1,sql2;
sql2="select * from qarpt where section='"+Session["section"]+"' ";
sql1="SELECT COUNT(*) as co from qarpt where section='"+Session["section"]+"' ";
string s1="",s2="";
s1=Request.Form["radio"];
if(s1!=""&&s1!=null)
{
sql1=sql1+" and radio='"+s1+"'";
sql2=sql2+" and radio='"+s1+"'";
}s1=Request.Form["name2"];
if(s1!=""&&s1!=null)
{
sql1=sql1+" and name2='"+s1+"'";
sql2=sql2+" and name2='"+s1+"'";
}
}sql2=sql2+" order by time2 desc"; SqlDataAdapter myCommand=new SqlDataAdapter(sql2, myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, "qarpt");
MyList.DataSource = ds.Tables["qarpt"].DefaultView;
return ds.Tables["qarpt"].DefaultView;
}
{
string nowDSN=ConfigurationSettings.AppSettings["data"];
SqlConnection myConnection=new SqlConnection(nowDSN);
str1="SELECT COUNT(*) as co from qarpt where section='"+Session["section"]+"' ";
string sql2="select * from qarpt where section='"+Session["section"]+"' ";string sql1,sql2;
sql2="select * from qarpt where section='"+Session["section"]+"' ";
sql1="SELECT COUNT(*) as co from qarpt where section='"+Session["section"]+"' ";
string s1="",s2="";
s1=Request.Form["radio"];
if(s1!=""&&s1!=null)
{
sql1=sql1+" and radio='"+s1+"'";
sql2=sql2+" and radio='"+s1+"'";
}s1=Request.Form["name2"];
if(s1!=""&&s1!=null)
{
sql1=sql1+" and name2='"+s1+"'";
sql2=sql2+" and name2='"+s1+"'";
}
}sql2=sql2+" order by time2 desc"; SqlDataAdapter myCommand=new SqlDataAdapter(sql2, myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, "qarpt");
MyList.DataSource = ds.Tables["qarpt"].DefaultView;
return ds.Tables["qarpt"].DefaultView;
}
CREATE procedure 过程名称
@Section nvarchar(50) --你自己写吧,不知道你怎么定义的
@radio nvarchar(50)
@Name2 nvarchar(50)
AS
Declare @StrSqlCount nvarchar(2000)
Declare @strSqlContent nvarchar(2000)SET @StrSqlCount='SELECT COUNT(*) as co from qarpt where section=@Section'
SET @strSqlContent='SELECT * From qarpt where section=@Section'IF @radio is not null
BEGIN
SET @StrSqlCount=@StrSqlCount+'AND radio=@radio'
SET @strSqlContent=@StrSqlCount+'AND radio=@radio'
END
IF @Name2 is not null
BEGIN
SET @StrSqlCount=@StrSqlCount+'AND Name2=@Name2'
SET @StrSqlContent=@StrSqlContent+'AND Name2=@Name2'
ENDSET @StrSqlContent=@StrSqlContent+' order by time2 desc'exec sp_executesql @SqlStr,N'@ServiceID int',@ServiceID
(
@radio nvarchar(50)=null
)
as
begin
declare
@sql1 nvarchar(100),
@sql2 nvarchar(100)
set @sql1 = 'select count(*) as co from qarpt where id is not null '
set @sql2 = 'select * from qarpt where id is not null '
if(@radio is not null)
begin
set @sql1 = @sql1 + ' and radio =''' + @radio + ''''
set @sql2 = @sql2 + ' and radio =''' + @radio + ''''
end
execute(@sql1)
end
GO
--------------------------------------------------------------------
string nowDSN=ConfigurationSettings.AppSettings["data"];
SqlConnection myConnection=new SqlConnection(nowDSN);
SqlDataAdapter myCommand = new SqlDataAdapter("look6",myConnection);myCommand.SelectCommand.Parameters.Add(new SqlParameter("@radio", SqlDbType.Char));
myCommand.SelectCommand.Parameters["@radio"].Value = Request.QueryString["radio"];
myCommand.SelectCommand.CommandType=CommandType.StoredProcedure;DataSet ds = new DataSet();
myCommand.Fill(ds, "qarpt");
return ds.Tables["qarpt"].DefaultView;
DataBind(); 为什么老错:例外詳細資訊: System.Data.SqlClient.SqlException: 程序 'look6' 預期使用未提供的參數 '@radio'。