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
0 answers
8 views

Updating parameter values from a PostgreSQL procedure InOut JSON parameter

The JSON is being passed into a PostgreSQL procedure _get_xml_status(INOUT i_json json). I need to update the xmlInfo.Status parameter for each of the xmlName nodes depending on what I find for each ...
0 votes
0 answers
18 views

Procedure updating table with CLOB data type column does not work properly

I have a table with below structure : create table bb_analysisitems_batch ( batch_id number, batch_name varchar2(100), batch_result clob ) There are other ...
  • 1,366
1 vote
1 answer
24 views

Update rows recursively based on previous rows by individual

I am struggling in finding a faster way to update the data I am currently working with. More precisely, I have workers' contracts data with starting and ending date for each contract. However, ...
0 votes
1 answer
52 views

Routinely deleting data from tables using stored procedure

I've created a maintenance stored procedure to delete data older than 30 days on events table. What are the best practices for deleting data? I've encased the procedure with error handling, i.e. TRY......
  • 11
2 votes
1 answer
24 views

What are the usecases for stored procedures (`CREATE PROCEDURE`) as of Postgresql 14?

I've had an impression it was some go-to way of encapsulating complex SQL logic. But digging related responses, such as this one, it doesn't seem to be the case. So are CREATE PROCEDURE-related tweaks ...
-3 votes
0 answers
17 views

Grant permission on all stored procedures to a user to my database mysql [closed]

how to grant permission of my live database (stored procedures and all ) to a user in mysql
0 votes
1 answer
30 views

How to find tables with self referencing Foreign keys

Hi is there any query to get all self referencing foreign keys . I need to get the all tables and the foreign keys in a database with sled referring and drop and create it .Any idea how to do it .?
1 vote
1 answer
20 views

MySQL selectively pull distinct rows from multiple tables

Table A has ID and Name fields and the following data 1 Joe 2 John 2 John 2 John 4 George 4 George 4 George Table B has ID and a different field 1 ABC 1 XYZ 3 ABC 4 ABC 4 GHI 4 XYZ I would like to ...
-1 votes
1 answer
66 views

How to use temp table or while loop instead of cursor

i have an sp to delete data from grandchildren tables . But I need to change the cursor and use a temp table or while loop instead of the cursor. I have tried but was not working can somebody help. Sp ...
0 votes
0 answers
16 views

Grant execute with update inside a store procedure [closed]

I need grant the execute permission to a store rocedure with some updates inside. Which grants I must assign for execute the updates only with the sp and not directly? Thanks
0 votes
0 answers
22 views

pg_cron timezone issue, unable to run at specified time

Unable to run at proper timezone. I want to run two things bash scripts Stored procedure For both case I found it run at different time. Instead of 1 AM and 3 AM pg_corn runs another time. Why it is ...
0 votes
0 answers
18 views

Unable to delete data from dynamically assigned table at PostgreSQL13

Unable to delete from dynamically assigned table data at PostgreSQL13. Basically I want to archive 10 days older PostgreSQL data to another table. I want to pass days, source table and target table as ...
0 votes
0 answers
24 views

T-SQL: Slow performance on Stored Procedure Sending Data to LinkedServers

I am new to T-SQL and I am facing an issue with a slow processing stored procedure. The purpose of this procedure is to transfer table data from child databases up to their corresponding parents. The ...
  • 101
2 votes
1 answer
44 views

Procedure not looping properly

I have several client databases on one server. They all have a prefix. Lets use "piggy_" as the prefix for the purposes of this. I.e. piggy_client_a piggy_client_b piggy_client_c Below is ...
  • 123
0 votes
1 answer
66 views

How to map the newly created records in parent table with the child table?

have a table Employees Id Name EmployeeType_ID 1 John 1 2. Mathw 2 EmployeeTypes Id TypeName 1 Perm 2. Temp 3. Regular Now I am copying data from one server to ...
0 votes
1 answer
19 views

Searching and deleting MySQL records after insert with stored procedure

I have a quandary with which I am struggling to answer, and looking for some advice on how it can be done. We have a table, that is basically comprised of the following fields. I have used Info x to ...
  • 33
-1 votes
1 answer
322 views

SQL Server stored procedure taking too long sometimes even though indexes have been added with memory grant issues

I have a stored procedure that takes too long sometimes even though I have added all suggested indexes. I keep getting the Query had to wait for memory grant The query plan can be found here: Query ...
0 votes
0 answers
191 views

