时间戳

首页 > 解决方案 > 为什么即使参数正确，存储过程也不返回Nothing？

jquery - 为什么即使参数正确，存储过程也不返回Nothing？

问题描述

我试图将复选框选择的值传递给 SP以返回匹配的结果，但它不返回任何内容，但问题是当我在查询中手动给出值时它可以工作。我在 sql 中使用IN函数。

这是我的 Ajax 代码

    <script>
        $('.checked').click(function () {

            var colorss =$("#colors input:checked").map(function (i, el) { return el.name; }).get();
            var combinedstring = colorss.join("','");
            var result = "'" +combinedstring+"'";

            alert(test);
            if (this.checked) {

                $.ajax({
                    type: "POST",
                    url: 'FilterByColor',
                    data: { minimum_range: $("#minid").val(), maximum_range: $("#maxid").val(),color:result}, //--> send id of checked checkbox on other page
                    success: function (vlist) {

                        $('#card-group').empty();
                        for (var i in vlist) {

                            $('#card-group').append($('<div class="col-lg-3 col-sm-6 p-0"><div class="card product-men p-3"><div class="men-thumb-item"><img src="' + vlist[i].img1 + '" id="imgfront" class="card-img-top"><div class="men-cart-pro"><div class="inner-men-cart-pro"><a href="#" id="quickview" class="link-product-add-cart">Quick View</a>/</div></div></div><!-- card body --><div class="card-body  py-3 px-2"><h5 class="card-title text-capitalize" id="mname">' + vlist[i].mname + '</h5><div class="card-text d-flex justify-content-between"><p class="text-dark font-weight-bold" id="mprize">' + vlist[i].mprize + '</p><p class="line-through" id="mdprize">' + vlist[i].mdprize + '</p></div></div><!-- card footer --><div class="card-footer d-flex justify-content-end"><h3> <a href="Purchase/AddToCarts?mid=' + vlist[i].mid + ',mrate=' + vlist[i].mrate + '" class="hub-cart phub-cart btn">Add To Cart</a></h3></div></div></div>'));
                        }
                    },
                    error: function () {
                        alert('it broke');
                    },
                    complete: function () {
                        alert('it completed');
                    }
                });

            }
        });
</script>

这是我的SP

ALTER PROCEDURE [dbo].[getWproductByColor]
@sprize int,
@eprize int,
@color char(50)
as

BEGIN
select mid,LTRIM(RTRIM(mtype))as mtype,LTRIM(RTRIM(mname))as mname,LTRIM(RTRIM(mdetails))as mdetails,LTRIM(RTRIM(mcode))as mcode,LTRIM(RTRIM(mcolor))as mcolor,msize,LTRIM(RTRIM(mctype)) as mctype,convert(date,mdate)as mdate,mdprize,mprize,mcount,mimg1,mimg2,mimg3  from [dbo].[material] where mtype='women' and (mdprize between @sprize AND @eprize)and (mcolor IN (@color)) order by mdprize asc;
END

标签： jqueryajaxasp.net-mvc-4stored-procedures

解决方案

请在 Sql server 中创建以下函数

CREATE FUNCTION [dbo].[SplitColor] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (ColorCode Varchar(50) not null)
AS
BEGIN
    ;-- Ensure input ends with comma
    SET @InStr = REPLACE(@InStr + ',', ',,', ',')
    DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(ColorCode) VALUES (@VALUE)
END
    RETURN
END

请确保传递的字符串以单个逗号终止

您的存储过程需要一些修改

 ALTER PROCEDURE [dbo].[getWproductByColor]
    @sprize int,
    @eprize int,
    @color char(50)
    as

    BEGIN
    select mid,LTRIM(RTRIM(mtype))as mtype,LTRIM(RTRIM(mname))as 
    mname,LTRIM(RTRIM(mdetails))as mdetails,LTRIM(RTRIM(mcode))as 
    mcode,LTRIM(RTRIM(mcolor))as mcolor,msize,LTRIM(RTRIM(mctype)) as 
    mctype,convert(date,mdate)as mdate,mdprize,mprize,mcount,mimg1,mimg2,mimg3  
    from [dbo].[material]
    where mtype='women' and
   (mdprize between @sprize AND @eprize) and
   (mcolor IN (SELECT * FROM SplitColor(@color))) 
   order by mdprize asc;
    END

推荐阅读