存储过程参数过长报错的处理

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/s15204325802/article/details/53507553

下面为具体解决方式:

(1)新建一张数据库表,用来存参数,如下图

 

(2)将参数入参数表,入时要注意不要按行入要按列入。

(3)将存储过程的查询结果与该表进行关联

注:要重点注意判空操作,在经过我的分析后,决定用下图方式进行处理

SELECT A.* FROM MHP_OEE.RESRCE AS A INNER JOIN MHP_OEE.TABLE_FOR_PARAMETER AS B ON A.RESRCE = B.RESRCE
WHERE (A.RESRCE = B.RESRCE and (select count(C.RESRCE) from MHP_OEE.TABLE_FOR_PARAMETER AS C WHERE C.LINE_AREA is not null )>0 ) 
OR (1 = 1 and (select count(C.RESRCE) from MHP_OEE.TABLE_FOR_PARAMETER AS C WHERE C.LINE_AREA is not null )=0 )

展开阅读全文

我是菜鸟!存储过程 where参数过长,执行无效,求解!!!

04-17

存储过程:rnCreate PROCEDURE SPrn(rn@Tables varchar(1000),rn@PrimaryKey varchar(100),rn@Sort varchar(200) = NULL,rn@CurrentPage int = 1,rn@PageSize int = 10,rn@Fields varchar(1000) = '*',rn@Filter varchar(1000) = NULL,rn@Group varchar(1000) = NULLrn)rnASrn/*默认排序*/rnIF @Sort IS NULL or @Sort = ''rnSET @Sort = @PrimaryKeyrnDECLARE @SortTable varchar(100)rnDECLARE @SortName varchar(100)rnDECLARE @strSortColumn varchar(200)rnDECLARE @operator char(2)rnDECLARE @type varchar(100)rnDECLARE @prec intrn/*设定排序语句.*/rnIF CHARINDEX('DESC',@Sort)>0rnBEGINrnSET @strSortColumn = REPLACE(@Sort, 'DESC', '')rnSET @operator = '<='rnENDrnELSErnBEGINrnIF CHARINDEX('ASC', @Sort) = 0rnSET @strSortColumn = REPLACE(@Sort, 'ASC', '')rnSET @operator = '>='rnENDrnIF CHARINDEX('.', @strSortColumn) > 0rnBEGINrnSET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))rnSET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))rnENDrnELSErnBEGINrnSET @SortTable = @TablesrnSET @SortName = @strSortColumnrnENDrnSelect @type=t.name, @prec=c.precrnFROM sysobjects o rnJOIN syscolumns c on o.id=c.idrnJOIN systypes t on c.xusertype=t.xusertypernWhere o.name = @SortTable AND c.name = @SortNamernIF CHARINDEX('char', @type) > 0rnSET @type = @type + '(' + CAST(@prec AS varchar) + ')'rnDECLARE @strPageSize varchar(50)rnDECLARE @strStartRow varchar(50)rnDECLARE @strFilter varchar(1000)rnDECLARE @strSimpleFilter varchar(1000)rnDECLARE @strGroup varchar(1000)rn/*默认当前页*/rnIF @CurrentPage < 1rnSET @CurrentPage = 1rn/*设置分页参数.*/rnSET @strPageSize = CAST(@PageSize AS varchar(50))rnSET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))rn/*筛选以及分组语句.*/rnrnIF @Filter IS NOT NULL AND @Filter != ''rnBEGINrnSET @strFilter = ' Where ' + @Filter + ' 'rnSET @strSimpleFilter = ' AND ' + @Filter + ' 'rnENDrnELSErnBEGINrnSET @strSimpleFilter = ''rnSET @strFilter = ''rnENDrnIF @Group IS NOT NULL AND @Group != ''rnSET @strGroup = ' GROUP BY ' + @Group + ' 'rnELSErnSET @strGroup = ''rn/*执行查询语句*/rnEXEC(rn'rnDECLARE @SortColumn ' + @type + 'rnSET ROWCOUNT ' + @strStartRow + 'rnSelect @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + 'rnSET ROWCOUNT ' + @strPageSize + 'rnSelect ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + 'rn'rn)rnGOrnrnrnrnwhere 参数:当我只留下几个xxx like 执行成功rnstring temp = "(Sarxm LIKE '%" + this.textBox1.Text + "%') OR (Xb LIKE '%" + this.textBox1.Text + "%') OR (Mz LIKE '%" + this.textBox1.Text + "%') OR (Gj LIKE '%" + this.textBox1.Text + "%') OR (Sfzhm LIKE '%" + this.textBox1.Text + "%') OR (Whcd LIKE '%" + this.textBox1.Text + "%') OR (Hzhm LIKE '%" + this.textBox1.Text + "%') OR (Yytc LIKE '%" + this.textBox1.Text + "%') OR (Jg LIKE '%" + this.textBox1.Text + "%') OR (Xzz LIKE '%" + this.textBox1.Text + "%') OR (Nc LIKE '%" + this.textBox1.Text + "%') OR (Xqah LIKE '%" + this.textBox1.Text + "%') OR (Lxfs LIKE '%" + this.textBox1.Text + "%') OR (Zz LIKE '%" + this.textBox1.Text + "%') OR (Bz LIKE '%" + this.textBox1.Text + "%')"; 论坛

