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.

239 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5 votes
0 answers
181 views

Does specifying STRICT on a PostgreSQL trigger procedure do anything?

Does specifying CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT or STRICT (https://www.postgresql.org/docs/current/static/sql-createfunction.html) on a trigger procedure in PostgreSQL do anything? ...
soliz's user avatar
  • 293
4 votes
0 answers
405 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] ...
Vojislav Depalov's user avatar
4 votes
2 answers
3k 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 ...
Antidiscrete's user avatar
4 votes
0 answers
122 views

creating deadlock for distributed database in mysql-(Testing purpose)

I need to create a deadlock for distributed database in MySQL for testing purpose with a simple sql query(without front end), can anyone good with MySQL query help me out in this with sample query. ...
Acube's user avatar
  • 99
4 votes
1 answer
404 views

Read/Write Deadlock in SQL Server

We are facing deadlocks when a stored procedure is concurrently executed. We are using Microsoft SQL Server 2008 R2, concurrent execution is done with java executing multiple request of the stored ...
Aslam Kumhar's user avatar
3 votes
2 answers
145 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 ...
user217624's user avatar
3 votes
0 answers
1k views

INSERT-SELECT Statement occasionally fails silently and works upon re-run

We have a batch import process that BULK INSERTs raw data into staging tables, does some data mapping, and then pushes it to another database for validation. The procedure where my issue occurs is ...
Haris Khan's user avatar
3 votes
0 answers
150 views

User defined stored procedure - where's the execution plan?

SQL Server 2016, Enterprise Edition in Windows Is there anyway to display the execution plan of a stored procedure created in C# with Visual Studio Data Tools? This is a question that someone with ...
Craig Efrein's user avatar
  • 9,640
2 votes
0 answers
34 views

Where sk = null clause gives an error when used in a query but not in a stored procedure in teradata

In teradata sql I get an error when I use sel * from table where sk = null However if I include this in a stored procedure and pass a null value in the parameter I don't. replace procedure(In name ...
iosdev's user avatar
  • 21
2 votes
0 answers
133 views

Problem with sysdepends while checking dependencies

I have this script to list all dependencies of all views in a database . I get the dependencies in the right order when I use sysdepends ,but I am missing a few dependencies while using sysdepends. ...
Viz Krishna's user avatar
2 votes
0 answers
198 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 ...
Guiles Kompanix's user avatar
2 votes
0 answers
938 views

Stored Procedure: How remove Comments, spaces, and extra lines, or companies binaries?

We are trying to compare stored procedure codes. Example, these two stored procedure codes produce same result. (1) Is there method to convert stored procedures into binaries to validate they ...
user avatar
2 votes
0 answers
1k views

SQL Server stored procedure not throwing certain kind of error

I noticed an issue in my app recently where an database insert that occurred in a stored procedure was failing, but no exception was getting thrown. The insert failed because a column that doesn't ...
Gary Reckard's user avatar
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 ...
Irregular Square's user avatar
2 votes
0 answers
1k views

C# Windows Service task fails due to a SP timeout, but works fine after executing the SP in SSMS

I have a Windows Service app running and sometimes a task which has a method call to a stored procedure (with a Select operation) is keep failing due to a timeout. But when I execute that SP with ...
Aki T's user avatar
  • 213
2 votes
0 answers
3k views

Mysql: Stored procedures vs. inline SQL?

We are evaluating whether we should write stored procedure OR write inline queries. I read this link to get an opinion. I want to know pros and cons from mysql perspective as the above question is ...
maverick's user avatar
  • 151
2 votes
0 answers
141 views

Permissions on views/stored procedures and the referenced base tables in linked servers

I need help setting up security division between SELECTing and EXECUTEing from views and stored procedures and access to base tables referenced by those objects on linked servers on SQL Server 2012SP3....
LLeeuwenstein's user avatar
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, ...
Alex Frenkel's user avatar
2 votes
0 answers
45 views

Securing SQL 2012 column data strategy

