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.
1,683
questions
-2
votes
0
answers
21
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 ...
0
votes
0
answers
18
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 ...
- 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 ...
0
votes
0
answers
22
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 ...
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 ...
- 113
2
votes
1
answer
164
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 ...
- 35
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 ...
0
votes
0
answers
56
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 ...
- 33
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'...
- 103
0
votes
0
answers
65
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,
...
- 25
0
votes
0
answers
7
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 ...
0
votes
1
answer
120
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 ...
- 15
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
- 1
0
votes
0
answers
111
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].
[...
- 11
0
votes
2
answers
53
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 ...
0
votes
2
answers
189
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 ...
- 139
3
votes
3
answers
647
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 ...
- 139
1
vote
2
answers
154
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 (...
- 5,589
1
vote
1
answer
199
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 ...
- 139
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 ...
- 21
1
vote
3
answers
197
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 ...
- 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: ...
1
vote
1
answer
207
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 ...
- 11
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....
- 1
0
votes
1
answer
43
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 ...
- 1,053
1
vote
0
answers
36
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....
- 11
0
votes
1
answer
118
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 ...
- 159
0
votes
1
answer
50
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 ...
- 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 ...
- 3,540
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(...
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 ...
- 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 ...
- 1,960
0
votes
2
answers
156
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 ...
- 291
0
votes
1
answer
107
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, ...
- 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
...
- 1,461
-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 ...
- 1
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 ...
- 21
0
votes
1
answer
66
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 ...
1
vote
1
answer
375
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 ...
- 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 ...
- 109
0
votes
2
answers
168
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 ...
- 149
0
votes
0
answers
382
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 ...
- 101
-1
votes
1
answer
711
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',...
1
vote
1
answer
358
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
@...
- 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 ...
- 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 ...
- 221
0
votes
1
answer
179
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).
...
- 11
-1
votes
1
answer
135
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) ...
- 97
0
votes
0
answers
227
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 &...
- 37
1
vote
1
answer
274
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. ...
- 183