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
1answer
24 views

Stored Proc hangs when no rows exist in cursor? [closed]

I'm on 8.0.19 if that matters. Here is my test proc: DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `testNullProc`() main:BEGIN DECLARE fileID INTEGER; DECLARE finished INTEGER ...
1
vote
0answers
21 views

How to write Procedure to fetch data from other table?

drop procedure if exists findClosestTimeStamp; delimiter $$ create procedure findClosestTimeStamp() begin declare tt1 time; declare dt1 date; declare view1 varchar(30); declare done int ...
0
votes
1answer
41 views

Moving data from staging table to production table uses up to 200GB of Log space

I have multiple staging tables containing data that needs to be moved to production tables. The process of moving this data is done within a transaction to make it possible to rollback everything in ...
0
votes
1answer
35 views

Dynamic SQL breaks statement

I am attempting to write a query that pivots a certain column that may have any number of distinct values in it. I am able to get this to work with static values, but when I try to parametrize the ...
0
votes
1answer
32 views

Pass a table name as a parameter to a stored procedure

I am trying to create a Stored Procedure, and pass the name of a table as a parameter and I get this message:Must declare the table variable "@tblName". What do I have to change to make it ...
0
votes
0answers
9 views

How to optimize full-text search stored procedure

Our beta database (same server) data is 1 month behind live. However we did not much changes. What's wrong is query time of stored procedure. Beta has no issue and return results in 2 sec top. Live ...
0
votes
0answers
57 views

Stored procedure runs slow on SQL Server 2016 compared to SQL Server 2019

The stored procedure is running very slow on SQL Server 2016 as compared to 2019. It's doing some common validation from a temp table and inserting data into primary tables and mapping tables. The ...
1
vote
1answer
28 views

How to calculate running balance for many accounts?

please help me fix query below to calculate running balance correctly. What I need to do is: Calculate running balance (debit- credit) by account_no Reset the balance when account_no changes Reset ...
0
votes
1answer
22 views

MySQL Stored Procedure Compiles Without Error (I want the error to occur)

I have a MySQL stored procedure that runs fine and does its job quite well. Yesterday, I edited it in MySQL Workbench and introduced a spelling mistake on purpose. For example: - Valid statement: ...
1
vote
2answers
43 views

Merge Datetime Ranges

I have been struggling to merge datetime ranges in oracle SQL/PLSQL (Database Standard Edition 11gR2). I am trying to merge datetime ranges so that the following data order_id start_date_time ...
0
votes
0answers
10 views

How can you see PostgreSQL procedures in Azure Data Studio?

If I use pgAdmin 4 to query PostgreSQL, I can see procedures section under materialized views. But, If I use Azure Data Studio I cannot see the procedures section. Any idea how to get to see the ...
1
vote
1answer
12 views

MySQL Store Procedure - Setting AUTO_INCREMENT value

I am in trouble coding a store procedure as shown below CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12)) BEGIN DECLARE `_rollback` BOOL DEFAULT 0; DECLARE ...
0
votes
1answer
17 views

Triggers or views on frequently updating table

We have a table which is frequently updating. We have a scheduler to update this table which runs every 10 minutes. There are not more than 50k records in this table but when scheduler runs, database ...
1
vote
2answers
46 views

How to create a running balance column based on account number?

I have a stored procedure that returns account statement for one or multiple accounts Here is the part I have issues with: returning a running balance in each record based on account number. My query ...
0
votes
0answers
19 views

Average Waiting Tasks Count / Resource Waits

I'm trying to help troubleshoot a sudden performance problem with SQL Server 2014 SP2 (12.0.5659.1). I don't have details on what exactly the scheduled stored procedures are doing, but I know that the ...
0
votes
1answer
22 views

MySQL - Pass input parameter into Cursor query

Is it possible to pass input parameter into Cursor SELECT statement WHERE clause? For some reason I think it isn't working. I'm trying to pass _TAG and _ITEM_NAME into where clause. DECLARE ...
0
votes
2answers
34 views

Permission to select sys tables in stored procedure

I have implemented the stored procedure to check if a specific [schema].[procedure] exists. CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist] @schemaName varchar(128), @...
0
votes
1answer
28 views

MySQL : Stored Procedure from Trigger giving duplicate results

I have created a stored procedure to insert data into an audit table. The procedure accepts an argument that takes table name and same value is inserted into the audit table along with PK. When i'm ...
0
votes
0answers
13 views

