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
26 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 ...
user avatar
0 votes
1 answer
15 views

Passing dynamic filter to the Postgresql stored procedure

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 ...
user avatar
  • 13
0 votes
1 answer
27 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 ...
user avatar
  • 188
-1 votes
0 answers
17 views

SQL Server Question Stored Procedures and contuous delployment Azure DevOps

Background: We hare migrating from oracle to sql server. We have a LOT of stored procedures that live in packages. We have a lot of of packages as well. Packages usually are tied to a main application....
user avatar
  • 1
2 votes
0 answers
86 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 ...
user avatar
3 votes
0 answers
127 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] ...
user avatar
2 votes
2 answers
155 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 ...
user avatar
  • 31
2 votes
2 answers
144 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 ...
user avatar
  • 165
0 votes
0 answers
18 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 ...
user avatar
0 votes
0 answers
27 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 ...
user avatar
  • 477
-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 ...
user avatar
1 vote
1 answer
59 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 &...
user avatar
  • 129
0 votes
2 answers
63 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 ...
user avatar
2 votes
1 answer
53 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/...
user avatar
-1 votes
2 answers
36 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 ...
user avatar
  • 5
0 votes
1 answer
40 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 ...
user avatar
-2 votes
1 answer
51 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 ...
user avatar
1 vote
1 answer
79 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 ...
user avatar
  • 11
0 votes
0 answers
41 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 ...
user avatar
5 votes
1 answer
206 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 ...
user avatar
  • 287
2 votes
2 answers
43 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 ...
user avatar
-1 votes
1 answer
29 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 ...
user avatar
1 vote
0 answers
14 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,...
user avatar
1 vote
1 answer
54 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". ...
user avatar
0 votes
2 answers
97 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....
user avatar
0 votes
1 answer
25 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 ...
user avatar
  • 3
-1 votes
1 answer
40 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 ) ...
user avatar
-1 votes
1 answer
18 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 ...
user avatar
2 votes
4 answers
199 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 ...
user avatar
0 votes
0 answers
74 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 ...
user avatar
1 vote
1 answer
53 views

Stored procedure on OVHCloud MySQL server fails with Unknown column '' in 'field list'

I'm using the jaro winkler stored procedure here: https://gist.github.com/TheHiddenHaku/7229861. This runs fine on my local MySQL server (version 8.0.19) but when trying to use the same function on ...
user avatar
  • 575
0 votes
0 answers
34 views

How to get first row group by partid and compliance typeid and document type?

I work on SQL server 2014 I face issue I can't get only one row based on part id and compliance type id and document type . so if have part id as 3581935 and compliance type id 1 and document type Web ...
user avatar
0 votes
1 answer
157 views

Unknown column in field list MariaDB

I am using PHPMyAdmin and MariaDB and get this weird error as mentioned in the title. CALL log_total_outstanding(@message); MySQL said: Documentation #1054 - Unknown column 'time' in 'field list' I ...
user avatar
  • 13
0 votes
0 answers
26 views

mysql 8.0.26 - stored procedure name length issue (25 Chars) only

I have found a very odd issue with mysql 8.0.26. If I try to either create a procedure via the command line interface (mysql shell) or via a sql script, if the stored procedure name is 25 characters ...
user avatar
-1 votes
2 answers
192 views

WHEN run query on SQL Server, it is very slow. How to solve execution plan issues?

I work with SQL Server and face an issue that a query is very slow. INSERT INTO [ExtractReports].[dbo].[TradeCodesDelete] (PartID, Code, ...
user avatar
1 vote
1 answer
185 views

Why does mysqldump not backup procedures?

I am running MySQL 8.0.23 and try to backup/restore procedures. Therefore I created a user "dump@localhost" with the following rights: > Grants for dump@localhost > GRANT PROCESS ON *.*...
user avatar
  • 283
0 votes
2 answers
448 views

Calling a powershell script using xp_cmdshell with parameter in T-SQL

I have been using the following references for this Calling powershell function from T_SQL. This is on SQL server 2016 The element I need help on is the format of the call in SQL to the powershell ...
user avatar
  • 227
1 vote
0 answers
13 views

Can I Run The Same Statement On A List of Tables? [duplicate]

I'm working on a PostgreSQL stored procedure to remove records from a set of tables. The criteria for removal is the same on all the tables. So I've got something like this: -- Start with the ...
user avatar
0 votes
1 answer
39 views

SQL Server - Best way to use case inside case

I want to update the column to find out the unequal data from two tables. I'm using a case query to check both tables, and it's working fine but some conditions must be met which confuses me Problem I ...
user avatar
0 votes
1 answer
109 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-...
user avatar
0 votes
1 answer
331 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 ...
user avatar
3 votes
2 answers
330 views

Local Variables in Stored Procedures

I've read online that if you use local variables in stored procedures instead of the input variables then the stored procedure is optimized as if it was using the hint OPTIMIZE FOR UNKNOWN. How ...
user avatar
0 votes
0 answers
24 views

Which rights does a user need to backup procedures in MySQL 8.x?

I am performing a backup on MySQL 8.0.26 which needs to include all routines, triggers, events etc. in order to be fully recoverable. The command I am using: /usr/bin/mysqldump -u $MUSER -p$MPASS ...
user avatar
  • 283
0 votes
2 answers
67 views

Oracle 12cR2 RAC - ORA-06508

Environment: DB Version: Oracle Database 12cR2 EE on Exadata RAC: True Problem: ORA-06508 on procedure. One of our users had a DBA role privilege, we don't want such a big privilege to be defined ...
user avatar
0 votes
1 answer
45 views

How to improve performance of a stored procedure called from a web page?

This is my first SQL stored procedure $sql = ("CALL Login_Details($ID);"); $result = mysqli_query($conn, $sql); $row = mysqli_fetch_array($result); mysqli_next_result($conn); When I run ...
user avatar
-1 votes
1 answer
29 views

MySQL Stored Procedure Generating an Error during creation

I have a MySQL stored procedure (shown below) that's supposed to build a list of IDs from a table of hierarchically related items. I'm having to re-engineer the stored procedure to switch from using a ...
user avatar
  • 101
1 vote
1 answer
107 views

How to parametrize DDL in a plpgsql procedure?

How do I parametrize DDL statements in a plpgsql procedure? Namely, I want to use the org_label param as the role and schema name but the code below takes it literally and I end up with the role "...
user avatar
0 votes
0 answers
36 views

How to make multiple queries and save the result in a cursor?

I have 4 tables (Company, User, Role, UserRole), I need to get the Id of several users, and then delete them. TABLE Company Id Name TABLE User Id FirstName Email Company TABLE UserRole UserId RoleId ...
user avatar
  • 101
-2 votes
1 answer
25 views

Getting an error while creating stored procedure

When I tried to create the following procedure, I get an error that the must declare scalar variable username. How do I resolve this error? Create procedure sp_addagent @username varchar(50), ...
user avatar
1 vote
1 answer
86 views

How can I Run stored procedures in public schema to provision and create tables in a new schema

I'm building a multi tenant application with shared database and different schemas(each customer has its isolated data in a schema) I have run a stored procedure that creates all tables and ...
user avatar

1
2 3 4 5
32