Question

Photo of David Hanson

0

Calling Stored Procedure w/Parameter in Dynamic Data Block

5

Hi all,


I have a Dynamic Data Block where I'm attempting to execute a stored procedure while passing a URL parameter.  I've tested the SP call using Microsoft SQL Server Management Studio and everything works fine.  The query I'm running is, "EXEC spSundayDateGivingAnalytics @sSDate='2018-03-11' "  The expected output is given.

In RMS, though, I receive an error when I attempt to navigate to the page where I'm trying to call this stored procedure.  The error is, "Query Error! Conversion failed when converting date and/or time from character string."


Here are the details:

URL of the Dynamic Data Block page giving the error:  http://rms.mychurchrms.com/page/1544?sSunday=2018-03-18

Dynamic Data Block configuration:


DynamicDataBlock.png


I've tried setting the Parameters value to "sSDate=sSunday" as well, but the same error is displayed.

If I only set Parameters to "sSunday" of "{sSunday} then I see an empty result set -- even though executing the stored procedure in MS Management Studio returns two records, so I know for the selected date there are records that should be returning.


I'm not sure what I'm doing wrong as the documentation states that I should be setting Parameters to, "param1=value;param2=value"


Any suggestions?

  • Photo of David Hanson

    0

    I've finally fixed the issue, but not sure what I really did.  Here are the steps that I did in case others experience a similar issue:

    1.  Modifed my Stored Procedure to SELECT @sSDate

    2.  Ran the URL to trigger the SP from RMS.  Result:  no date was returned -- empty result set

    3.  Modified RMS' Dynamic Data Block's Parameter value to reflect "sSDate=sSDate;" and reran the URL.  Result:  date was returned

    4.  Modified SP to remove the SELECT @sSDate and reran the URL.  Result:  query returned the expected results.

    5.  Modified the Parameters value to remove the semicolon -- new value is now, "sSDate=sSDate" and reran the URL.  Result:  expected result set was provided.

    It seems like I'm back to where I started from with the exception that my URL parameter is named exactly like my Stored Procedure parameter.  Apparently this may be the trick going forward is to ensure the SP parameters are named the same as the URL-passed parameters.

    • Daniel Hazelbaker

      Daniel Hazelbaker

      Sorry for the wild goose chase, looking at the code again it seems the "=" part is required. So the core issue is that you were not originally passing "sSDate" but rather "sSunday" in the query string. The format of the parameters is "key=defaultvalue", so you would probably want something like "sSDate=2018-01-01" so that it would take any "sSDate" query string parameter, and if none is found use "2018-01-01" as the default value.

    • Photo of Daniel Hazelbaker

      0

      Your format for the "Parameters" is wrong.  This is for passing query string parameters. So, if your query string is "?param1=value;param2=value" then you would set the "PArameters" setting to "param1,param2". This would indicate that you want to allow "param1" and "param2" to be passed as SQL Parameters from the query string.

      If you set "PArameters" to "param1=xyz,param2=123" then you are saying you want to allow "param1" and "param2" to be passed from the query string, but if they are not provided on the query string then set "param1" to the defautl value "xyz" and "param2" to the value "123".

      So in your case, you probably want to just put "sSDate" in the Parameters, and you will then have to do something like http://rms.mychurchrms.com/page/1544?sSDate=2018-03-18

    • Photo of David Hanson

      0

      Thanks, Daniel. I modified the URL to reflect, "http://rms.mychurchrms.com/page/1544?sSDate=2018-03-18". I also modified my Dynamic Data Block "Parameters" field to only reflect "sSDate". When I attempt to run the page, I still see an empty result set. Any other suggestions that I can try?

    • Photo of Daniel Hazelbaker

      0

      If the error is gone, then it is probably something in your stored procedure. You would have to do some debugging like having it output the value of the @sSDate parameter so you can determine what value it's getting and things like that.

    • Photo of David Hanson

      0

      I modified the stored procedure to return the passed-in parameter and the result after navigating to "http://rms.mychurchrms.com/page/1544?sSDate=2018-03-18" is a blank result set.  So it would appear as though RMS is not actually populating the Parameters value of "sSDate" when it calls the SP.  Not sure how to troubleshoot this further.