Function in Postgres that uses the table where I'm passing the function inside the function body?

I've got two tables, one with street_codes and street_numbers (addresses) and another with sidewalks_keys that correspond to each address (address_sidewalks_keys). I would like to create a function in ...
2
votes
1answer
27 views

Should “Long Running with Low CPU” be expected for an SPROC that performs millions of INSERTS?

Brent Ozar's sp_BlitzCache flags the sproc below as the most "expensive" one in my system ("Frequent Execution, Long Running With Low CPU"). The sproc just checks a table for a row corresponding to a ...
0
votes
0answers
7 views

Store Procedure to Copy One Column value of one table into another table row

I have two table tblDailyCheckIn and tblWeeklyTimeSheet. I need to copy working hours for every employee from tblDailyCheckIn and insert into Sunday, Monday, Tuesday, Wednesday etc of ...
0
votes
1answer
22 views

date variable manipulation

I'm working on DB2 stored procedure, which will be called by the Korn Shell (.ksh). My DB2 version is 11.5 and it is running on AIX. I was stuck in TIMESTAMP to DATE conversion. Below is my ...
0
votes
1answer
16 views

Inconsistent ResultSet from Procedure with Variables

I'm working on building a procedure with two inputs, and the Syntax checks out, but the result-set is not consistent with the logic. In this question, there are in actual fact two questions. Number 1 ...
0
votes
1answer
10 views

Mysql query - Event to delete rows by its value

need help here I want a query to delete all rows in which column value is older than today. One of the columns is 'check_in' (values in timestamp format) and I want the query to identify the row that ...
0
votes
0answers
41 views

Cascading Triggers

I'm running MariaDB 10.2.31 on Ubuntu 18.04.4 LTS. My data consists 99% of JSON-encoded strings from a remote API. Example (orders) very much simplified: { "id":123, "referrer": "webshop", ...
0
votes
2answers
47 views

My Procedure isn't working right. It keeps giving me an error

