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
1 answer
666 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 ...
1 vote
1 answer
284 views

Comments and Spacing Removed in Create Procedure Statement on some SQL Servers

Have third-party procedure creation scripts to install on my databases. SQL Server 2019 CU21 databases are spread across several Windows 2019 servers. On some servers, the creation scripts run fine. ...
-2 votes
0 answers
28 views

How to create a store procedure will trigger an email notification in sql server database?

Below is our current problem in production and want to create a store procedure will trigger an email will be able to fix this issue below. We had a truck that went out with 30 tons loaded but the ...
1 vote
2 answers
1k views

DB2 - Using stored procedure variable in a "CREATE TABLE as (..)" Statement

migrating from Netezza to DB2 we face the following problem: CREATE OR REPLACE PROCEDURE DoStuff() BEGIN DECLARE vStarttime TIMESTAMP; SET vStarttime = now(); CREATE TABLE foo AS ( ...
0 votes
3 answers
1k views

MS SQL: How to Assign A Table Column to a Variable

I'm trying to assign a calculation involving two table columns as the value of a variable. I keep getting the error that the multi-part identifier could not be bound. My ultimate objective is to ...
0 votes
1 answer
361 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 ...
0 votes
0 answers
18 views

Configuring Informix to access data on another server

I have inherited an Informix installation that contains a large amount of business logic in the form of procedures. The company also uses SQL Server, MySQL and Postgres for various roles. We're very ...
1 vote
2 answers
4k views

Old user removed, now having DEFINER errors

Old users were removed from the mysql.user table and now while running some views, I got the error that the user@host does not exist. Also while taking a dump, a view is unable to be dumped due to ...
0 votes
1 answer
847 views

Procedure MySQL cursor taking a long time to execute

I have a MySQL procedure, with a cursor in it. The sample cursor query is given below DECLARE cs CURSOR FOR (SELECT a.mag_id FROM A a WHERE a.creation_date BETWEEN (...
2 votes
1 answer
1k views

MySQL privileges for roles aren't applying to users?

I've done what I consider to be sufficient research with no answers to justify posting this question here. I am attempting to grant users permission to execute certain stored procedures. And because ...
0 votes
1 answer
364 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
0 answers
8 views

KILL QUERY Flooding in General Query log for MySQL 8.0.25

Problem : We have two MySQL servers in master master replication. We can see many KILL QUERY statements in MySQL general log specific to stored procedures as below : 2024-03-20T21:54:45.056981+05:30 ...
9 votes
2 answers
23k views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
0 votes
0 answers
22 views

Need to create a duplicate table in a separate schema automatically whenever a new table is being created

Whenever a new table is being created in Schema 'A', I need the table to be duplicated in the Schema 'B'. This should happen automatically. I have searched for trigger concept but it wont work on DDL ...
1 vote
2 answers
2k views

MySQL is pretty slow after 50-100k rows whats happen?

I have been trying now many times, and I tried to find a way to make sense of and find out my problem. But the problem is still performance issues. I tested it on a developer server ( 2 cores, 4gb ram ...
1 vote
0 answers
18 views

procedure that takes the distinct tables with incremented (int) key and sub the int back for the original value

I'm working on developing a set of stored procedures in PostgreSQL to abstract common operations I'm seeking advice on implementing a pattern for substituting values for INTS in child tables. Please ...
0 votes
2 answers
18k views

Get the list of tables used in a stored-procedure for a database

our company is planning to administer the report server database and the requirement is to get the list of the tables used in stored-procedures in the productions server database.Is there any query/...
1 vote
1 answer
3k views

How to fix MariaDB commands out of sync, missing expression near ON?

I have the following procedure DROP PROCEDURE IF EXISTS find_transactions_past_5_days; DELIMITER $$ CREATE PROCEDURE find_transactions_past_5_days (IN input_date DATE) BEGIN ...
2 votes
1 answer
169 views

Catching exceptions and rolling back transactions

I am just trying to see if I understand this correctly: By default, any error occurring in a PL/pgSQL function aborts execution of the function and the surrounding transaction I have a procedure ...
1 vote
1 answer
1k views

Execution time of triggers fired when a procedure is executed in postgresql

I have a stored procedure which may delete and/or insert tuples into a database table. This table has an AFTER INSERT OR DELETE OR UPDATE trigger. Basically I want to retrieve how much time was spent ...
2 votes
2 answers
8k views

`RETURN NEXT` vs `RETURN RECORD` in PostgreSQL

What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?
1 vote
1 answer
8k views

Error 1304 when creating a procedure after a drop if exists

I have some php code that runs queries stored in a database to update some thousand client databases. Now, for the first time, I needed to create a stored procedure because the update is complex. I'm ...
1 vote
1 answer
295 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 ...
1 vote
2 answers
108 views

Prevent users from sending an email attachment in sql server

In SQL Server, I want to restrict all users (except a few specific users) to send an email with attachment. For example, someone import data into excel file and send it via mail using the ...
-1 votes
0 answers
58 views

How to convert a function into a Stored Procedure in Microsoft SQL?

I have this function that takes a decimal parameter and returns a varchar CREATE FUNCTION dbo.NumberToWords(@Number DECIMAL(18, 2)) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Words NVARCHAR(MAX) SELECT ...
0 votes
1 answer
1k views

Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records. create or ...
0 votes
1 answer
40 views

sql bigger parameter then specyfied - no error - just taking substring? why?

I have some sql procedure (MSSQL 2019) if relevant Create PROCEDURE [dbo].[test] @tmp varchar(32) AS print @tmp why when i do EXEC @return_value = [dbo].[test] @tmp = N'...
0 votes
1 answer
125 views

Execution timeout exception in ASP.NET core

My problem is a quite hard to define. We have an SQL Server (version 15.0.2104.1) stored procedure that fills out our web site home page with data. When executed from SSMS it takes abount 0.5 second ...
0 votes
0 answers
65 views

Using results from query as stored procedure parameters and combining results

I have a standard select query which returns a set of results :- SELECT c.CASEID, c.Event, c.ACTION, c.CYCLE, c.PROPERTYTYPE, c.COUNTRYCODE, c.STATUSCODE, c.CRITERIA, ...
0 votes
0 answers
7 views

Aurora3 RDS MySQL Stored function/trigger performance when using information_schema

Since moving to Aurora3 RDS (MySQL 8 based) vs Aurora 2 (MySQL 5.7 based) noticed that execution time of certain triggers and stored functions performance has degraded. Correlation between triggers ...
1 vote
1 answer
820 views

How to record execution time of all Procedures in MySql

I want to record execution time of all procedures in MySql. For example, I have a database named test and it contains 25 procedures. When a procedure runs, I'd like each execution time to be recorded ...
0 votes
0 answers
29 views

How to find SSIS package thats calling / Executing a stored procedure?

How to find SSIS package that's calling / Executing a stored procedure? May I know a SQL query OR some other way to see which SSIS package is calling / executing a stored procedure? Thanks, Kumar
2 votes
2 answers
3k views

MySQL store procedure use select responce in INSERT...SELECT where in array

I am pretty sure that this is a simple matter and that it's answered but I seems like not able to find it online, or to phrase it correctly. I have a table (ven_data) that has a few columns: id_v, ...
0 votes
1 answer
3k views

MySQL: Privileges to view Views/Stored Procedure for user Regardless of Host

I've created stored procedures on a (remote hosted - DreamHost) MySQL Database using the HeidiSQL client. One problem i seem to have is that viewing/editing the content of stored procedures is only ...
4 votes
2 answers
4k views

Way to obtain full list of column dependencies for stored procedure?

I've built a stored procedure (which we can call sproc_deps) that uses sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. I want it to list out all of the tables and columns used by a ...
-1 votes
1 answer
340 views

Query with stored procedure

I have a query that grabs data from a .csv and inserts into a table I created, now I need to take a single column from my destination table and use it to gather data from another database from another ...
12 votes
3 answers
14k views

SQL Server Insert Into - How to identify the column that is causing truncation error

I have a stored procedure that inserts 650 fields into a table. The insert is failing with a truncation error. It's a simple INSERT INTO SELECT (a bunch of fields) FROM (a bunch of tables) Below ...
1 vote
1 answer
2k views

MySQL - using result of Procedure in Trigger to Insert in new table

I'm guessing I'm doing something that's prohibited by MySQL, but can't see what. Basically, I have a set of tables that hold lists of things (item name, item types, item groups etc.), and I need to ...
0 votes
0 answers
113 views

Insert data into Always Encrypted column using stored procedure in MSSQL

I am facing an issue while trying to insert data into SQL table which is having encrypted columns in it. Following is my approach. I have created a table with three columns: [ID], [Name], [Password]. [...
15 votes
4 answers
27k views

In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure

One way to determine the executing stored procedure is to use "dynamic management" methods, like so: SELECT sqlText.Text, req.* FROM sys.dm_exec_requests req OUTER APPLY sys....
1 vote
1 answer
3k views

SHOW CREATE PROCEDURE procedure_name not showing the procedure content

Hello I'm struggling with the Issue where i have to view the stored procedure content for that i have issue the command to get the name of the Procedure mysql> SHOW PROCEDURE STATUS; +-----+-------...
14 votes
1 answer
2k views

Why does a simple natively compiled stored procedure run out of memory when table variables are used?

My version of SQL Server is SQL Server 2019 (RTM-CU18). The following repro code requires that an in memory filegroup is created. For anyone following along, please remember that an in-memory ...
0 votes
1 answer
423 views

Insert Into Table performance questions

I am writing a stored procedure, main purpose of it will be the insertion into table Procedure will be signed by certificate, and sending email notifications Procedure will accept user-defined table-...
1 vote
2 answers
308 views

What is the fastest way to log the Input and Output parameters when executing Stored Procedure

Normally i will log this information at the end of each SP. The syntax looks like this. DECLARE @SPContent nvarchar(4000)= ( SELECT @ServiceID [ServiceID], @AccountID [...
0 votes
1 answer
746 views

MySQL Stored Procedure Compiles Without Error (I want the error to occur)

I have a MySQL stored procedure that runs fine and does its job quite well. Yesterday, I edited it in MySQL Workbench and introduced a spelling mistake on purpose. For example: - Valid statement: ...
0 votes
1 answer
920 views

Creating map and use that within the mysql stored proc

I am working on a migration project and the data needs to be migrated from system A to system B. The id's in system A and system B will be different. So maintaining a table with source and target ...
0 votes
2 answers
54 views

MariaDB gives syntax error with empty near-string

Im using MariaDB 10.24.8 on Linux and I'm encoutering this syntax error which has been stumping me for the last few hours: ERROR 1064 (42000) at line 7: You have an error in your SQL syntax; check the ...
0 votes
1 answer
2k views

MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure

I have this stored procedure: START TRANSACTION; code_gen_loop: WHILE @Codes > 0 DO IF MOD( @Codes, 10000 ) = 0 THEN EXECUTE qry_status_stmt2; EXECUTE qry_status_stmt3; ...
0 votes
2 answers
679 views

Mysql query - Event to delete rows by its value

need help here I want a query to delete all rows in which column value is older than today. One of the columns is 'check_in' (values in timestamp format) and I want the query to identify the row that ...
2 votes
2 answers
1k views

Obtain a "list" of stored procedures which include INSERTs without naming the columns involved

As part of some enhancement, a new column has been added to over 750 tables. Now my problem is within almost 3000+ of stored procedures, some old INSERTs do not name columns. Is there any way I can ...

1
2 3 4 5
34