I am working on a project that requires a column that is currently in plain text to be encrypted. As part of the project i still need to be able to run a SP from SSMS and the plain text data. I was ...
Chandler R Reeves's user avatar
2 votes
0 answers
308 views

Failed sp_executesql queries and rpc_completed

I'd like to track which procedures are failing in my database using extended events. Procedures are being executed like that from an application: EXEC sp_executesql 'exec dbo.GetData'; And when that ...
Evaldas Buinauskas's user avatar
2 votes
0 answers
549 views

Shortcut for Applying stored procedures in MySQL Workbench

Using MySQL Workbench, after editing a stored procedure in the built-in editor, the only way I know how to commit the changes are to take my hands off the keyboard, use the mouse, and click "Apply", ...
BRebey's user avatar
  • 121
2 votes
0 answers
1k views

Random (rare): MySQL Stored Procedure Does Not Exist

I have a stored procedure that runs each day, for roughly 20 schemas (each have their own copy), for the last 3-4 months. I got the error "Procedure [xxx] does not exist" for all databases in my ...
cjriii's user avatar
  • 121
2 votes
0 answers
3k views

Mysql stored procedure SQLEXCEPTION

Below is my sample query, in which I first create new folder then update count then create a new tag and finally get new id. insert into folder (name)values("new"); update folder set count = count + ...
pitu's user avatar
  • 121
2 votes
0 answers
100 views

Mysql log table

I have a logging table that contains 3 fields Id action time The time column is 11 digit integer. Our goal is that each month the logs of the prior month will be deleted. It is possible to do with a ...
user69153's user avatar
  • 157
2 votes
0 answers
142 views

Read show create table in mysql stored procedure

How can I read the output of following query and assign to a stored procedure's variable? SHOW CREATE TABLE users; Or, is there is an information schema from where I can draw the full table ...
Bimal Poudel's user avatar
2 votes
0 answers
2k views

exec sp_getapplock - Different behaviour using go or without go

we have a strange behaviour while using sp_getapplock (we tried to use this to solve a problem with a deadlock involving some insert while another session was trying to execute a ALTER SWITCH command)....
Leonardo Romanato's user avatar
2 votes
0 answers
645 views

Cumulative Function in MySQL

i have a problem creating cumulative function in mysql database. let say i have a stored procedure which getting data and counting quantity/sum_quantity example A table consist of consumer goods ...
randytan's user avatar
  • 121
2 votes
0 answers
138 views

Why does MySQL force the tradeoff between swallowing errors and leaking commits in transactions?

Referring specifically to the choice of whether to use DECLARE EXIT HANDLER FOR SQLEXCEPTION. If you use it, you swallow errors and there doesn't seem to be any way to access them programmatically, ...
djechlin's user avatar
  • 223
2 votes
0 answers
218 views

Requesting advice on creating a table controlled SQL job scheduler

I have been tasked with cleaning up a series of SQL jobs that send email updates about various data and system conditions in my company. Currently, there are a ton of jobs, each with its own SQL code ...
Davenport's user avatar
  • 327
1 vote
1 answer
120 views

Is there any benefit to WITH RECOMPILE or OPTION (RECOMPILE) in a stored procedure that uses temp tables at every stage?

Suppose that I have a stored procedure. It does not call any other stored procedures and is not called by any others, nor is it called concurrently. Every step in this stored procedure either creates ...
J. Mini's user avatar
  • 115
1 vote
0 answers
35 views

Azure Elastic Job - a stored procedure does not run on the target database

The target group NPBlue contains 3 databases NPBlue 882E2367-93C0-46CC-A4ED-90AE5F2860EB Include SqlDatabase 69B479D8-34FA-48A6-9A3D-6B302864B9AD NULL NULL NULL x-database-weu.database....
Neurobion's user avatar
1 vote
0 answers
37 views

How to write a heterogeneous variadic function

I am trying to write a postgres stored procedure that can take a variable number of arguments of potentially different types (i.e. a heterogeneous list of arguments). As I learned from here, the ...
tinlyx's user avatar
  • 3,490
