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
30 views

How to update new records when data is inserted using sql server?

We are facing a problem, i want to modify current store procedure. So it will once there is a new inserted data, so it can update it not duplicate data with same data and end up sending an email to ...
thrue2023's user avatar
2 votes
1 answer
123 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
106 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
0 votes
0 answers
50 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
37 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
63 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
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 ...
BigKiwiDev's user avatar
0 votes
1 answer
67 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
18 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
87 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
35 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
183 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
  • 93
3 votes
3 answers
600 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
  • 93
1 vote
2 answers
133 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
134 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
  • 93
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
1 vote
3 answers
141 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
40 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
102 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
71 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
39 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,041
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
0 votes
1 answer
81 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
41 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
39 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,500
0 votes
1 answer
36 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
32 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
77 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
132 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
103 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
549 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
144 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
61 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
300 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
38 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
145 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
331 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
496 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
325 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
55 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
112 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
164 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
123 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
213 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
221 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
0 votes
1 answer
44 views

How to rapidly compare definitions of many tables of two PostgreSQL databases?

I have two PostgreSQL databases, the one for producing, and the other for testing. Both of them have a lot of tables with same table names. Now I need to upgrade the producing system to the new ...
Leon's user avatar
  • 347
-1 votes
1 answer
59 views

How to rollback data mart update if data quality checks are not passed?

Good afternoon! I am working with Oracle database. I have a PL/SQL procedure which is scheduled with DBMS job to update every night a table SOME_TABLE: CREATE OR REPLACE PROCEDURE MY_PROC IS ...
rsx's user avatar
  • 1
1 vote
1 answer
32 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
293 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
2 3 4 5
34