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
-1 votes
0 answers
23 views

How can I add an insert procedure into this stored procedure

I have a stored procedure to provision tables in a database...I also want to insert system-generated data into the date_dim table...I have some values in single quotes 'yy-mm-dd' in the insert ...
user avatar
2 votes
1 answer
40 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 ...
user avatar
  • 123
0 votes
1 answer
50 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 ...
user avatar
0 votes
1 answer
17 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 ...
user avatar
  • 33
-1 votes
1 answer
127 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 ...
user avatar
0 votes
0 answers
43 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
64 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 ...
user avatar
0 votes
2 answers
93 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 ...
user avatar
1 vote
2 answers
79 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 ...
user avatar
0 votes
0 answers
71 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
58 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 ...
user avatar
  • 13
0 votes
1 answer
32 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
2 votes
0 answers
96 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
143 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
459 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
3 votes
2 answers
426 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
  • 175
0 votes
0 answers
26 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
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 ...
user avatar
  • 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 ...
user avatar
1 vote
1 answer
188 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
  • 139
0 votes
2 answers
89 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
65 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
72 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
115 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
58 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
2 votes
2 answers
137 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
  • 31
0 votes
0 answers
43 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
234 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
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 ...
user avatar
-1 votes
1 answer
32 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
20 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
280 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
1 vote
2 answers
376 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
72 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
28 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
386 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
113 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
62 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
36 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
251 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
34 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
453 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
401 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
1k 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
40 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
135 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
610 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
367 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

1
2 3 4 5
32