How to make stored procedure with bulk insert from Blob storage?

Im building a stored procedure to bulk insert from a .csv file located on Azure Blob Storage. I got it to work, by passing storage name + filename, and the SAS code. At first, it was ok, I ran the sp ...
user avatar
1 vote
1 answer
81 views

Is it safe to use user defined variables inside stored procedures in MySQL?

Currently I'm working with Stored Procedures in MySQL and I'm using in some procedures user-defined variables and I've seen that type of variables are initialized in the current session and keep their ...
0 votes
2 answers
99 views

SQL Query execution with IN statement

I didn't get proper result Below is my tables values : DECLARE @Id as nvarchar(MAX) = N'''2'',''3''' DECLARE @spparams as nvarchar(max) =N'@Id as nvarchar(max)' EXEC sp_executesql N'select * from ...
1 vote
2 answers
106 views

stored procedure runs fine in all sessions but one

There are procedures that run fine manually but not in a job, or fails when run from an application, or not work in SSIS SQL task Mine works in all sessions but one. this is the code I am running - it ...
0 votes
0 answers
130 views

How to use "with execute as" while querying a linked server from SQL Server

I have a strange permission issue with SQL Server 2019 (same thing happened also with 2016 version). I have a database ReportDB, where reportuser (login: reportlogin) can run several reports. I also ...
0 votes
1 answer
117 views

Passing dynamic filter to the Postgresql stored procedure [closed]

I have a stored procedure like this. create or replace function filter_data_student(filter_conditions text) RETURNS VOID language plpgsql AS $function$ DECLARE v_sql text default ''; BEGIN ...
  • 13
0 votes
1 answer
34 views

Keep procedure running even connection dies?

I want to create a procedure in my database that is expected to sleep for very long periods of time, like 9 hours. They perform some operations, sleep for hours, then do other stuff and exit. I will ...
  • 188
2 votes
0 answers
102 views

Why does SQL Server 2019 stored proc get Adaptive Join on Development Server, but not on Production Server?

I have a stored proc with one select statement that gets a plan w/ Adaptive Joins on my development server, but not on my production server. What could explain that? I'm not much interested in ...
3 votes
0 answers
158 views

Why adding select into table is changing execution plan?

I am having following query: DECLARE @VersionsIds TABLE(VersionId INT) INSERT @VersionsIds(VersionId) VALUES (520) SELECT vEps.MasterPolicyId -- INTO #Temp FROM [Events].[vwEventMasterPolicy_Search] ...
2 votes
2 answers
727 views

how to update one column with trigger mysql

i have 2 tables the first one is Table KEYS game keys WOW AAAAAAA-AAAAAAA-AAAAA WOW BBBBBBB-BBBBBBB-BBBBB L4D2 AAAAAAA-AAAAAAA-AAAAA TABLE inventory game country language pieces WOW USA EN 0 ...
  • 31
3 votes
2 answers
675 views

Stored Procedure with WHERE .. IN array parameter

I want to set a boolean on every entry in a table matching an array of row ids passed into a stored procedure. Help me crack the proper syntax to accomplish this? The following is as close as I've ...
  • 185
0 votes
0 answers
42 views

MySQL Storage Procedures: Automatic Hourly Partition Maintenance Syntax Issue

So I am streaming data from MySQL (fast in writes) to ClickHouse (insanely fast in reads). My MySQL to ClickHouse service copies the MySQL rows to ClickHouse compressed columns quite fast, so I do not ...
0 votes
0 answers
28 views

is it possible to compatible the store procedure in different type of database

I want to do some data statistic, now I am using the PostgreSQL 13, I want to write some procedure to do this task, but I want the procedure to compatible with all type of database like Oracle, SQL ...
  • 527
-3 votes
1 answer
41 views

How to write select statment that get data based on partid from last date to current date with filling gapes?

I work on sql server 2012 i face issue i can't make select statment get Partid from last month until current month march (3) . based on last date exist per partid and on same time if there are any ...
1 vote
1 answer
275 views

How to avoid deadlocking in a stored procedure with SERIALIZABLE isolation level, INSERTs, UPDATEs, and DELETEs

I'm having trouble with deadlocks in a stored procedure. This stored procedure is part of a custom full-text-like search system. It's called after a record is updated, and inserts/updates a list of &...
  • 139
0 votes
2 answers
109 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 ...
2 votes
1 answer
75 views

Multiple Computed, Dependent Columns on INSERT in SQL Server?

I'm relatively new to database programming and am looking for some some theory/best practices and feedback on an issue involving multiple dependent, cascading, computed columns for an OLTP SQL Server/...
-1 votes
2 answers
108 views

Dangers of running this stored procedure that kill blocking processes

use master GO CREATE PROC support_KillBlockingProcesses (@RecursiveCount int = NULL) AS DECLARE @count int, @spid int, @sql nvarchar(max) SET @count = ISNULL(@RecursiveCount, 3) while @count > 0 ...
  • 5
0 votes
1 answer
225 views

Help syntax for Stored Procedure to INsert/Update records from 1 table into another [duplicate]

I am trying to create a stored procedure to insert or update records from 1 table into another table and I am having several syntax issues. it does not like the @MergeLog declaration it errors on ...
-2 votes
1 answer
67 views

when update large table it take too much time to update

I work on sql server 2019 when update table Z2DataCore.parts.SourcingNotMappedParts i found slow and long time when update estimated execution plan actual execution plan Update statement takes 26.5 ...
2 votes
2 answers
178 views

Temporarily disable SELECT for one table (during an update)

We have a dedicated data mart SQL Server with several really big tables that are updated one partition at a time via parametrized SSIS loop. Quite often when clients try to read such a table during an ...
  • 31
0 votes
0 answers
44 views

when check long wrong query i found status suspend with wait log buffer?

I work on sql server 2019 i run my stored procedure on sql server . it take may be 5 hours so i try to trace why it take too much time or too long time . so I make this query to trace issue on my ...
5 votes
1 answer
272 views

SQL Server allow stored procedure chaining

I have to premit that i am not a DBA but i have the need to mantain a SQL Server instance and have few problem with stored procedure permission chaining (hope terms is correct). To brifiley explain my ...
  • 287
2 votes
2 answers
45 views

when make select to top 5 of rows from table it take too much time?

I work on sql server 2019 i face issue when make select top 5 rows it take too much time . Number of Rows on Table Z2DataCore.parts.SourcingNotMappedParts 70 milion rows . when run statment select ...
-1 votes
1 answer
33 views

How to get partnumber that have at least have one source type Website and at least one stockid equal 1 Based on PartNumber?

I work on sql server 2017 i face issue i can't get partnumber that have at least source type website and at least have stockid equal 1 per partnumber so with another meaning i need to get part numers ...
1 vote
0 answers
34 views

How to return query result to a tset and later process the data?

Table and values are as follows: -- create a table CREATE TABLE table ( id INTEGER PRIMARY KEY, a integer[], b integer[] ); -- insert some values INSERT INTO table VALUES (1, '{1,2,3,4,5,6,7,8,9,...
1 vote
1 answer
501 views

Trigger uses a procedure or a function?

PostgreSQL version 10 and before use the term "Trigger Procedure" (https://www.postgresql.org/docs/10/plpgsql-trigger.html). But later versions use the term "Trigger Function". ...
1 vote
2 answers
606 views

How to save result from a Stored Procedure in a Linked Server to a variable or table?

I need to read the results of a stored procedure in a linked server, and store them in a variable, or a temporary table if not possible. I can do this, and I get the correct result: EXEC ('EXEC DBName....
0 votes
1 answer
169 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 ...
  • 3
-1 votes
1 answer
42 views

how to display category name for c and x from table category without using self join?

I work in sql server 2014 I need to get categories c and x without using self join. But, I don't know how to do that. My data sample create table #category ( categoryc int, categoryx int ) ...
-1 votes
1 answer
53 views

Error while declaring procedure

When I run this query it returns me a count of null values of the column country (select sum(case when (country IS NULL) then 1 else 0 END) as 'null_flag' from city) whereas ...
2 votes
4 answers
484 views

NONCLUSTERED index seek on mslid is slow

I have an issue in SQL Server where a NONCLUSTERED index seek is performing poorly. Below is the actual execution plan https://www.brentozar.com/pastetheplan/?id=Sk3-4JGAK How can I enhance ...
0 votes
0 answers
143 views

Create a stored procedure that dynamically union tables having extension .csv

I have a database named Fire that has 41 tables with the name format dbo.[#######.csv]. All the tables have the different number of column name like table 1 column A,B and table 2 column A,B,C and ...

1
2 3 4 5
33