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
2answers
35 views

procedure not returning desired result

I am trying to generate a report from this procedure create procedure [dbo].[hstatement] ( @fromdate date, @todate date, @prd varchar(8) ) as begin create table #tempdata ( ...
3
votes
2answers
67 views

Display line number where error occurred in DB2 stored procedure

I have added an EXIT handler in my procedure that captures the SQLSTATE and SQLCODE, and even found a way to get the procedure name, but I also need to know where the error occurred. Suggestions ...
0
votes
1answer
26 views

Postgres 12: Inserting array of composite types, composite type has an array field. Malformed array literal, unexpected end of input

I'm having an issue with inserting a simple array of integers into my table in Postgres via a procedure. The array of integers is declared within a composite type and I'm trying to insert an array of ...
0
votes
1answer
28 views

sql server stored procedure - collection of data for fields as input

I have a stored procedure to add a collection of values to a field in table. How I implement it now: from my .net app, I create a string of comma separated values from an array( eg: resource1, ...
1
vote
2answers
50 views

Stop SQL job if it runs past a certain time

I have a SQL job which executes a stored procedure. The proc loops through a number of databases, then executes a series of dynamic SQL queries to archive/delete a bunch of tables in each database. ...
0
votes
3answers
43 views

DB2, How to save all stored procedures as files

How does one extract all stored procedures from a DB2 schema as individual files (without using Data Studio so it can be scripted). I need them all so I can uploaded to source control. I have found ...
2
votes
3answers
54 views

Retain indent formatting when compiling a stored procedure without Data Studio

When I compile a stored procedure using CLPplus console (or even in dBeaver), all indentation is stripped out. Only when I compile in Data Studio is the formatting retained, but I would like to ...
-1
votes
0answers
56 views

Running a stored procedure in a loop crash before ending [closed]

For reasons of performance, I need to run in parallel multiple stored procedures (actually, the same SP, with different parameters) and combine the results in code. It is not possible to read all the ...
1
vote
1answer
32 views

MySQL: SELECT to local variable in stored procedure

I have the following mysql stored procedure: DELIMITER // CREATE PROCEDURE INSERT_INTO_WORKDAY(IN deviceName VARCHAR(16), IN cardUID VARCHAR(14)) BEGIN SET @...
0
votes
1answer
20 views

Procedure with “bind varibale” does not return the desired result set

I have a table with below structure: create table CUST_MODERN_CHANNEL_BRANCH ( customer_Num NUMBER, channel_Code NUMBER(5) ) Some example data : Customer_Num Channel_Code ========...
0
votes
1answer
23 views

How do you call an external C function from a SQL stored procedure?

In Teradata 17, I am trying to call their own hash_md5 external function from my own SQL stored procedure: replace procedure p() sql security creator begin declare l_hash varchar(32); set l_hash = ...
0
votes
1answer
38 views

What is the format of EXTERNAL NAME argument?

When a Java class cl with a method mth resides in a package pck, what should I put into the EXTERNAL NAME clause of CREATE PROCEDURE? I tried CREATE PROCEDURE mth (arg1 INTEGER) LANGUAGE JAVA EXTERNAL ...
0
votes
1answer
22 views

Generate script for all stored procedures in Azure SQL database

I'm trying to create a SQL script that will create all the stored procedures that were exported from another database. Basically navigating in SSMS to the database > Generate Scripts > select ...
0
votes
2answers
44 views

Passing database name to input parameter from a stored procedure

I have some issue with this application. I know that there are a lot of topics about passing a Database name as input but still I couldn't figure out what is the issue my code. Hopefuly, you guys can ...
0
votes
0answers
27 views

mysql multiple transaction rollback problem

I want to have multiple procedure call and one transaction commit, for example: call CreateRestaurant(?, ?, ?); call CreateMenu(?, ?, ?, ?, ?); call CreateMenuItem(?, ?, ?, ?); but I want to wrap all ...
0
votes
1answer
21 views

Write Oracle proc/function to create dynamic views with changing column headers

I had similar requirement earlier but now i have been given a clear approach that needs to be implemented. I need to write a procedure(preferably)/function, which would on basis of app_id passed as ...
0
votes
1answer
27 views

PLS_INTEGER giving Error

My procedure giving numeric overflow error: ERROR at line 1: ORA-01426: numeric overflow ORA-06512: at "HDM_DBUSER2.HDM_CLEANUP_UTIL", line 1196 because it exceeds pls_integer maximum ...
2
votes
1answer
61 views

Script to backup TDE certificate and Key throws error Cannot write into file

I am attempting to automate TDE key backups. I created the stored procedure below and was going to schedule it to be executed as a sql agent job. However, when I execute the procedure I get this ...
2
votes
2answers
61 views

Why MySQL performs so poor on SHOW PROCEDURE STATUS?

I've installed MySQL server with many databases that use the InnoDB engine. With lots of procedures in each DB, mysql.proc table contains more than 250,000 rows and it becomes ultimately slow when I ...
0
votes
1answer
27 views

Dynamic execution query produces error when called from the procedure, but works when executed manually

create procedure createTable(@tableName varchar(25), @columnName varchar(25), @columnType varchar(25)) as begin declare @sqlQuery as varchar(MAX) set @sqlQuery = 'create table ' + @...
1
vote
0answers
36 views

Recursively list dependencies / references of a stored procedure

I'm trying to import a subset of our production dump with impdp for our local development database running on Oracle XE. To find out what other objects I have to import for a particular stored ...
0
votes
0answers
22 views

What C# type is the correct representation for DB2 UDB boolean?

The project that I work on uses IBM.Data.DB2 nuget package for its data layer. I need to call into utl_mail.send_attach_varchar2 stored procedure on the DB2 side. It has a boolean parameter att_inline ...
0
votes
0answers
17 views

Not able to create a stored procedure for a table insert in DB2 UDB 10.5 [duplicate]

I want to create a very simple stored procedure that will only take several IN parameters and insert them into a table. To learn how to do it, I went to CREATE PROCEDURE (SQL) statement From that I ...
3
votes
2answers
47 views

At my wits end trying to create my very 1st MySQL stored procedure

I am trying to create a stored procedure in MySQL 5.5.8. using connector 8.0.13. Once this procedure is debugged, it will have to be embedded into a c#.net application, to be created on the fly every ...
1
vote
1answer
49 views

Problem with unmasking a database user during a sproc

I am having an issue with a stored procedure that refreshes dynamic data masking on a database. This sproc is run as a job against all databases on this server. The problem that I have is that I have ...
0
votes
2answers
57 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 ...
0
votes
1answer
25 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
45 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
19 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
34 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
74 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
73 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
20 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
44 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
155 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
33 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
46 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
13 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
98 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
232 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
360 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
26 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
66 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
47 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
73 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
42 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
56 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
44 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
53 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
2 3 4 5
30