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,621
questions
0
votes
0
answers
24
views
Linked server query occasionally returns results slowly
I have an unfortunate situation where I need to pull a next-number from a table from both a local and a linked server. To do so, I've created a stored procedure to return the result. When I execute ...
- 294
0
votes
1
answer
61
views
Cannot find the object because it does not exist or you do not have permission
Trying to do a simple
GRANT EXECUTE ON OBJECT::dbo.sp_add_job TO [user]
GO
and I am getting
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'sp_add_job', because it does not exist or you ...
- 3
0
votes
2
answers
30
views
How to update table keeping data available to users (Oracle)?
I have a table VISIT_HIST in an Oracle data warehouse. This table contains number of customer visits during every month and is rather big (~130 mn rows).
CREATE TABLE (
VALUE_MONTH DATE,
USER_ID ...
- 3
-1
votes
1
answer
34
views
Stored procedure to Insert data into table with select statement and argument parameter [duplicate]
I am creating a stored procedure which will populate my table with data passed from procedure argument and from other table.
I have a table with three columns (cart_id, product, sold_quantity). Now, I ...
- 229
0
votes
1
answer
35
views
Database Dependency Chart?
I have several SQL Server (2008 forward) and Oracle (12c forward) legacy databases running on a server... I know for a fact that various stored procedures point to other databases, like select * from ...
- 217
2
votes
1
answer
118
views
ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function
I need to execute CREATE MATERIALIZED VIEW ... WITH DATA from a function/procedure but I'm getting ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function.
There is any ...
- 247
0
votes
0
answers
5
views
js transform code applied to a postgres / timescale view
Let's assume this data type:
timestamp INT
ticker VARCHAR
price NUMERIC
with some example data:
0, A, 10
0, B, 100
1, A, 20
1, B, 1000
2, A, 30
2, B, 10000
...
And a JS function, implemented with ...
- 261
0
votes
1
answer
62
views
SQL Server: how to get a stored procedure to execute tsql from output
I wanted to see if it's possible to create a SQL Server stored procedure to run a select statement, which will generate a list of rows/users as the result set. I then want the result set to be ...
- 3
0
votes
0
answers
54
views
How to execute Stored Procedure by dedicated user with limited rights
My question is similar to Rights to execute stored procedure, but not read underlying tables directly, however my storedprocedure reads from different databases.
I'm trying to read databases matching ...
- 101
1
vote
0
answers
22
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`.`...
- 111
0
votes
0
answers
9
views
In rest api how to get parent_id have child_id in same database using node.js and used at regressive function
{
"error": false,
"message": "Data fetched successfully",
"exam_format": [
{
"id": "b2a9f30f-04bf-4735-b293-...
1
vote
1
answer
138
views
SQL Server procedure, to find and replace data inside big table
I need to write a store procedure to update link reference in my DB. Link can be contained in few nvarchar field that contains JSONs (that could contains some urls).
To do so i update the tables in ...
- 351
0
votes
2
answers
105
views
Msg 214, Level 16, State 201, Procedure master..xp_cmdshell, Line 1 Procedure expects parameter 'command_string' of type 'varchar'
i am using SQL Server Management Studio 2018
I have created a stored procedure where i want to generate CSV Files
CREATE PROCEDURE generate_csv_files
AS
BEGIN
SET NOCOUNT ON;
DECLARE @country ...
- 20
2
votes
1
answer
174
views
PostgreSQL: How to use INOUT parameter of a Stored Procedure called in dynamic SQL?
I have a stored procedure with an INOUT parameter, which value is modified inside the SP. I need to call that SP, providing a variable as that parameter, and then use the modified variable. But the ...
- 131
0
votes
2
answers
112
views
Try Catch w/Transaction and executing stored procedures
I need to create an error-handling method for a couple of Stored Procedures.
Proc1
CREATE PROCEDURE dbo.Proc1
AS
BEGIN TRY
BEGIN TRANSACTION
EXEC Proc2
INSERT INTO tableA (col1, col2) ...
- 35
1
vote
0
answers
76
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 ...
- 11
0
votes
1
answer
58
views
MS SQL Server: Dynamical create procedure / function / view / trigger in other database (sp_executesql)
Problem:
you want / need to use dynamic SQL to create procedure / function /
view / trigger in other databases than the current
you can't specify the database name in the statement, CREATE VIEW ...
- 845
0
votes
2
answers
55
views
How to convert getdate() stored as varchar to date
I have stored 'getdate()' as text in one of my column in a table. (It's a complicated scenario ,how I saved it)
Now when I select the field what I am getting is getdate() instead of the date . How can ...
- 95
0
votes
1
answer
37
views
Solutions for fixing stored procedures that rely heavily on global temporary tables?
I am working on re-writing some stored procedures. The last issue was an abuse of EXECUTE AS USER='someUser' to use the default schema and work against those.
The second thing I just noticed though ...
-1
votes
2
answers
41
views
How to increment as 001,002 etc In SQL Server
i have created an sp to export scripts i stored in a table to text files. What this sp do is if I give 100 as count then each file will have 100 scripts from the table. i am saving the file name as ...
- 59
-2
votes
2
answers
97
views
Making SQL give age brackets on command
I'm trying to make SQL return how many users are inside each age bracket grabbing the date of birth from the user from the table GUEST and converting it into years, at the moment I'm trying to make it ...
0
votes
0
answers
25
views
Update child tables when an id field updated In main table
I have this sp to delete child from child tables when something deleted from the main table .
Now I want the same function with update statement . When I update the id field on main table . I want to ...
- 95
0
votes
1
answer
134
views
Performance benefit of dividing a frequently used stored procedure into multiple stored procedures in SQL Server
I have a stored procedure usp_appdata that is used by our application loading screen as well as a lot of other places in the application. This SP is very resource intensive and returns a lot of data. ...
- 319
0
votes
2
answers
44
views
Is there other options for SQL-like languages?
I'm currently writing a stored procedure in MySql, but soon noticed a few shortcomings. First of all I'm using cursor on a large set of data (~50 million rows) and do calculations on those. However, ...
- 1
1
vote
1
answer
69
views
update current row doesn't trigger attached function
To update a column in PostgreSQL using the function which is invoked by a trigger on each row add/update-
Steps I followed:
First, let's add the field "line_updated_dt_tm" to the table of ...
- 109
0
votes
0
answers
53
views
Sorting not working in mysql stored procedure
The procedure works, but sorting of dynamic columns does not work, according to their data type (each column has its own data type: CHAR, SIGNED, DECIMAL...). Column names are created dynamically &...
- 1
2
votes
0
answers
50
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. ...
- 95
0
votes
1
answer
196
views
Infinite running loop while executing procedure
I have this table with 3 records. 3 records are create scripts. I need to execute the create scripts and if there is a 208 error occurred then I need to keep the record and set status as 1. Then ...
- 95
0
votes
1
answer
66
views
getting the name of the function which caused trigger to activate in postgresql
Is there a way to get name of the function/procedure, which contains query, which caused a TRIGGER to activate?
For example I have function update_user_whatever and it updates user data and I want to ...
0
votes
2
answers
293
views
PostgeSQL Schedule Jobs
I am looking a way that can give the ability to schedule jobs on PostgreSQL, for example to schedule execution of some procedures or statements based on interval or on specific schedule.
I tried ...
- 163
1
vote
1
answer
103
views
SQL query to get the order of procedures based on their dependent procedures
i have this query to get all sp's and depending(nested) sp's on those sp's. I need to create all these procedures on another database . Any idea how to get them in the correct order.
select distinct ...
- 95
0
votes
1
answer
56
views
SQL query to script all objects from a database in order
i have a script to get all objects (functions ,views ,triggers and procedures from a database . I need to create this in another database . The problem is some procedures may have another procedures ...
- 95
0
votes
2
answers
36
views
Internal Paramaters on sp_MSdel<TableName>, sp_MSins<TableName>, sp_MSupd<TableName>
The following procedures sp_MSdel, sp_MSins, sp_MSupd are the outcome of an configured publication/subscription on SQL Server.
These procedures let you do small transformations inside them so the ...
- 163
0
votes
1
answer
89
views
Query with stored procedure
I have a query that grabs data from a .csv and inserts into a table I created, now I need to take a single column from my destination table and use it to gather data from another database from another ...
3
votes
1
answer
295
views
Performance problems in SQL Server. Parameter Sniffing or not in my scenario?
Here I am facing a performance problem that occurs with particular statements in a stored procedure, that has many statements, on occasion
SP is executed once a second, normally completes less than 50 ...
- 5,144
1
vote
2
answers
118
views
What is the fastest way to log the Input and Output parameters when executing Stored Procedure
Normally i will log this information at the end of each SP. The syntax looks like this.
DECLARE @SPContent nvarchar(4000)= (
SELECT @ServiceID [ServiceID],
@AccountID [...
- 11
0
votes
0
answers
388
views
Wrong number or types of arguments in call to procedure
I am getting Below error message in the catch exception:
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'WS_GET_WEB_TREE_NODES' ORA-06550: line 1, column 7: PL/...
0
votes
0
answers
43
views
How to get server name in springboot
I want to execute a stored procedure based on the server name.
Below is the stored proc.
@Modifying
@Query(nativeQuery = true, value = "EXEC sys.sp_wait_for_database_copy_sync @...
0
votes
1
answer
165
views
Obtaining schema name of PostgreSQL stored procedures
I'm trying to obtain schema names of stored procedures in PostGRESQL as I'm writing a query to scan through all existing stored procedures on the server and return those that contain a specific ...
0
votes
0
answers
32
views
How should the procedure structure using Transaction be?
I want to make both error checks and insert and update in a single procedure.
There are triggers in the tables that I insert and update in the procedure, and other procedures work in these triggers.
...
- 91
0
votes
1
answer
48
views
Creating a variable procedure statement
I have an procedure statement as below. My update sentence changes according to the @role parameter here.
Is it possible to dynamically make the sentence in the procedure according to the "...
- 91
0
votes
0
answers
76
views
set a trigger dependent on multiple tables
Is there a way to set trigger dependent on multiple linked tables? (linked by relationship such as PK/FK relationship). The use case is given below:
I've got 5 tables like this:
TableA{
idA SERIAL, ...
- 109
0
votes
0
answers
30
views
Need to Add new parameter to Working stored procedure in MYSQL
This SP is used in front end it has 7 parameters need to add one more parameter(as i have done) so that
call proc_123(2018-01-10, 2022-10-07 ,-1, 1, 50,-1 ,-1,'airport');
- 1
0
votes
0
answers
42
views
Updating parameter values from a PostgreSQL procedure InOut JSON parameter
The JSON is being passed into a PostgreSQL procedure _get_xml_status(INOUT i_json json).
I need to update the xmlInfo.Status parameter for each of the xmlName nodes depending on what I find for each ...
- 9
0
votes
0
answers
276
views
Procedure updating table with CLOB data type column does not work properly
I have a table with below structure:
create table bb_analysisitems_batch
( batch_id number,
batch_name varchar2(100),
batch_result clob )
There are other ...
- 1,390
1
vote
1
answer
189
views
Update rows recursively based on previous rows by individual
I am struggling in finding a faster way to update the data I am currently working with. More precisely, I have workers' contracts data with starting and ending date for each contract. However, ...
0
votes
1
answer
441
views
Routinely deleting data from tables using stored procedure
I've created a maintenance stored procedure to delete data older than 30 days on events table.
What are the best practices for deleting data?
I've encased the procedure with
error handling, i.e. TRY......
- 11
3
votes
1
answer
72
views
What are the usecases for stored procedures (`CREATE PROCEDURE`) as of Postgresql 14? [closed]
I've had an impression it was some go-to way of encapsulating complex SQL logic. But digging related responses, such as this one, it doesn't seem to be the case.
So are CREATE PROCEDURE-related tweaks ...
- 176
0
votes
1
answer
258
views
How to find tables with self referencing Foreign keys
Hi is there any query to get all self referencing foreign keys . I need to get the all tables and the foreign keys in a database with sled referring and drop and create it .Any idea how to do it .?
- 59
1
vote
1
answer
21
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 ...
- 11