create or replace PROCEDURE CHECK_BLACKLIST ( P_EMPLOYEE_USERNAME IN VARCHAR2 , P_ROWCOUNT OUT NUMBER ) AS BEGIN SELECT COUNT(*) INTO P_ROWCOUNT FROM boe_safegaurd WHERE UPPER(...
0
votes
1answer
55 views

User info retrieval takes too long - ASP.NET Membership

I have an application that has been taken from a sharepoint user control and converted to plain web pages. Everything is working fine - I can sign up and retrieve my password, etc. However, when I try ...
0
votes
1answer
33 views

Unknown column in field list

Please help i dont know what i'm doing wrong. CREATE DEFINER=`root`@`localhost` PROCEDURE `setState`(IN `statename` VARCHAR(100), IN `countryID` INT(11)) MODIFIES SQL DATA BEGIN SET @...
1
vote
2answers
70 views

Error Code: 1136. Column count doesn't match value count at row 1

I'm struggling here. This is my code CREATE DEFINER=`****`@*****` PROCEDURE `setCountry`( IN `countryname` VARCHAR(100) CHARSET utf8mb4, IN `nationality` VARCHAR(100) CHARSET utf8mb4, IN ...
0
votes
1answer
17 views

Can't add or update child, Foreign Key Error

I'm kinder new in MySQL. I was trying to insert value in a relational database. I know that i have to insert in parent table before doing it in child table. Assume we have tables named STATE, COUNTRY....
0
votes
1answer
27 views

Generate GRANT for all tables reference by a procedure

I have a couple of stored procedures in my DB in which each one select from a couple of tables. I need to construct a statement where I can give it a stored procedure and get the grant select ...
1
vote
1answer
30 views

SQL Server evaluates where condition before or during the run?

I have a Stored Procedured that looks similar to this: CREATE PROC Campaign.STP_CampaignTransaction_Get ( @id INT @first_name VARCHAR(50) @last_name VARCHAR(...
0
votes
1answer
26 views

How to pass a variable to a stored procedure?

Calling the following procedure in DO $$ bloc seems to not work correctly . Procedure : CREATE OR REPLACE PROCEDURE pLog(P_VAR_LOG text) AS $$ BEGIN IF D_SetLog THEN RAISE NOTICE '[LOG %] ...
0
votes
1answer
31 views

Creating stored procedure in MySQL-8

I am trying to create a SP which will run batch update on about 100 million rows in a table. I created the below SP for it: delimiter // CREATE PROCEDURE `bulk_update`(IN table_name varchar(30)) ...
-2
votes
1answer
68 views

MySQL trigger calling stored procedure always get null value for out parameter of stored procedure [closed]

My stored procedure OUT parameter, always return a null value. Here is sample Table, Trigger and Procedure code. Table: test Columns: id - Int status - enum(‘pass’, ‘fail’) status - enum(‘pass’, ‘...
-1
votes
1answer
64 views

Use a temp table's result in a view [closed]

I am using a temp table in a stored procedure. I need to use that temp table's result in my view. How can I do that?
0
votes
1answer
42 views

Can someone help me with the SQL Server syntax for the my Oracle query

Oracle version: CREATE OR replace PROCEDURE Item_reco_recordset (p_depositid IN NUMBER, p_b1 IN NUMBER, ...
1
vote
1answer
45 views

Run stored procedures in other schemas from stored procedure [closed]

I have a current process: a large procedure in one schema. There is a plan to have A, B1, B2, B3 schemas (B-schemas are identical, could be B-n schemas) in Oracle 12c database. Schemas B1, B2, B3 have ...
0
votes
1answer
58 views

MS SQL - CLR Stored Procedure runs as deleted user

I have a SQL Server 2017 database which contains some CLR stored procedures. When trying to call those CLR SP's, I get a "login failed" error back from the CLR SP. My user is connected and works ...
0
votes
0answers
46 views

Quick question on Scalar UDF's

I am trying to browse online but can not get enough info on how to measure performance of scalar UDF's in SQL server 2014. Newer version yes i do see from plans we can get the same and newer dmv's ...
0
votes
0answers
20 views

Dynamically creating a new table from a given table name without return type using a PostgreSQL function?

I want to try using PostgreSQL to create a function which given a table name, an existing column name, and a new column name, it selects all data from this table, applies a function to this column ...
-2
votes
1answer
32 views

crosstab dynamic in mysql

this is output from query true like this: bln bt cl kj ma pj sb sn st wh 1 421 465 437 474 196 29 146 392 37 2 398 443 328 462 181 0 30 438 0 3 209 258 105 298 51 0 0 213 0 ...
-1
votes
1answer
85 views

PL/SQL returning Procedure value and putting it in an insert clause [closed]

I'm doing an assignment and the last question states that I call the procedure from question 2 and function from question 3 and then put them in an insert clause(question 4). Write a PL/SQL Code for ...
1
vote
0answers
21 views

MySQL 5.7 - Calling procedure with dynamic name

I'm trying to write a MySQL (5.7) procedure that dynamically calls another procedure based on the one of its inputs. The code I came up with so far is this (procedure_exists works for checking ...
0
votes
0answers
27 views

Hardening WITH ENCRYPTION option for stored procedure protection

Is there any way for make WITH ENCRYPTION option more difficult to bypass ? I have to protect the stored procedures code and hide it from other sysadmin of the instance. I can't manage it with ...
-1
votes
1answer
264 views

Stored procedure in SQL Server that returns total count of columns in table and distinct count of values in each column [closed]

I need a stored procedure that takes any table name as a parameter and returns total count of rows and distinct count of values in each column of that particular table when executed. Let's take the ...
0
votes
1answer
33 views

How to get the settings associated with a stored procedure in relation to filtered indexes?

I quite like using filtered indexes in the proper context. I have added the following filtered index: USE [COLA]; CREATE UNIQUE NONCLUSTERED INDEX F_InterviewReport ON [dbo].[...
0
votes
1answer
108 views

SQL Server - Add signature to stored procedure by certificate does not work

I have a stored procedure in Database 1 that updates couple of tables in Database 1 and Database 2 To avoid granting calling login/user permissions to do table updates, I try to use code signing ...
1
vote
0answers
30 views

Do I always need boilerplate code to prevent nested transactions?

Because I never know if my procedure might be called from other procedures that have an opened transaction, should I always check for existing transactions before opening a new one? It seems like a ...
0
votes
3answers
54 views

When do i need to DEALLOCATE a prepared statement in a MySQL stored procedure?

My question is simple. In what situations is it better to deallocate the prepared statement and thus removing it from memory and in what situations is it better to keep it in memory? I have written a ...

1
2 3 4 5
29