1 vote
1 answer
212 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. ...
Jeff Bauersfeld's user avatar
1 vote
1 answer
31 views

Checking if a specific date is closed in a JSON data stored within a text field in MariaDB

Problem Description I'm currently working on a complex stored procedure that involves a table called "price_options_test". Within this table, there is a text field named "close_days&...
Khuzema's user avatar
  • 13
1 vote
0 answers
280 views

ORA-12840: cannot access a remote table after parallel/insert direct load txn After Switching To Oracle.ManagedDataAccess

We are encountering a strange issue at work. For years we had a .NET Framework Windows service running with the Unmanaged Oracle Client (10.1.3001) with no problems at all. Recently, we decided to ...
NickDelta's user avatar
  • 113
1 vote
0 answers
47 views

Issue with stored procedure returning no results

I have the following MySQL procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `ESOLUTIONS`.`getPlatformByAttribute`( IN attributeName VARCHAR(255), IN startRow INT ) BEGIN SELECT ...
Kevin Huntly's user avatar
1 vote
0 answers
34 views

Allow procedure to get multiple (but not defined) number of input variables

I have a serie of statements like this one: SELECT `Article`.`id`, `Article`.`title`, CONCAT('[', GROUP_CONCAT(DISTINCT CASE WHEN (`junction_article_author`.`authorRank` IS NULL AND `Authors`.`...
fauve's user avatar
  • 111
1 vote
0 answers
406 views

Azure SQL Error "Invalid object name '##{myTempTableName}'

I am getting an error in my stored procedure: "Invalid object name '##{myTempTableName}' for 3 global temporary tables. There are several temp tables used in the procedure, but I am getting this ...
One More Tea's user avatar
1 vote
1 answer
30 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 ...
Sam Bilbrey's user avatar
1 vote
0 answers
565 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,...
Erwin Zangwill's user avatar
1 vote
1 answer
763 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 ...
Ahmed's user avatar
  • 23
1 vote
1 answer
2k 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 ...
paigelarry342's user avatar
1 vote
0 answers
115 views

SQL Server to PostgreSQL with unique Constraint

I am trying to push data from a MS SQL Server to a PostgreSQL (both on my local device) by using the pg as linked server and calling a stored procedure of the SQL Server. A simple INSERT INTO [...
Andre S.'s user avatar
  • 111
1 vote
0 answers
693 views

How to cast a string into an identifier?

I have a stored procedure and a table. In the table the name of the stored procedure is stored. CREATE OR REPLACE PROCEDURE "hello" () LANGUAGE plpgsql AS $$ BEGIN RAISE INFO 'Hello World!'...
ceving's user avatar
  • 275
1 vote
1 answer
100 views

Is it possible to run a stored procedure automatically on a new database when it is attached?

Replication is causing issues on our daily snapshots. What we would like to do is remove replication automatically on a database when it is attached to the SQL server. Is this possible?
Fire Starter's user avatar
1 vote
0 answers
2k views

Unable to resolve column level collations

This whole stored procedure is to generate a csv file into the mention folder where file is getting generated but the headers are missing. --Adding missing headers into the file---- DECLARE @...
sowmya sirsi's user avatar
1 vote
0 answers
793 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 ...
stuXnet's user avatar
  • 121
1 vote
1 answer
279 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 ...
Tom H's user avatar
  • 11
1 vote
0 answers
457 views

How to write Procedure to fetch data from other table?

drop procedure if exists findClosestTimeStamp; delimiter $$ create procedure findClosestTimeStamp() begin declare tt1 time; declare dt1 date; declare view1 varchar(30); declare done int ...
Nurav's user avatar
  • 11
1 vote
1 answer
2k views

MySQL - Pass input parameter into Cursor query

Is it possible to pass input parameter into Cursor SELECT statement WHERE clause? For some reason I think it isn't working. I'm trying to pass _TAG and _ITEM_NAME into where clause. DECLARE ...
R0bert2's user avatar
  • 111

1
2 3 4 5