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
-1 votes
1 answer
34 views

SQL Server Management Studio "Key cannot be null" error when giving a specific user permissions on Stored Procedures

I'm attempting to give a specific user execute permissions on a Stored Procedure. When I attempt to do so, I get an error: Key cannot be null. Parameter name: key (System)" When I attempt to ...
LYNITS's user avatar
  • 1
1 vote
1 answer
73 views

Trouble with slow stored procedure performance on 35M rows with CTE/Temp table

We recently went through a new feature release to our app that added about 25 new columns to a large table (~35 million rows), and now we're having some major query performance issues. I assume that ...
David Allen's user avatar
0 votes
1 answer
44 views

Conditional update

I would like to create a conditional update on an audit table, such that if the update on the main table succeeds, then the update on the audit table is performed. Right now I have this CREATE DEFINER=...
tcelvis's user avatar
0 votes
0 answers
49 views

Calling stored procedures multiple times in the same transaction

I have a PLSQL Package with several stored procedures inside. This package is used to create specific reports. I call the procedures like that : package.init(parameter1 => 'Blablabla'); package....
Luke's user avatar
  • 1
1 vote
0 answers
48 views

MySQL InnoDB Stored Procedure locks table?

MySQL 8.0 InnoDB I have some sql statement like SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; INSERT INTO MyTable SELECT Name FROM MyTable ...
onets's user avatar
  • 11
3 votes
2 answers
277 views

How to create stored procedure in remote server using linked server

I tried to create an stored procedure in a remote server using this way EXECUTE (N'USE [master]; EXEC sp_executesql N'+ @sqlCommand) AT [LinkedServer] However I received this error Msg 111, Level 15,...
lego con tsijiari's user avatar
-1 votes
1 answer
41 views

SQL code to calculate amount based in parent child relationship

account code amt abc x 123 abc y 456 efg x 321 efg y 654 sdf x 123 sdf y 345 sdf z 234 x is the parent code and y and z are the child code, if same account is present for both parent and ...
Shweta Srivastava's user avatar
-2 votes
1 answer
90 views

Data Is Null. The Method or Property cannot be call on null values. Exception

In my application ultiple users request the API and in the API I am calling the stored Procedure via Entity Framework but some times getting exception Data Is Null. The Method or Property cannot be ...
Diya Rawat's user avatar
0 votes
0 answers
25 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
10 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
1 answer
46 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
20 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
309 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
117 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
1 answer
45 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
67 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
11 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
1 vote
1 answer
286 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
77 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
179 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
118 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
205 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
  • 555
3 votes
3 answers
752 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
  • 555
1 vote
2 answers
248 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
511 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
  • 555
2 votes
0 answers
37 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
440 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
  • 127
0 votes
1 answer
90 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
411 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
103 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
75 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
49 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
225 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
67 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
72 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,620
0 votes
1 answer
62 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
43 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
82 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
  • 2,000
0 votes
2 answers
234 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
118 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
  • 581
4 votes
1 answer
589 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
165 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
84 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
544 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
65 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
225 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
493 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
1k 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
436 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
2 3 4 5
34