jquery easyui datagrid queryParams 参数过长怎么处理

01-17

$(function () rn // var ta = $.getUrlParam('tab');rn // var jsdata = '"total":239,"rows":["StationName":"101"]';rn // var jsdata = '"total":239,"rows":["StationName":"阿右旗","sn":"52576","yesterday":"20131220","dbtsum1":-6.500,"dbtmax1":-6.500,"dbtmin1":-6.500,"wvsum1":2.200,"vpsum1":2.100,"rhsum1":56.000,"casum1":null,"lcasum1":null,"spsum1":857.900,"spmax1":857.900,"spmin1":857.900,"YD":"20131221","StationNum":"52576","dbt02":-7.900,"dbt08":null,"dbt14":null,"dbt20":null,"dbtsum2":3.700,"dbtmax2":1.300,"dbtmin2":0.600,"p20_8":null,"p8_20":null,"ptotal":null,"wd02":"359","wd08":null,"wd14":null,"wd20":null,"wv02":1.400,"wv08":null,"wv14":null,"wv20":null,"wvsum2":3.700,"vp02":2.000,"vp08":null,"vp14":null,"vp20":null,"vpsum2":7.500,"rh02":"059","rh08":null,"rh14":null,"rh20":null,"rhavg":61,"rhmin":null,"rhsum2":246.000,"v02":30.000,"v08":null,"v14":null,"v20":null,"ca02":7.00,"ca08":null,"ca14":null,"ca20":null,"lca02":0.00,"lca08":null,"lca14":null,"lca20":null,"casum2":13.00,"lcasum2":0.00,"sp02":856.200,"sp08":null,"sp14":null,"sp20":null,"spsum2":3423.800,"spmax2":856.600,"spmin2":855.100,"dbtavg":-0.116666,"dbtmax":1.300,"dbtmin":-6.500,"wvavg":0.245833,"vpavg":0.400000,"rhavg1":12.583333,"caavg":null,"lcaavg":null,"spavg":178.404166,"spmax":857.900,"spmin":855.100]';rn // var data = $.parseJSON(jsdata);rn $.ajax(rn //要用post方式 rn type: "Post",rn //方法所在页面和方法名 rn url: "TimingResult.aspx/JoinParam",rn contentType: "application/json; charset=utf-8",rn dataType: "json",rn success: function (data) rn //返回的数据用data.d获取内容 rn var vv = data.d.split(']');rnrn var colfield = vv[0];rn var cols = [];rn var cfs = colfield.split(',');rn $(cfs).each(function (index) rn var s = cfs[index].split('|');rn var col = field: s[0], title: s[1] ;rn cols.push(col);rn );rn $('#tbUsers').datagrid(rn title: '查询结果',rn width: 998,rn height: 248,rn dataType: 'json',rn loadMsg: '数据装载中......',rn fit: true, //自动大小 rn rownumbers: true, //行号 rn url: '../../../Handler/Live/GetTiming.ashx' + vv[1],rn// queryParams: s_key: '800', website: 'http://www.xxxx.cn/con.html' ,rn frozenColumns: [[rn field: 'StationName', title: '台站名称', width: 80, sortable: true ,rn field: 'StationNum', title: '台站编号', width: 80, sortable: true ,rn field: 'YD', title: '日期', width: 80, sortable: true ,rn ]],rn columns: [cols]rn // onLoadSuccess: function (data, param) rn // ,rn // onLoadError: function (data, param) rn // alert("error " + data);rn // rn );rn // $('#tbUsers').datagrid("loadData", data); //将数据绑定到DataGrid中 rn ,rn error: function (err) rn //templateData = err;rn alert(err);rn rn );rn ); rnrn这里面的vv[1]是传递的参数,如?aa='1111',但值很长怎么处理 论坛

没有更多推荐了,返回首页