# Getting error while creating store procedure by named query **URL:** \ **Category:** Ignition\ **Created:** [June 18, 2025, 6:01pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542 "2025-06-18T18:01:30Z")\ **Posts on this page:** 11\ **Page:** 2
**Author:** ![dhiraj.pawar](https://avatars.discourse-cdn.com/v4/letter/d/3ec8ea/32.png) [@dhiraj.pawar](https://forum.inductiveautomation.com/u/dhiraj.pawar)\ **Post date:** [June 24, 2025, 5:33am UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/21 "2025-06-24T05:33:28Z")
Hello, Currently we are looking for dynamically solution which will be work like Calculation engine(Which generate different formulas dynamically ) I this case we divided 2 approch 1. Creating simple querries by step 1 2. Based on simple queries creating store procedure for individual parameters required for generating final result/Reports. So step by step user will configure parameters and with required operations & map into sequential manner by store procedure Then created trigger which will executing store procedure (Parametrs) in defined sequence & generate reports This is final goal. While creating store procedure we are passing variables , query & calculation required for generate store procedure ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 24, 2025, 11:33am UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/22 "2025-06-24T11:33:51Z")
Here's the most reasonable way to do it though I am still **wholeheartedly** against giving Ignition a user account that can create/drop tables or otherwise is the db\_owner/root due to security risk involved (if your ignition server gets owned, then so now is your database immediately). I tried this on my local sql server and it worked. Create update named queries like `createTable` ```python CREATE TABLE cm_shiftSchedule ( scheduleID INT IDENTITY(1,1) PRIMARY KEY, entityID INT NOT NULL, shiftDay DATE NOT NULL, pdtCode VARCHAR(10) NOT NULL, pdt VARCHAR(100), Duration DECIMAL(16,4) NOT NULL, createdAt DATETIME DEFAULT GETDATE() ) ``` and `createSP` ```python CREATE PROCEDURE PlannedDowntime @Int INT, @Int1 INT, @Result DECIMAL(16,4) OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE @Result_TPM DECIMAL(16,4); SET @Result_TPM = ( SELECT SUM(Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = CONVERT(DATE, GETDATE()) AND pdtCode = 'TPM' AND pdt IS NOT NULL ); DECLARE @Result_Break DECIMAL(16,4); SET @Result_Break = ( SELECT SUM(Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = CONVERT(DATE, GETDATE()) AND pdtCode = 'BRK' AND pdt IS NOT NULL ); DECLARE @Result_WGBT DECIMAL(16,4); SET @Result_WGBT = ( SELECT SUM(Duration) FROM cm_shiftSchedule WHERE entityID = 13 AND shiftDay = CONVERT(DATE, GETDATE()) AND pdtCode = 'WGBT' AND pdt IS NOT NULL ); SET @Result = ( ISNULL(@Result_TPM, 0) + ISNULL(@Result_Break, 0) + ISNULL(@Result_WGBT, 0) ); RETURN @Result; END; ``` Then you can run ```python system.db.runNamedQuery("createTable") system.db.runNamedQuery("createSP") ``` and it will create your table/stored procedures. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 24, 2025, 11:57am UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/23 "2025-06-24T11:57:37Z")
> [@bkarabinchak.psi](#): > > user account that can create/drop tables Permissions for DDL are necessary for the tag historian and usually for the SQL Bridge module (unless you pre-create everything yourself). ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 24, 2025, 12:02pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/24 "2025-06-24T12:02:33Z")
Yea fair enough. Suppose that is necessary for new historian tables. That still happens in the background though and managed by the server unaccessible by the userbase, not in a vision/perspective UI like OP is asking for. ---
**Author:** ![dhiraj.pawar](https://avatars.discourse-cdn.com/v4/letter/d/3ec8ea/32.png) [@dhiraj.pawar](https://forum.inductiveautomation.com/u/dhiraj.pawar)\ **Post date:** [June 24, 2025, 4:04pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/25 "2025-06-24T16:04:06Z")
Hello @bkarabinchak.psi , Can you help this approach in detail How you created name query & passed parameter to generate table or store procedure ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 24, 2025, 4:16pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/26 "2025-06-24T16:16:03Z")
Make a query string parameter. > **[Named Query Parameters | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/8.1/platform/sql-in-ignition/named-queries/named-query-parameters)** > > Parameters allow you to make Named Queries dynamic. They act as placeholders you can pass values into when requesting the query to execute. Other resources in Ignition can then pass arguments into the parameters. The exact implementation depends on... ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 24, 2025, 5:01pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/27 "2025-06-24T17:01:37Z")
> [@bkarabinchak.psi](#): > > Make a query string parameter. Not a good idea for this. Named queries get screwed up by SQL comments much worse than "Prep" methods. The prep methods pass the entire SQL as a **statement** to the JDBC driver. NQ calls do parameter munging first. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 24, 2025, 5:51pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/28 "2025-06-24T17:51:48Z")
I already don't tink this entire thing is a good idea so 🤷 I still think the best method is to predefine their stored procedures in a .sql file one time and run that via SSMS. Having said that I did just test this with query strings and it did create a new table and stored proceudre. I'm sure there are instances where it would fail but just new names of tables and sotred procedures (which seems to be OP's aim) worked for me. ---
**Author:** ![dhiraj.pawar](https://avatars.discourse-cdn.com/v4/letter/d/3ec8ea/32.png) [@dhiraj.pawar](https://forum.inductiveautomation.com/u/dhiraj.pawar)\ **Post date:** [June 24, 2025, 6:24pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/29 "2025-06-24T18:24:13Z")
Hi, For trail purpose i setup your query in name query but it getting procedure keyword error Can you please help with that ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/3/73510e4a463d94de6d1487d023ba680925414c7d.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/7/e7d65c1b573af66b1b99e5a731720260e4430bd7.png) ---
**Author:** ![michael.flagler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/michael.flagler/32/26272_2.png) [@michael.flagler](https://forum.inductiveautomation.com/u/michael.flagler)\ **Post date:** [June 25, 2025, 3:54am UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/30 "2025-06-25T03:54:22Z")
I think all of your parameters for this will need to be query strings, not value parameters. I think that's what the error is referring to. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 25, 2025, 12:06pm UTC](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542/31 "2025-06-25T12:06:35Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/0/2026b0feb0679adb44d50b32192016f9d1152a3d.png) [Previous page](https://forum.inductiveautomation.com/t/getting-error-while-creating-store-procedure-by-named-query/107542.md?page=1)