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
0answers
26 views

Stored procedure in my production server causing blocking [on hold]

I have a MS SQL server production server. We have a stored procedure, which is consumed by multiple clients like website and mobile app. We are noticing a steep increase in the blocks with the ...
0
votes
0answers
22 views

Describe a result set returned from a stored procedure in the Golang driver for Db2 for LUW [migrated]

I have two tables: create table test2 ( id integer not null primary key, first_name varchar(100), last_name varchar(100) ) create table test3 ( id integer not null primary key, home_address ...
0
votes
1answer
23 views

Outputting result as 2 separate columns

So I created a procedure that calculates the average and median of a string array of numbers: CREATE PROCEDURE dbo.spAvg_Median @input nvarchar(20) AS BEGIN declare @split table(id int ...
-1
votes
1answer
19 views

Triggers and Tables with SQL and NodeJs

Hi I 've a application runnning with nodejs and SQL server, What I need is that every time I Insert a record into the ChargesTable I need to check, how may charges does my user have made, and every 5 ...
-4
votes
1answer
81 views

SQL Server database slow performance [closed]

I have a SQL Server database in production, and typically the performance of the application connected the database is satisfying. For some days many clients were complaining about slow performance of ...
0
votes
0answers
26 views

In PostgreSQL how to create procedure to splits data into multiple table

input table name : DATA name ---- abc xyz rcm cmr uvw tuy required output: output table1 name: tab1 name ---- abc xyz output table1 name: tab2 name ---- rcm cmr output table1 name: tab3 name --...
0
votes
2answers
19 views

finding duplicates and updating a column with values according with number of duplicates using mysql stored procedure

I am using Mysql 10.1.29-MariaDB Database to create a new table. What I am trying to do is to increment a number for each duplicate appearance of a value of company_name in another column. For ...
0
votes
0answers
41 views

IIS Causing High SQL Processes Load in SQL Server [on hold]

Sometimes, - especially since last 7-10 days- there are hundreds processes are coming from a spesific web application which calls same stored procedure many times at the same time in my production sql ...
0
votes
1answer
89 views

The publication 'My_publication' doesn't exist?

We are trying to test the success of Transactional replications. by Googling , i found that i need to execute in our publisher this stored procedure : sp_publication_validation However , When i ...
1
vote
2answers
46 views

Procedure to find a specific EXPRESSION in all tables in a specific schema in ORACLE

Hope you're doing well. Since I'm new to Oracle database and PL/SQL it's a bit difficult for me to understand complicated queries and now I'm trying to figure out what this procedure exactly does.I ...
0
votes
1answer
28 views

SSIS job — how to create stored procedure that repeats SSIS jobs?

Hey I realize I can probably create a modular parent package that repeats SSIS jobs in Visual Studio itself .... but I know you can execute SSIS packages using a stored procedure in SSMS -- 'exec ...
-1
votes
0answers
11 views

Mysql subtract multiple record with multiple records of another table in FCFS pattern

I have a inventory table of item_in and item_out. Now whatever item goes out from inventory should be on basis of FCFS. Now what i want how much items are remaining in the stock. My inwards table has ...
-3
votes
2answers
49 views

How can we get the output fields of a procedure?

I have written a procedure but I want to figure out what columns (fields) in the output without running the process?
2
votes
1answer
28 views

How to read T-SQL time statistics?

I have a simple stored procedure as an example, which reads a table with 3 rows. CREATE PROCEDURE demo_procedure AS BEGIN SELECT * FROM my_table END When I run it with time ...
0
votes
1answer
63 views

Stored Procedure error “Must declare the scalar variable” [closed]

I am trying to create a stored procedure with dynamic SQL inside of it. The proc receives 5 variables, 3 of which are names of columns and fields and another 2 are for OFFSET and FETCH NEXT part. I ...
0
votes
1answer
47 views

Cannot find slow (frozen) running query in Query Store [closed]

I have huge issues with a stored procedure that has not finished the last week. It seem slike it stops at around 140 000ms of CPU time and freezing there, while the elapsed time increases. This makes ...
0
votes
1answer
28 views

How to get my result from Procedure

How can i get my out parameters from my procedure. My stl_mbc_cdr table has over 100 records and i cant see my results. declare call_charge VARCHAR(10); MSG_OUT VARCHAR(50); BEGIN FOR ...
2
votes
2answers
39 views

MySQL - Can we use the same temporary table name in different stored procedure?

In our database, we generate statistics by periodically calling a large number of stored procedures in the event. Considering there are two SP auto_update_daily_users auto_update_daily_orders Is ...
-1
votes
1answer
18 views

i need to change all my user databases from full recovery to simple by using command in four servers

I need to change all my user databases from full recovery to simple recovery by using command in all my servers. need to create a stored procedure can you please give me a solution .. Thanks in ...
0
votes
1answer
16 views

In a MySQL cursor, will it be possible to skip an iteration or row of the cursor?

Before asking this question, I was checking if there were any similar questions, but I did not find what I'm looking for. Here we go!. The problem is that I have a cursor that is crossed, but believe ...
3
votes
1answer
155 views

What is the proper event in Extended Events to track stored procedure executions?

My goal is to track executions of a single stored procedure using Extended Events In the event selection list, I can find following: sql_statement_completed sql_batch_completed ...
3
votes
1answer
83 views

How to grant execute permissions to a stored procedure but not to the underlying databases

I am trying to allow a user to run a stored procedure while restricting them read/write access to the underlying tables. I am able to complete this with other stored procedures, but the one I am ...
-1
votes
2answers
60 views

When is an Insert committed?

If I highlight/select both the statements below, without a GO stmt between them, will the Insert be committed once it shows 'Rows Affected 1234' in results pane? If so, can I then cancel the Rebuild ...
-3
votes
1answer
31 views

Return result sets from stored procedure in Postgres v12

How to return result sets from a procedure in Postgres v12(beta). The link below says that changes have been done in Postgres v12 beta. https://wiki.postgresql.org/wiki/TODO#Server-Side_Languages: ...
2
votes
1answer
76 views

Troubleshooting a slow performing encrypted stored procedure

I have a stored procedure which is part of a vendor supplied system. The stored procedure is encrypted so we can't see the code. The stored procedure has all of a sudden started to run slow. The ...
6
votes
1answer
460 views

Mysterious procedure calls without parameters - but no exceptions generated

I've been tracing a handful of stored procedures with EventClass RPC:Completed that are intermittently called from .Net SqlClient Data Provider without required parameters - just "exec SprocName". ...
2
votes
2answers
47 views

Stored Procedure - Reseed Identity

I have a bunch of LU tables that have values added and deleted all the time. Therefore, I figured writing a quick stored procedure would serve me well and then can call the stored procedure on another ...
4
votes
1answer
81 views

Execute Permissions for a Store Procedure that creates databases

I have a Stored Procedure that creates a Database for a specific project which needs to run every month by one of our Data Analysts. The question is how do I structure this to enable the Analyst to ...
0
votes
2answers
23 views

SQL USING CAST in a query inside a stored Procedure

I was trying to use a query inside an Stored procedure which has a cast funtion. But when executing the SP I'm getting an error Conversion failed when converting date and/or time from character ...
0
votes
1answer
39 views

PostgreSQL, Stored Procedures, Recursive call Tree structure

I'm faced with a problem.I have a ternary structured data. The relation is from child to parent, more specific a child has an parent_id. A parent shouldn't have more than 3 children. When 4th child ...
7
votes
1answer
145 views

After Restoring Log Shipping to Secondary Server, First Stored Procedure Execution is Slow

We've set up log shipping to a secondary SQL server on Standby/ Read-Only to offload all SSRS report generation. This works fine within the restrictions imposed by: Kicking out the user during the ...
1
vote
0answers
28 views

Proc For SSRS Report shows a high number of executions ExecutionLog shows low Execution count for Report

So, I am using SolarWinds DPA tool to monitor DB activity and obv do things based on that information. I found a stored procedure that is being used for a SSRS report that has a high number of ...
5
votes
1answer
276 views

Creating Stored Procedure in local db that references tables in linked server

I currently have a local server linked to a remote server. Is it possible to create a stored procedure within the local server, but querying data from tables within the linked server. I am aware ...
2
votes
1answer
63 views

Sync'ing MySQL stored procedures to Git

Is there a decent way to sync MySQL database source code (ie: functions and stored procedures) to git? I'd like to be able to develop stored procedures within my IDE and version control them in git ...
0
votes
0answers
30 views

Insert values into a table using a trigger with values from stored procedure

I'm a newbie here. So, please feel free to correct me if my code or thinking approach can be worked upon. I am working in MySQL Workbench and MySQL 8.0 Command Line Client. I have a database called ...
5
votes
2answers
243 views

Executing a stored procedure which selects and inserts into tables in SQL Server

I am a bit confused on the permissions required to execute stored procedure. For example, if you have 2 tables and a stored procedure which reads data from Table1 and inserts data into Table2, will ...
1
vote
2answers
215 views

Returning multiple result sets from server-side procedures in Postgres 11

This blog post by a Postgres consulting company mentions that the new server-side procedure support in Postgres 11 would be able to return multiple result sets. ➥ Did this feature indeed appear in ...
8
votes
3answers
845 views

Can a stored procedure reference the database in which it is stored?

Suppose I have a stored procedure that is duplicated, with some modifications, in several databases. And I want to reference the database in which the stored procedure is stored, even if it is ...
4
votes
1answer
63 views

postgres notifications from inside stored procedures/triggers not immediately received by node.js listener client

We are using postgres 11's stored procedures to do looped update of rows in batches. After a batch is updated, a 'progress' table is updated with progress percent and status, THEN a notification (...
6
votes
4answers
2k views

Writing a T-SQL stored procedure to receive 4 numbers and insert them into a table

I need to write a stored procedure to receive 4 numbers and insert them into a table. This is what I've developed so far: Declare @1 Int = 10, @2 Int = 20, @3 Int = 30, @4 Int = 40 Create ...
0
votes
0answers
16 views

Unable to view MySQL db stored procedure

We have many stored procedures created with SQL SECURITY DEFINER by user A Now user B is responsible for maintaining some of the stored procedure. However user B is unable to view/update the stored ...
1
vote
1answer
50 views

executing a stored procedure on a monthly schedule

I'm looking to create an invoicing procedure that executes automatically once a month. The procedure name is INVOICE_PROC. I have tried to use a trigger to execute it but I can't get it to work. ...
1
vote
1answer
94 views

Syntax error when using exception inside for loop [closed]

I am trying to create the following procedure on postgresql CREATE PROCEDURE run_all_scheduled_tasks() LANGUAGE plpgsql AS $$ DECLARE rec record; BEGIN FOR rec IN SELECT * FROM some_table ...
-3
votes
3answers
63 views

if statement in a query [closed]

I am getting an error while running this code: Incorrect syntax near the keyword 'IF' I need the output through function only with table return . CREATE FUNCTION [dbo].[...
0
votes
1answer
18 views

Do I need to change the delimiter when creating a procedure using PHP MyAdmin?

I know I need to change the delimiter when manually creating a MySql Procedure but I can't find evidence on how to do it when using the PHP MyAdmin new procedure form or is it even necessary to change ...
-4
votes
1answer
34 views

3 Tables Inner Join [closed]

I have 3 tables: tblUser, tblRole, tblPage. Below are the data in the tables: tblUser userID: 1 (Primary Key) userName: Bob roleID: 1 (Foreign Key) tblRole roleID: 1 (Primary Key) roleName: Admin ...
7
votes
2answers
686 views

how to create a data type and make it available in all Databases?

If I create a stored procedure in the master database, and I want to execute it from any of my databases I just follow this link: Making a Procedure Available in all Databases that give me this code ...
0
votes
0answers
83 views

Regarding NIGHT SHIFT Employees

I have small issue on night shift of our employees. Shift timings are from 22:00 to 6:00. If the shift starts today at 11 PM it will end the next morning at 6:00 AM (7 Hours ). Here I want to know ...
8
votes
3answers
676 views

CAST throwing error when run in stored procedure but not when run as raw query [duplicate]

Something very odd is happening here. I have a query that looks like this. SELECT CAST(FT.DOP AS SMALLINT) FROM TRACKING_DATA WHERE date > @mydate and identifier = 0000000000 When run as a raw ...
0
votes
0answers
39 views

Quering Postgresql digest function inside procedure works on pgadmin but not works by calling via Laravel

recently I have created stored procedure in Postgres Sql which deals about with hashing passwords. When used via PgAdmin tool it works like a charm as stated below. I have also installed extension ...