Skip to main content

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
-2 votes
0 answers
29 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 ...
thrue2023's user avatar
0 votes
0 answers
19 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 ...
Hut8's user avatar
  • 101
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 ...
Sana Ansari's user avatar
0 votes
0 answers
23 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 ...
Edvin Guromin's user avatar
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 ...
RandomNumberFun's user avatar
2 votes
1 answer
171 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 ...
Lugoom485's user avatar
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 ...
Farid Zahidov's user avatar
-1 votes
0 answers
60 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 ...
Luis Avalos's user avatar
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'...
Dorian's user avatar
  • 103
0 votes
0 answers
66 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, ...
UK1539's user avatar
  • 25
0 votes
0 answers
8 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 ...
BigKiwiDev's user avatar
0 votes
1 answer
126 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 ...
Eyal Hasson's user avatar
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
kumar's user avatar
  • 1
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]. [...
Prathap r's user avatar
0 votes
2 answers
55 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 ...
Fnununkslit's user avatar
0 votes
2 answers
190 views

Aside from explicitly flushing the cache or demanding recompiles, what recompiles a full stored procedure in SQL Server 2019?

I recently finished reading Plan Caching in SQL Server 2008 and I have become confused. It seems that, short of totally flushing the plan cache or explicitly demanding a stored procedure to be ...
J. Mini's user avatar
  • 143
3 votes
3 answers
653 views

Given that CREATE PROCEDURE must be the first statement of its batch, what is the use of Temporary Stored Procedures?

I was recently made aware of temporary stored procedures and became shocked that I've never seen them used before in my career. I was very disappointed when I remembered that CREATE PROCEDURE must be ...
J. Mini's user avatar
  • 143
1 vote
2 answers
161 views

Track CPU time used by a Stored Procedure

I need to track CPU time (not duration!) used by a stored procedure. Procedure has many SQL statements inside it. I've tried module_end event in Extended Events, but it does not have cpu_time field (...
Aleksey Vitsko's user avatar
1 vote
1 answer
209 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
  • 143
2 votes
0 answers
35 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
1 vote
3 answers
205 views

MySQL Temporary Tables and Connection Pools

This is more of a "please confirm/correct my understanding" question. Background I connect to MySQL using JDBC, the connection pool has around 250 connections, most of which seem ...
DSZ's user avatar
  • 125
0 votes
1 answer
49 views

Oracle Redaction and Definer's Rights Stored Procedures

0 I created a data Redaction policy for a field EMAIL in a Person Table in an ORACLE Data Base 12c. In the expression statment of the Redaction Policy is the exception for the Data base owner user: ...
Ana Esquivel's user avatar
1 vote
1 answer
222 views

how to generate dummy data in mysql using stored procedure in relatable table?

I want to generate dummy data for country and states table and the states table have a relation with the country table using a foreign relation ship here are the tables MYSQL table CREATE TABLE ...
leburik89's user avatar
0 votes
0 answers
79 views

Stored Procedure Error: an exception occurred while executing a transact-sql statement or batch , invalid object name 'sys.all_objects'

Am trying to expand the Store Procedure in SQL Server 2012 x64 but there is a error prompts. Any Help? Thank you. TITLE: Microsoft SQL Server Management Studio Failed to retrieve data for this request....
Marvin's user avatar
  • 1
0 votes
1 answer
45 views

Can pl/pgsql function take a variable number of parameters?

I have a database with multiple schemas which have the same tables and views, and I want to create a procedure that creates a view from one the views in the schema. CREATE OR REPLACE FUNCTION ...
vfclists's user avatar
  • 1,053
1 vote
0 answers
38 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
0 votes
1 answer
127 views

No output when calling a stored procedure

Is there any mistake in my SQL code? The procedure is apparently created, there's no error, but when I call it with CALL user_orders(10) I get no rows. The query itself is valid: if I execute it on ...
Sergey Zolotarev's user avatar
0 votes
1 answer
51 views

How would you implement Rollback when you need to make sure that 2 updates and 2 inserts either all happen or are all rolled back?

I have a MySQL stored procedure. Cut down, it looks like this. START TRANSACTION SELECT some_columns SET some_variables UPDATE row_in_balance_table UPDATE row_in_entry_table INSERT ...
T M's user avatar
  • 11
1 vote
0 answers
49 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,550
0 votes
1 answer
41 views

Why is PostgreSQL `coalesce` not working as expected/hoped inside a function? [closed]

A simple function that looks like this: CREATE OR REPLACE FUNCTION text_to_tsvector(config regconfig, source text) RETURNS tsvector LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT RETURN to_tsvector(...
Kasbolat Kumakhov's user avatar
0 votes
1 answer
37 views

MYSQL transactions and guaranteeing inserts/deletes/updates

I have a stored procedure called processWinningBet. It takes a pendingBet and does these things. update balance table for user creates a row in historic bets table deletes the corresponding row in ...
T M's user avatar
  • 11
0 votes
1 answer
80 views

Scalar Functions Causing Plan Cache Bloat

Our plan cache is showing a large number of duplicate entries for a scalar function that is called as per the code below: SELECT dbo.fnSomeFunction('12345678') I have done some testing and found that ...
SE1986's user avatar
  • 1,960
0 votes
2 answers
158 views

In SQL Server, if I have two otherwise identical stored procedures with different names, will they use the same execution plan?

Azure SQL Managed Instance. Here is the scenario. I have a stored procedure that produces a count of rows that match some criteria. But, depending on the Customer that is chosen, the statistics can be ...
CB_Ron's user avatar
  • 291
0 votes
1 answer
108 views

How to rename columns in an SQL-server database

My employer has the habit of giving customer specific tables a name, starting with the name of the customer. This makes it difficult to call the sp_rename stored procedure for renaming column names, ...
Dominique's user avatar
  • 521
4 votes
1 answer
567 views

Compilations/sec high when load testing a simple procedure

I am trying to load test a simple insert stored procedure: CREATE TABLE _test(ID BIGINT) GO CREATE OR ALTER PROCEDURE dbo.test_sp AS BEGIN SET NOCOUNT ON; BEGIN INSERT INTO _test ...
Artashes  Khachatryan's user avatar
-1 votes
1 answer
27 views

needle in a stored procedure haystack

I've been handed a stored procedure with almost 3,000 lines of code -_- for the most part it looks fairly benign, a lot of temp tables created, with end result of a data push via select statement from ...
BeckBeck's user avatar
1 vote
1 answer
148 views

SQL Server stored proc locking, serialization

Stored proc in SQL Server INSERT INTO table1 value sets A , B Value set A has as one of its parameters: (SELECT TOP 1 cashbalance - 1 FROM table1 WHERE userid = __ ORDER BY createdUTC, ID) Value set B ...
M S's user avatar
  • 21
0 votes
1 answer
69 views

MS SQL Dynamic Pivot from Stored Procedure result

I have 3 tables as Student, Batches and StudentAttendanceRegistry. I managed to join these tables and get a desired result as following, StudentID Name Year Month ...
inD4MNL4T0R's user avatar
1 vote
1 answer
383 views

Is it possible to add a query hint to an entire stored procedure in SQL Server 2022?

SQL Server 2022 has a new feature called Parameter Sensitive Plan Optimization. I don't want to turn it on for my entire database, but I have four stored procedures that I believe would benefit from ...
CB_Ron's user avatar
  • 291
0 votes
1 answer
46 views

Cannot pass parameter to select in MySQL 8.0

I'm writing a generic partitioning helper, and suddenly run into trouble. CREATE PROCEDURE `weekly_partition`(in schema_name_arg varchar(128), in table_name_arg varchar(128), in ...
Alex Povolotsky's user avatar
0 votes
2 answers
173 views

About version control on the programming scripts of SQL Server, e.g., stored procedures and functions

We have an instance of SQL Server (MSSQL) version 2016, and the database contains programming scripts, e.g., stored procedures and functions. For the programming scripts of MSSQL, we wonder how to do ...
James's user avatar
  • 149
0 votes
0 answers
389 views

Another way to Call DB2 stored procedure on data integration platform with outputs parameters

I want to call a DB2 stored procedure on one of the data integration platform, so-called as Lobster_data. Here, it is one of the example of executing stored procedure. create or replace procedure ...
dtc348's user avatar
  • 101
-1 votes
1 answer
734 views

Calling stored procedure with local custom type @ dblink

Newbie here. I am trying to execute stored procedure located at DBLINK. Here's my code: DECLARE TYPE ARRAY_VARCHAR2 IS TABLE OF VARCHAR2(32767); Vparams ARRAY_VARCHAR2 := ARRAY_VARCHAR2('p1', 'p2',...
Jasurbek Erkinboyev's user avatar
1 vote
1 answer
362 views

Call SQL Stored Procedure from Powershell to insert XML into a table

I have a working stored procedure: USE [DB_NAME] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[INSERT_XML] -- Add the parameters for the stored procedure here @...
Jeramy's user avatar
  • 113
1 vote
1 answer
56 views

What is the optimal way to handle literals that are referenced in NOT IN clauses of many queries within a Stored Procedure?

Back in the days of C (before C#) and in environments where memory was very limited or expensive (or both), there was a benefit to reducing repetitive use of a literal as it would reduce memory usage ...
Mark Freeman's user avatar
  • 2,181
2 votes
2 answers
130 views

Are SQL Server stored procedures backed up by default?

We have two servers running SQL Server 2019 (15.0.x). When we do a full database backup (by right-clicking the data base, then Tasks, then Backup), does that include backing up the all items under ...
FreeMan's user avatar
  • 221
0 votes
1 answer
181 views

Deadlock on DELETE statements (stored procedure & adhoc query)

I am experiencing an issue with deadlocks when running a stored procedure that is run every 15 minutes. There is also an adhoc query that is being run by an external program (I don't have access to). ...
Drees's user avatar
  • 11
-1 votes
1 answer
136 views

Oracle SQL Developer stored procedure

create table emp as select 7782 empno, 'CLARK' ename from dual; CREATE GLOBAL TEMPORARY TABLE emp (empno CHAR(20), ename CHAR(20)) ON COMMIT DELETE ROWS; insert into emp (empno,ename) ...
guradio's user avatar
  • 97
0 votes
0 answers
229 views

How to Union the results of sp_Help stored procedure?

I'm using the sp_help stored procedure to see all the database objects for a given database in SQL Server. However, I have 92 databases and each time I run this stored proc I have to manually copy &...
JTD2021's user avatar
  • 37
1 vote
1 answer
291 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
2 3 4 5
34