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 vote
0 answers
15 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 ...
-1 votes
0 answers
28 views

Can I search stored procs for all columns used in join statements and where clauses?

I am trying to come up with an Index maintenance process which will pull all columns used in join statements and where clauses from stored procedures. Does any logic exist which would help me ...
0 votes
2 answers
42 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) ...
1 vote
0 answers
21 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 ...
-2 votes
0 answers
23 views

issue time changed from 12 hours to 24 hours when copy from excel to sql server 2017? [closed]

I work on sql server 2017 i have date field name datelogged with datatype datetime2(7) my table structure as below CREATE TABLE [dbo].[print_report]( [ID] [int] NOT NULL, [datelogged] [...
0 votes
1 answer
27 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 ...
0 votes
2 answers
40 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 ...
0 votes
1 answer
25 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
35 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 ...
-2 votes
2 answers
81 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
18 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 ...
0 votes
1 answer
69 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. ...
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
24 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 ...
0 votes
0 answers
32 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 &...
2 votes
0 answers
44 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. ...
0 votes
1 answer
95 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 ...
0 votes
1 answer
29 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
116 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 ...
1 vote
1 answer
70 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 ...
0 votes
1 answer
42 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 ...
0 votes
2 answers
28 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 ...
0 votes
1 answer
70 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
249 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 ...
1 vote
2 answers
90 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 [...
0 votes
0 answers
239 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
34 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
64 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
46 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
41 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
13 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
33 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 ...
0 votes
0 answers
161 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
88 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
213 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
52 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 ...
0 votes
1 answer
149 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 .?
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 ...
-1 votes
1 answer
720 views

How to use temp table or while loop instead of cursor

i have an sp to delete data from grandchildren tables . But I need to change the cursor and use a temp table or while loop instead of the cursor. I have tried but was not working can somebody help. Sp ...
0 votes
0 answers
135 views

pg_cron timezone issue, unable to run at specified time

Unable to run at proper timezone. I want to run two things bash scripts Stored procedure For both case I found it run at different time. Instead of 1 AM and 3 AM pg_corn runs another time. Why it is ...
0 votes
0 answers
49 views

Unable to delete data from dynamically assigned table at PostgreSQL13

Unable to delete from dynamically assigned table data at PostgreSQL13. Basically I want to archive 10 days older PostgreSQL data to another table. I want to pass days, source table and target table as ...
0 votes
0 answers
94 views

T-SQL: Slow performance on Stored Procedure Sending Data to LinkedServers

I am new to T-SQL and I am facing an issue with a slow processing stored procedure. The purpose of this procedure is to transfer table data from child databases up to their corresponding parents. The ...
  • 101
2 votes
1 answer
92 views

Procedure not looping properly

I have several client databases on one server. They all have a prefix. Lets use "piggy_" as the prefix for the purposes of this. I.e. piggy_client_a piggy_client_b piggy_client_c Below is ...
  • 123
0 votes
1 answer
218 views

How to map the newly created records in parent table with the child table?

have a table Employees Id Name EmployeeType_ID 1 John 1 2. Mathw 2 EmployeeTypes Id TypeName 1 Perm 2. Temp 3. Regular Now I am copying data from one server to ...
0 votes
1 answer
40 views

Searching and deleting MySQL records after insert with stored procedure

I have a quandary with which I am struggling to answer, and looking for some advice on how it can be done. We have a table, that is basically comprised of the following fields. I have used Info x to ...
  • 33
-1 votes
1 answer
895 views

SQL Server stored procedure taking too long sometimes even though indexes have been added with memory grant issues

I have a stored procedure that takes too long sometimes even though I have added all suggested indexes. I keep getting the Query had to wait for memory grant The query plan can be found here: Query ...
0 votes
0 answers
467 views

How to make stored procedure with bulk insert from Blob storage?

Im building a stored procedure to bulk insert from a .csv file located on Azure Blob Storage. I got it to work, by passing storage name + filename, and the SAS code. At first, it was ok, I ran the sp ...
user avatar
1 vote
1 answer
215 views

Is it safe to use user defined variables inside stored procedures in MySQL?

Currently I'm working with Stored Procedures in MySQL and I'm using in some procedures user-defined variables and I've seen that type of variables are initialized in the current session and keep their ...
0 votes
2 answers
162 views

SQL Query execution with IN statement

I didn't get proper result Below is my tables values : DECLARE @Id as nvarchar(MAX) = N'''2'',''3''' DECLARE @spparams as nvarchar(max) =N'@Id as nvarchar(max)' EXEC sp_executesql N'select * from ...

1
2 3 4 5
33