为什么即使参数是正确的，存储过程也不返回任何东西？

Question

我试图通过将选中的值传递给SP以返回匹配的结果，但它没有返回任何内容，但问题是，当我在查询中手动给值时，它使用 in 函数在sql中工作s.Am。

这里是我的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

Answer 1

请在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

Answer 2

调试得很好！

首先，在控制器函数上放置断点，其中数据来自ajax post。然后检查值是否正确传递。如果它是您想要的，那么检查您的sp调用函数以了解进一步的错误可能性(例如sp数据类型和发送数据类型)。如果这也是正确的话，

打开SSMS(Sql Server Management Studio)，然后从Tools Open SQL Server Profiler跟踪在您的进程上确切执行了什么查询。

我相信你自己会发现的