var strSQL = new StringBuilder();
strSQL.Append("declare @rodno varchar(50) ");
strSQL.Append("set @rodno = @GetRod ");
strSQL.Append("SELECT 'aaa' AS Type,a.rodNO as RodNO,a.starttime AS Starttime,b.endtime AS Endtime ");
strSQL.Append("FROM PRO_COREVAD_BEGIN a,PRO_COREVAD_END b ");
strSQL.Append("WHERE a.rodNO=@rodno ");
strSQL.Append("AND a.rodNO=b.rodNO ");
strSQL.Append("union ");
strSQL.Append("SELECT 'bbb' AS Type,a.rodNO,a.starttime,b.endtime ");
strSQL.Append("FROM PRO_Coresinter_BEGIN a,PRO_Coresinter_END b ");
strSQL.Append("WHERE a.rodNO LIKE (@rodno+'%') ");
strSQL.Append("AND a.rodNO=b.rodNO ");
strSQL.Append("union ");
strSQL.Append("SELECT 'ccc' AS Type,c.rodNo,a.starttime,b.endtime ");
strSQL.Append("FROM PRO_CoreVacuumdegass_BEGIN a,PRO_CoreVacuumdegass_END b,PRO_CoreVacuumdegass_BatchInfo c ");
strSQL.Append("WHERE c.rodNO LIKE (@rodno+'%') ");
strSQL.Append("AND a.batchid=b.batchid AND a.batchid=c.batchid AND c.batchid = (SELECT MAX(batchid) ");
strSQL.Append("FROM PRO_CoreVacuumdegass_BatchInfo ");
strSQL.Append("WHERE rodNo LIKE (@rodno+'%')) --ddd");
SqlParameter[] parameters = {
new SqlParameter("@GetRod", SqlDbType.VarChar,50)
};
parameters[0].Value = rodno;
var ds = SQLHelper.GetDs(strSQL.ToString(), parameters);
我想用一个@GetRod值给SQL里面所有的@rodno赋值,但是返回的数据库只有一行数据,求解释
strSQL.Append("declare @rodno varchar(50) ");
strSQL.Append("set @rodno = @GetRod ");
strSQL.Append("SELECT 'aaa' AS Type,a.rodNO as RodNO,a.starttime AS Starttime,b.endtime AS Endtime ");
strSQL.Append("FROM PRO_COREVAD_BEGIN a,PRO_COREVAD_END b ");
strSQL.Append("WHERE a.rodNO=@rodno ");
strSQL.Append("AND a.rodNO=b.rodNO ");
strSQL.Append("union ");
strSQL.Append("SELECT 'bbb' AS Type,a.rodNO,a.starttime,b.endtime ");
strSQL.Append("FROM PRO_Coresinter_BEGIN a,PRO_Coresinter_END b ");
strSQL.Append("WHERE a.rodNO LIKE (@rodno+'%') ");
strSQL.Append("AND a.rodNO=b.rodNO ");
strSQL.Append("union ");
strSQL.Append("SELECT 'ccc' AS Type,c.rodNo,a.starttime,b.endtime ");
strSQL.Append("FROM PRO_CoreVacuumdegass_BEGIN a,PRO_CoreVacuumdegass_END b,PRO_CoreVacuumdegass_BatchInfo c ");
strSQL.Append("WHERE c.rodNO LIKE (@rodno+'%') ");
strSQL.Append("AND a.batchid=b.batchid AND a.batchid=c.batchid AND c.batchid = (SELECT MAX(batchid) ");
strSQL.Append("FROM PRO_CoreVacuumdegass_BatchInfo ");
strSQL.Append("WHERE rodNo LIKE (@rodno+'%')) --ddd");
SqlParameter[] parameters = {
new SqlParameter("@GetRod", SqlDbType.VarChar,50)
};
parameters[0].Value = rodno;
var ds = SQLHelper.GetDs(strSQL.ToString(), parameters);
我想用一个@GetRod值给SQL里面所有的@rodno赋值,但是返回的数据库只有一行数据,求解释
delphi的sql参数好像是 :参数名
这样拼凑应该可以吧:
strSQL.Append("set @rodno = "+rodno+" ");
则必须decalre过的
看你的需求,好像@getrod是一次性接受程序的值给sql
拼凑一下就行了,不用专门@getrod
strb.Append("set @x=@y ");
strb.Append("select * from (select 3 b Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 4 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 5 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 3 Union all ");
strb.Append("select 6 Union all ");
strb.Append("select 7 ) a where b=@x "); GridView1.DataSource= DbManagerSQL.Query(strb.ToString(), new SqlParameter[] { new SqlParameter("@y",3) });
GridView1.DataBind();
测试了,不是delcare的问题,是你的查询语句逻辑问题,可将SQL语句放在SqlServer执行一下