SQL Server Optional parameter stored procedure with web service (.asmx) and Ajax call from front end

vatpfxk5  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(92)

I have a stored procedure with an optional parameter like this:

CREATE PROCEDURE SP_GetPData
    (@StDt Datetime,
     @EndDt Datetime,
     @Process VARCHAR(10) = NULL,
     @Item VARCHAR(50) = NULL)

Then I have a method in my web service ( .asmx ) file like this:

public string GetProdData(string stdt, string enddt, [Optional] string process, [Optional] string item)
{
    return (ConvertDataToJsonString(result));
}

I call this method from my webpage like below:

var params = new Object(); 
params.stdt = myStDt; 
params.enddt = myEndDt; 
params.process = ??; 
params.item = ??;

$.ajax (   {
       url: /myWebService.asmx/GetProdData
       ,data:JSON.stringify(params);
       ,success:function(response){}
       ,error:function(response){}   } );

How to pass the optional parameter through Ajax, or is there any way to get result with full parameters or by not providing optional parameters?

Do I need to refine the web service method or any other way out...

w9apscun

w9apscun1#

I manage to achieve this by changing my WebService(.asmx) method as follows.

public string GetProdData(Dictionary<string, string> myObj)
{
       if(myObj["process"]!=null){ /*add myObj's member as parameter in SqlAdapters Command...*/}
}

And in FrontEnd (Page Level)Javascript/JQuery as follows:

myObj.stdt = frDt;
            myObj.enddt = toDt;

            if (pro != '') {
                myObj.process = pro /*pro is local JS variable where Parameter will be stored and passed to WebService Method.*/
            } else { myObj.process = null;}

相关问题