【Vegas原创】善用procedure写比较长且容易变动的sql语句

2年前 (2022) 程序员胖胖胖虎阿

325 0 0

Proc：完全可以胜任where子句条件查询。

alter proc procBP_Cost_Query
@Application nvarchar(50),
@TechType nvarchar(50),
@Size nvarchar(50),
@TACFilm nvarchar(50),
@Treatment nvarchar(50),
@Thickness nvarchar(50),
@ModelGroup nvarchar(50)
as
declare @chrSQL varchar(8000)

set @chrSQL='SELECT a.CostID, a.modelgroupID,CostYear as BPYear,aa.CMALPH, a.productCost,a1.Code_Name AS Application, a4.Code_Name AS TechType, a2.Code_Name AS Size,
       a6.Code_Name AS TACFilm, a5.Code_Name AS Treatment, a3.Code_Name AS Thickness, aa.PGrade as Grade,UpdateDate,UpdateUser as Updatedby
FROM   T_BPCost AS a INNER JOIN
       T_ModelGroup AS aa ON a.ModelGroupID = aa.ModelGroupID INNER JOIN
       T_CoddApplication AS a1 ON aa.PApplication = a1.Code_id INNER JOIN
       T_CoddSize AS a2 ON aa.PSize = a2.Code_id INNER JOIN
       T_CoddThickness AS a3 ON aa.PThickness = a3.Code_id INNER JOIN
       T_CoddTechType AS a4 ON aa.PTechType = a4.Code_id INNER JOIN
       T_CoddTreatment AS a5 ON aa.Treatment = a5.Code_id INNER JOIN
       T_CoddTACFilm AS a6 ON aa.PTACFilm = a6.Code_id
WHERE  2>1 '

if @Application<>'ALL'
begin
set @chrSQL=@chrSQL+' and  a1.Code_Name = '''+@Application+'''' --'转义符
end

if @TechType<>'ALL'
begin
set @chrSQL=@chrSQL+' and a4.Code_Name ='''+@TechType+''''
end

if @Size<>'ALL'
begin
set @chrSQL= @chrSQL+' and a2.Code_Name = '''+@Size+''''
end

if @TACFilm<>'ALL'
begin
set @chrSQL=@chrSQL+' and a6.Code_Name = '''+@TACFilm+''''
end

if @Treatment<>'ALL'
begin
set @chrSQL=@chrSQL+' and a5.Code_Name = '''+@Treatment+''''
end

if @Thickness<>'ALL'
begin
set @chrSQL=@chrSQL+' and a3.Code_Name = '''+@Thickness+''''
end

if @ModelGroup<>'ALL'
begin
set @chrSQL=@chrSQL+' and aa.PGrade = '''+@ModelGroup+''''
end

set @chrSQL=@chrSQL+ ' order by CMALPH'

exec (@chrSQL)

后台代码：

    void bindDataQuery()
    {
        //连接数据库,并插入数据
        SqlParameter[] sqlParam = new SqlParameter[7];

        sqlParam[0] = new SqlParameter("@Application", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "@Application", DataRowVersion.Default, drpApplication.SelectedValue.Trim());
        sqlParam[1] = new SqlParameter("@TechType", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "@TechType", DataRowVersion.Default, drpTech.SelectedValue.Trim());
        sqlParam[2] = new SqlParameter("@Size", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "@Size", DataRowVersion.Default, drpSize.SelectedValue.Trim());
        sqlParam[3] = new SqlParameter("@TACFilm", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "@TACFilm", DataRowVersion.Default, drpTAC.SelectedValue.Trim());
        sqlParam[4] = new SqlParameter("@Treatment", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "@Treatment", DataRowVersion.Default, drpTreatment.SelectedValue.Trim());
        sqlParam[5] = new SqlParameter("@Thickness", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "@Thickness", DataRowVersion.Default, drpThickness.SelectedValue.Trim());
        sqlParam[6] = new SqlParameter("@ModelGroup", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "@ModelGroup", DataRowVersion.Default, drpGrade.SelectedValue.Trim());