Forums (Obsolete)


Member	naveed.anjum-moftak - 8/26/2011 1:47:31 AM Calling Stored procedure with and without parameters outside kentico CMS Hi. I am using kentico CMS API outside kentico Project. I have write some stored procedures. Some accept parameters and others are not. I want to call them in my website using kentico API. How can i do that? Any code help will be appreciated. Kind Regards, Naveed Anjum

Member

kentico_michal - 8/26/2011 2:21:11 AM

RE:Calling Stored procedure with and without parameters outside kentico CMS

Hello,

At first, I would like to point you to the following section of Developer’s guide about using API and CMS controls outside CMS project.

In terms of executing a stored procedure, you can create a store procedure in your database within: <your_database>/Programmability/Stored Procedures. To pass a parameter you need to place parameters to the array.

An example for calling parameter could be this:


object[,] parameters = new object[1, 3];
parameters[0, 0] = "@SiteID";
parameters[0, 1] = "2";

//execute stored procedure
DataSet ds = gn.ExecuteQuery("Proc_CMS_Site_Select", parameters, CMS.IDataConnectionLibrary.QueryTypeEnum.StoredProcedure, false););

Best regards,
Michal Legen

Member

naveed.anjum-moftak - 8/27/2011 4:41:00 AM

RE:Calling Stored procedure with and without parameters outside kentico CMS

Hi Michal Legen,

Thanks for your kind reply.

I have used the above mentioned procedure. However when i try to call the stored procedure with parameters, it throws an exception as follows:

[GeneralConnection.ExecuteQuery]: Query 'Store procedure name' not found.

I have verified that stored procedure exists and it also accepts the right parameters.

Can you please explain what can be the reason behind this exception?

Thanks.

Kind Regards
Naveed Anjum


Member	naveed.anjum-moftak - 8/27/2011 4:59:35 AM RE:Calling Stored procedure with and without parameters outside kentico CMS Hi Michal Legen, I have resolved the problem. I was not passing the type as stored procedure. Thanks..


Guest	Stephen - 2/1/2012 9:45:51 PM RE:Calling Stored procedure with and without parameters outside kentico CMS And how did you do that? How do you pass the type as stored procedure?

Guest

Stephen - 2/2/2012 12:08:58 AM

RE:Calling Stored procedure with and without parameters outside kentico CMS

To answer my own question, if you want to pass parameters into a stored proc then DO NOT select stored proc.

Just enter query text with EXEC before the name of the stored proc, if you stored proc needed 2 parameters called Param1 and Param2 then put this as your query text.

EXEC YourStoredProcName @Param1='banana', @Param2='99'

Guest

brian.kellogg@vinsolutions.com - 11/5/2012 3:44:56 PM

RE:Calling Stored procedure with and without parameters outside kentico CMS

Yeah, that is great and all, but how do you give it real parameters? What if those parameters are going to change based on what page you are on? I would expect this to work with the querystring macros {%querystring_key%} but that doesn't seem to work.

Can anyone expand on getting a sproc to run in a custom query repeater from a custom table by using query string parameters?


Kentico Support	kentico_jurajo - 11/7/2012 7:01:26 AM RE:Calling Stored procedure with and without parameters outside kentico CMS Hi, Where and how are you executing the stored procedure? If it is on the code behind - you can read the query string parameters there using the URLHelper class and then pass them to the procedure. Best regards, Juraj Ondrus

Guest

brian.kellogg@vinsolutions.com - 11/7/2012 8:38:47 AM

RE:Calling Stored procedure with and without parameters outside kentico CMS

Hey Juraj! I am using the custom query repeater, and I made a sproc as a custom query. The only way I know how to pass in queries to it is by running it as a query, and using the EXEC <sproc_name> @param1 'paramvalue' - this just isn't that optimal. Is there a way to pass in parameters via any sort of query string? Doing it in code is easy, but if we have a lot of parameters the sproc has to incorporate a lot of null checks.


Kentico Support	kentico_jurajo - 11/8/2012 7:17:55 AM RE:Calling Stored procedure with and without parameters outside kentico CMS Hi, Using the EXEC syntax is the only way I can see right now how to do it. Is there any reason why you cannot use the query instead of the stored procedure? Best regards, Juraj Ondrus


Guest	brian.kellogg@vinsolutions.com - 11/8/2012 8:28:01 AM RE:Calling Stored procedure with and without parameters outside kentico CMS I can't use {%query_string%} macros for my parameters. If I had preset parameters this would work great, however, I think we're making progress towards another idea. For now I will just use the QueryString in code to get to my sprocs.