Questions tagged [stored-procedures]

Callable code installed on a database manager, exposing an API through which it can be invoked. Normally written in the native query language, some DBMS platforms support other languages as well.

Filter by
Sorted by
Tagged with
0
votes
1answer
26 views

psycopg2 connect error: cannot pass more than 100 arguments to a function?

I am trying to pass json object as a parameter to my stored procedure. I tried many things and solved couple of error atlast I am stuck with the error : Error : psycopg2 connect error: cannot pass ...
-1
votes
0answers
19 views

Issues with store procedure and system [closed]

I take this opportunity to send you a greeting and I comment you, Im working in a store procedure using system inside of MySQL but in workbench, it appears me, missing ;, extraneous input. These are ...
0
votes
1answer
23 views

Stored Procedure Signed by Certificate Unable to Add Members to Database Roles

I created a Stored Procedure that performs the Following tasks: Creates database Change owner to sa Set recovery model Creates users in database Adds users to database roles. Grants execute and view ...
0
votes
1answer
13 views

MariaDB: How can I grant read-only privileges for Stored Procedures?

I've granted SELECT privileges for a user to select on all tables of a database. However that user is unsable to view store procedures. I'd want to grant only the permission for SHOW CREATE PROCEDURE (...
0
votes
1answer
16 views

How to see the original text of the the function that has been wrapped by “DBMS_DDL.WRAP”

As I'm new to Oracle PL-SQL,I'm trying to be familiar with DBMS_DDL package and I used it to hide my source code,below you can see the example I'm working on: Declare v_string varchar2(32676); begin ...
0
votes
0answers
30 views

How to write a complex logic in SQL that should work two main cases (unfiltered and filtered by one dimension)?

This question is slightly related to this one. I am dealing with a legacy business logic that computes several DocumentXUser security tables. This logic is very complex (a single query with lots of ...
0
votes
3answers
48 views

Stored procedure runs fine when executed manually but fails in scheduled job

I have a stored procedure that runs fine when executed manually but fails in scheduled job. The error I receive is: Executed as user: \USER-a. Login failed for user '\USER-a'. [SQLSTATE 28000] (Error ...
0
votes
0answers
62 views

Cannot find column “ssma_oracle” user-defined function or aggregate “ssma_oracle.db_get_next_sequence_value”. The name could also be ambiguous

We have migrated our database from Oracle to SQL Server, but when we have open any trigger we have this error in line this syntax ssma_oracle. db_get_next_sequence_value when I change with this syntax ...
0
votes
0answers
5 views

How metadata can be generated using Stored procedure in snowflake in Generic

I have requirement like user is going to place the different columns csv file each time.I want to generate metadata for each csv file to load the data into snowflake tables I need help to resolve ...
0
votes
0answers
16 views

How to pass query string containing variable names into mysql stored procedure

I have this procedure into which I need to pass variable parameters. Problem is this: once inside the procedure, the IN parameter needs to pick up already created variables in order to create the ...
1
vote
2answers
41 views

How to update group no incremental by 1 based on every group from 1 to 3?

I work on SQL server 2012 I face issue i can't update group no to every steps from 1 to 3 meaning i need every group from 1 to 3 take identity number incremental by 1 as 1,2,3 etc .. create table #...
0
votes
1answer
69 views

Getting the “Column name or number of supplied values does not match table definition” when I use CTEs in an IF statement inside a stored procedure

I have a Stored Procedure that has 3 parameters. The stored procedure looks like this: ALTER PROC [dbo].[Trans_sp_test] (@Load bit = '',@ShowMsg bit = '', @File bit = '') AS SET NOCOUNT ON DECLARE @...
0
votes
1answer
24 views

SQL Server Distributed Transactions: xp_sqljdbc_xa_commit vs xp_sqljdbc_xa_end

In SQL Server 2019, can anyone definitively tell me the functional difference between xp_sqljdbc_xa_commit & xp_sqljdbc_xa_end? My guess is that commit is attempting to commit the transaction (...
0
votes
1answer
44 views

What is the best way to add optional update in procedure in transaction?

Currently I have a procedure that performs 4 operations in the Transaction: Update record in table A Update record in table B Insert record into table C Delete record from table D The first ...
0
votes
1answer
12 views

Return inserted entity in mysql stored procedure

I want to have inserted data in stored procedure, because of of sql injection I use prepare and execute statement. what should I've done to have the inserted data following code is only a part of ...
2
votes
2answers
40 views

How to do a recursive sum of children in mysql?

I've searched and tried for long hours, and can't seem to get what I need. I have a table of accounts as a tree -parents and children-, only leaf accounts can have transactions, any parent will NOT ...
0
votes
0answers
44 views

Internal error: An expression services limit has been reached [duplicate]

In debugging a problem the application is throwing the error: Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to ...
3
votes
2answers
279 views

Does changing a parameter value in a stored procedure before the query affect the cardinality estimate?

I routinely "scrub" the parameters of my stored procedures at the top before I run the query like this: -- Scrub params SET @SearchText = NULLIF(@SearchText, '') SET @ThreadId = NULLIF(@...
1
vote
1answer
25 views

Unable to save a stored procedure which contains a temporary table creation and update query

I am unable to save my procedure in Postgres. The procedure contains a temporary table creation and update query. Here is my query: CREATE OR REPLACE PROCEDURE account_report_v2() LANGUAGE SQL AS $$ ...
0
votes
1answer
65 views

Is this SP safe to SQL Injection?

CREATE PROCEDURE [sp_Test] ( @param nvarchar(Max) ) AS BEGIN DECLARE @Output nvarchar(Max) = N’Select ‘ + @param Select @output Return Intended Use exec sp_test ‘5’ Returns “select 5” ...
-2
votes
2answers
44 views

How do i use an IF statement in my procedure?

This is my procedure CREATE OR REPLACE PROCEDURE INSERT_ALL AS BEGIN INSERT_INTO_P_ENZYME; INSERT_INTO_P_EXPR_SYSTEM; INSERT_INTO_P_BUFFER_SYSTEM; INSERT_INTO_0_CONSTRUCT; INSERT_INTO_1_PRODUCED; ...
0
votes
1answer
23 views

Perl Script to call Oracles stored procedure with input parameter and OUTPUT parameter ,CURSOR

I am finding difficulty in fetching the records from stored procedure(oracle) by invoking it through perl script. SP takes 1 input and ouput parameter is cursor --------Here is the perl script_-----...
1
vote
1answer
34 views

Why should I renew stored procedures?

Is there a reason significant reason to recompile stored procedures? Of course the data in a database changes over time and the resultset of some stored procedures could differ due to changed ...
0
votes
1answer
54 views

Why can't I make this insert statement a procedure?

The insert statement works, but when I try to make it a procedure it doesn't compile. CREATE OR REPLACE PROCEDURE INSERT_INTO_CPP AS BEGIN INSERT INTO cpp /*+ ignore_row_on_dupkey_index ( cpp ( ...
-3
votes
1answer
42 views

Best way to update a Table

I have a SP that updates rows in a table periodically (several times a day) the SP currently gets a feed of the PK and the update value. The input feed can contain 1 - 1000 rows. I want to know if it ...
0
votes
1answer
49 views

Oracle: Is there a way to put exception handling into a function and just reference that function inside procedures?

If you were going to write 10 procedures that all have the same exceptions, you'd want to just reference the procedures defined somewhere else, right? You can call a function inside a procedure create ...
1
vote
1answer
42 views

Error when using OPENROWSET from a View as a nonsysadmin

[SQL Server 2012] I am running into an issue regarding the usage of a non-sysadmin account calling a view which contains a OPENROWSET call that pulls from a stored procedure. I was wondering whether ...
-1
votes
1answer
22 views

Create store procedure to disable and enable indexes

I have two below queries to, first of all, disable indexes and in another query enable them. Here are my queries --Disabling indexes DECLARE @my_sql2 NVARCHAR(200); DECLARE cur_rebuild CURSOR FOR ...
0
votes
1answer
26 views

Stored procedure not displaying data when null is passed into input parameter

I recently started learning stored procedures in MySQL and created a procedure like this: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getAllDetails`(IN _student_no INT, IN _class_id INT, IN ...
0
votes
1answer
72 views

Execute privilege granted successfully, but I can't execute the procedure

I am using the database as me. The procedure exists as an object that belongs to me. Grant sucessful. But when I attempt to EXECUTE insert_into_purified; i get Error starting at line : 469 in ...
1
vote
1answer
40 views

MYSQL Stored procedure to fetch entire row from a table

I am a beginner to SQL and currently practicing the concept of the stored procedure. I have a table Fetch_Marks that contains 20 records with Student_No and Marks as columns. Now I would like to ...
0
votes
0answers
60 views

How to insert variable number of rows inside an SQL Function with multiple queries?

My original idea was to use a transaction, since we have three statements and I want all or none. The transaction block looks something like below: BEGIN; INSERT INTO analysis_record (user_id,...
1
vote
0answers
52 views

How can I write procedures that I can loop over to insert a series of files into my database?

My goal: Insert a series of csv files into my database by creating procedures for each individual table and then looping over them. My csv files will all be named very similar to this: ...
0
votes
0answers
15 views

Is there a portable profile/well defined subset of SQL/PSM?

https://en.wikipedia.org/wiki/SQL/PSM says that DB2's SQL PL and Mimer SQL's PSM implement the SQL/PSM standard and that the MySQL/MariaDB is close while HSQLDB stored procedures, OpenLink Virtuoso ...
0
votes
1answer
32 views

Is there a better way of displaying 'Count' of records beside other columns in a select query?

I have a table with below structure : Test1(c_num number ,c_first_name varchar2(50), c_last_name varchar2(50)) 1)There is a normal index on c_num column. 2)The table has nearly 5 million records. I ...
1
vote
2answers
50 views

How to use 'Bind Variable' with 'With clause' in a Dynamic query using PLSQL

I have three tables with these structures: 1) z_test_a(c_num number , <other columns>) 2) z_test_b(c_num number , Amount number , <other columns>) 3) z_test_b(c_num number , Amount ...
0
votes
2answers
99 views

MySQL Call Stored Procedure along with an UPDATE statement

I am pretty much convinced that this is not possible but again I wanted to see if there is a possibility. I have an UPDATE statement and I want to call a SP for each row updated. Is it possible ...
0
votes
2answers
98 views

How to use 'Bind Variables' in a 'Dynamic Query ' when the exact number of variables are not known

I have a procedure in which I'm using Dynamic SQL(variable actual_query) and I'm generating this query based on the input parameter i_tables which is a concatenation of the name of some tables. It has ...
0
votes
1answer
42 views

How to display the exact query in the output section of a stored procedure when using 'Dynamic SQL' and 'Bind variables' in Oracle

I have a Procedure as you can see below. In this procedure I have a dynamic querywhich is qry variable and I'm using Bind variable in order to eliminate the risk of injection and improve performance: ...
1
vote
1answer
18 views

How to both reference and return a result set from a stored procedure in mysql?

I have read that a select query that stores a result into variables does not return a result set. Is there an efficient way to do both? BEGIN #return this and also use it in the following query ...
2
votes
1answer
48 views

Raise application error in a stored procedure when inputs contain keywords like 'delete' , 'insert','update'

I have a Stored Procedure which gets 6 input queries and the inputs are the same as where clause of a query. For instance where x=1 and z=5 and e=4. The Application makes these input strings and pass ...
-2
votes
1answer
49 views

Same query performance problems when using ORDER BY [closed]

I have query that uses a few CTEs. The final output query joins the CTEs together. When I ran the query with no ORDER BY, it runs in seconds. When I ran it with an ORDER BY, it takes several minutes ...
1
vote
1answer
25 views

Mysql How to update n rows using count function?

In the following table, I want to set delete = true if the total records for same orgid exceed 500 and I want to do it according to createdate such that if records exceed 500 the old records get ...
0
votes
1answer
26 views

MySQL: After inserting data into a table, the table remains empty

i'm trying to run this simple query: insert into diff_set_values(value) select value from diff_sets group by value; but after it is finished, there is no data in the destination table ...
-1
votes
1answer
27 views

Running total for current period when previous period is empty

I could use a little SQL help this morning I am new to SQL and could use some help. I have a stored proc set up in SSMS whose purpose is to determine the quarter to date total of transactions for a ...
0
votes
1answer
39 views

Postgres function/procedure won't return value

I'm new to PostgreSQL, coming from SQL server and I'm having trouble creating a function and/or procedure. First of all, may I just clarify that the only difference between a function and a procedure ...
-1
votes
1answer
28 views

Stored Proc hangs when no rows exist in cursor? [closed]

I'm on 8.0.19 if that matters. Here is my test proc: DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `testNullProc`() main:BEGIN DECLARE fileID INTEGER; DECLARE finished INTEGER ...
1
vote
0answers
24 views

How to write Procedure to fetch data from other table?

drop procedure if exists findClosestTimeStamp; delimiter $$ create procedure findClosestTimeStamp() begin declare tt1 time; declare dt1 date; declare view1 varchar(30); declare done int ...
0
votes
1answer
48 views

Moving data from staging table to production table uses up to 200GB of Log space

I have multiple staging tables containing data that needs to be moved to production tables. The process of moving this data is done within a transaction to make it possible to rollback everything in ...
0
votes
1answer
37 views

Dynamic SQL breaks statement

I am attempting to write a query that pivots a certain column that may have any number of distinct values in it. I am able to get this to work with static values, but when I try to parametrize the ...

1
2 3 4 5
30