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
-2
votes
0answers
22 views

Why am I getting 'Error converting data type nvarchar to numeric' [closed]

Can someone tell me why this Stored Procedure is erroring during Execute? - (5134 rows affected) *Msg 8114, Level 16, State 5, Procedure sp_PBI_GetProjectMetrics, Line 65 [Batch Start Line 2] Error ...
0
votes
1answer
23 views

Create new sql table based on a query from another table MariaDb

Just to preface this I am very much a noob when it comes to dbs. As the title states I want to create a new table based on query I do on an already existing table. At first I thought a view would be a ...
0
votes
1answer
23 views

Postgresql - Is calling a stored procedure in a trigger possible?

I would like to call a stored procedure using a trigger in Postgresql. Is it possible? Here is an example: create table foo(n int primary key, n1 int); create or replace procedure ...
0
votes
0answers
46 views

JSON_EXTRACT always returning null in stored procedure

Hi I'm new to SP and JSON function in mysql DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `adocs_sp_web_questionnaire_has_child`(IN `var_has_child_text` TEXT, IN `var_parent_id` INT, OUT `...
3
votes
0answers
35 views

Nested cursor under update command [closed]

I am building a procedure to label database rows if certain records meet some conditions. I am looping over rows using cursor and for every row I check for some conditions, if conditions are met I ...
-1
votes
1answer
21 views

Passing array with varying dimensions to postgres function [closed]

I am trying to process a group of transactions as one transaction with Postgres. I receive a group of transactions to process which I want to pass to Postgres to process all together or not at all. ...
1
vote
0answers
25 views

SQL Server to PostgreSQL with unique Constraint

I am trying to push data from a MS SQL Server to a PostgreSQL (both on my local device) by using the pg as linked server and calling a stored procedure of the SQL Server. A simple INSERT INTO [...
0
votes
2answers
35 views

How to resolve SQL error in stored procedure

I'm using MariaDB 10.5.9, my stored procedure: CREATE DEFINER=`trainer`@`localhost` PROCEDURE `updateTrainee`( IN `_txtTrainee` TEXT, IN `_field` TEXT, IN `_data` VARCHAR(16) ) ...
0
votes
1answer
27 views

MSSQL Merge 3 hierarchy tables

I want to merge 3 hierarchy tables into one table. The 3 tables: CREATE TABLE dbo.tbl1 ( key1 varchar(50) NULL, key2 varchar(50) NULL ) ON PRIMARY CREATE TABLE dbo.tbl2 ( key2 varchar(50) ...
0
votes
0answers
28 views

MSSQL CDC for views and stored procedures

is it possible to enable CDC for Views or Stored procedures? BR, Val
0
votes
1answer
71 views

Procedure insert into another table using cursor [closed]

I'm creating a procedure, using cursor, which checks in different tables if the current quantity of 5 products are below their minimum quantity. If the current quantity is below I create a PURCHASE ...
0
votes
0answers
26 views

Using Powershell and ODBC connection to Postgresql, how can I catch the Notices from a call procedure?

I have a procedure that will raise some output. call my_procedure(param1, param2); table "temp_ts" does not exist, skipping INFO: No BR has been applied. INFO: Params are valid. Code ...
0
votes
1answer
12 views

MariaDB procedure, from a previous select create another select and return both

I've this tables in my database, and I'm trying to select all the career info, including the specializations it has. CREATE OR REPLACE TABLE School_Catalog ( school_id INT UNSIGNED PRIMARY KEY NOT ...
0
votes
0answers
23 views

How to cast a string into an identifier?

I have a stored procedure and a table. In the table the name of the stored procedure is stored. CREATE OR REPLACE PROCEDURE "hello" () LANGUAGE plpgsql AS $$ BEGIN RAISE INFO 'Hello World!'...
0
votes
0answers
108 views

Permissions to run SQL Server Agent Job

I am getting the following error message: Message Executed as user: fin\jdoe. The server principal "fin\jdoe" is not able to access the database "abc" under the current security ...
4
votes
5answers
400 views

Handling optional parameters in stored procedure

I have a stored procedure, say "Test" with an optional parameter @Param1 int = NULL. In the procedure, the value of @Param1 is used to update the value of some column in some table, if the ...
1
vote
1answer
50 views

Stored Procedure with two options that execute different actions depending on their values

These were my instructions: Create a stored procedure that receives two parameters: @i_action in which: 'I' = inserts a value 'U' = Updates values 'D' = Delete Values 'C' = Consult and @_option ...
0
votes
0answers
43 views

Writing and execute a SELECT Stored Procedure in PL/SQL

I have a problem when i try to write a simple slect stored procedure in PL/SQL (I use a PL/SQL Developer IDE). I'm new in PL/SQL but i have experience in MS SQL where I wrote quite complex stored ...
0
votes
1answer
117 views

SQL profiler not showing incoming parameters of store procedure

My Task When user updates some values on the front end UI of an application, I want to know which SQL table/fields are updated, which SQL statements were executed, etc. My method Using SQL profiler, I ...
0
votes
0answers
56 views

SQL Server stored procedure sending unwanted rows

I am using SQL Server stored procedure to send email with Excel attachment. It is sending email with attachment, but there is a unwanted row that I do not want, so please help me to remove them. I ...
-4
votes
1answer
232 views

I am using sql server to send email from stored procedure with excel attachment. it is sending email with attachment but not in incorrect excel format [closed]

I am using sql server stored procedure to send email with excel attachment. This procedure is sending email with attachment but not in correct excel format. Excel is not proper it is disturbed ...
2
votes
1answer
69 views

I'm trying to use sp_ineachdb, please can anyone see where I'm getting it wrong?

Can anyone help please? I can't seem to find where I'm missing it. I'm trying to loop through the user databases in my SQL Server instance to delete all the existing users. I'm using the stored proc ...
4
votes
1answer
140 views

Is there a more optimal way to create this view or stored procedure? Currently using a cross apply, but it's slow

I've updated this post with some test data. I'm creating a report for my Movies database where I'd like for the end-user to be able to select movies of a certain genre. However, some movies have ...
1
vote
1answer
36 views

MariaDB: Create dynamic PrepareStatement inside a Procedure

I am trying to make a stored procedure which returns me the records of a table that match filters that can be applied in layers. The procedure receives certain variables as parameters and I want to ...
0
votes
1answer
53 views

Partition table design and house keeping (MSSQL)

I'm new to design Partition table, So my question is If I need to daily partition and keep the back up for 6 months, Should I create only one file group or create file group per day? which should be ...
-2
votes
1answer
48 views

How to auto run oracle stored procedure [duplicate]

Is there any possibility to auto run procedure after a specific time/date? For example I want to auto run this stored procedure after 2 days: CREATE OR REPLACE PROCEDURE deleteresetlinks AS BEGIN ...
0
votes
1answer
60 views

Performance issue when multiple columns of same table are grouped

I am dealing with large amount of data approx 1 million rows with 100s of columns. I have this proc which performs some calculation over this data based on grouped by a colum1. Now the same ...
0
votes
2answers
52 views

Lockdown database access on certain tables

I have a dev team who have a number of SQL databases used as a back end for mostly excel front end. This originally came from an excel spreadsheet until it got made into SQL databases. At first, they ...
1
vote
1answer
323 views

Create a Hangfire job via SQL stored procedure

I know how to create Hangfire jobs via .NET C# code, but there are a few occasions where I'd like for a database trigger to execute a Hangfire job. I searched the web but found nothing so I attempted ...
0
votes
3answers
205 views

Concat a string to a variable to use as a column name in a query

I have a procedure where I query table a and create variables like this SELECT TRCODE , COLA , COLB , COLC , COLD INTO TRANSACTION_TYPE , HOURS , RATE , FACTOR , APPROPRIATION FROM PR . PREXEC WHERE ...
0
votes
1answer
26 views

postgresql: procedure to return the number of occurrences of given year and service [closed]

I am creating a procedure to return the number of times a given service is done in a year. The procedure cannot return a NULL value. The table I am taking the data from is called "tb_orders" ...
0
votes
0answers
35 views

Performance Lag between Execution

I am facing a unique problem related to SQL Server query execution. I have around 150 stored procedures, which load data sequentially to the data warehouse tables. All stored procedures called inside ...
0
votes
2answers
45 views

Stored procedure prepared statement no error and not working [closed]

Here is my stored procedure: BEGIN #Procedure: insertMetaTagIfNotPresent #Parameters: bkID ID to find or NULL to create automatically # vcTag Tag name to assign, must be ...
1
vote
1answer
22 views

Stored procedure support in MySQL Workbench?

I've created a lot of stored procedures when I was using HeidiSQL on Windows. I am now on an iMAC (x64) and it doesn't look like installing HeidiSQL is an option. I've got MySQL Workbench version 8.0....
0
votes
3answers
48 views

How to give access to a user on only two specified procedures and specified tables on SQL Server?

We would like to introduce an external orchestrator in order to feed some tables from two stored procedures on SQL Server. However, we will need to create an 'orchstr' user for the orchestrator. We ...
0
votes
2answers
307 views

How to make a function in postgresql with two parameters to return count from column table?

I'm trying to do a function with two in parameters year and code from a table to return the count of the given code and year. The table is something like this: CREATE TABLE orders ( id INT NOT ...
8
votes
2answers
1k views

Why is part of transaction committed when stored procedure faults?

I have a stored procedure that begins by declaring a few variables then contains begin tran; After this it performs some validations on the supplied arguments (and increments an error count each time ...
-1
votes
1answer
49 views

Check if contract conflict with another contract query [closed]

I have a database with these tables as the bellow picture showed: The user can create a new contract each contract may have one or more shops and these shops status changes depending on the contracts,...
0
votes
2answers
99 views

SQL Server Management Send Mail as Part of a Job

I have a SQL Job that I would like to send an email through, but for some reason I can't get any details on why the email is not sending. This code works as expected when executed in the query window, ...
4
votes
3answers
537 views

MS SQL Server 2014: which stored procedure is my error in?

As a general question or three on Microsoft SQL Server 2014, that perhaps I should know the answer to already, or even perhaps did once - When my stored procedures run other stored procedures, how can ...
0
votes
2answers
32 views

Passing parameters in MySql procedure with prepare execute

I am trying to write a procedure that has a location parameter, this parameter is later used in the queries inside the procedure. Also, I want to be able to limit the query results dynamically. I have ...
0
votes
0answers
20 views

Optimisation of MySQL procedure to generate tree

I have MySQL table like this |-----------------------------| |IDENTIFIER | POINT1 | POINT2 | |-----------------------------| | NAME1 | AA1 | BB1 | | NAME2 | AA1 | BB2 | | NAME3 ...
1
vote
1answer
90 views

How to change the default isolation level of a transaction in a stored procedure in PostgreSQL PL/pgSQL?

I am interested in controlling the isolation level of a transaction inside a PostgreSQL procedure but haven't found any information on how to do it. The documentation on Transaction Management within ...
0
votes
1answer
175 views

SQL Server: sp_describe_first_result_set with more than one select in a stored procedure

I'm using sp_describe_first_result_set to retrive metadata from a stored procedure and having the following error: Msg 11512, Level 16, State 1, Procedure dbo.sp_describe_first_result_set, Line 1 [...
0
votes
1answer
105 views

Counting MAX event simultaniusly (based in datatime2) within a tallytable

Hi, I have a task that is meant to summarize several connections coming in to a given set of ports (trunks), into timeslots and count the results. The thing is that I would like to achieve two type of ...
0
votes
1answer
53 views

Store Procedure, Group multiple tables by time interval to new table: daily run schedule

Currently working on a thesis where I have been tasked with aggregating data to a new table I'm trying to build out an aggregated table where I'll store each row group by date and a time interval (let'...
1
vote
1answer
92 views

Netezza migration to Azure Synapse - Stored Procedures?

I have looked everywhere for useful documentation that comprehensively gives me the differences between Netezza stored procedures and functions so I can find Azure Synapse equivalents. Is there any ...
2
votes
1answer
187 views

SELECT query in a SP in AlwaysOn Availability Group Read Only Routing

There is a stored procedure which has select statement as well as insert and other statements. What will be the behaviour of the stored procedure in a Read Only Routing configured in AlwaysOn ...
0
votes
0answers
18 views

Store Procedure Two different database and join help

I try to write store procedure that ı have a question.Firstly I have two db in same local server.One of them is hfcenta_test db has agency old datas and it has country code column with country name....
0
votes
1answer
152 views

Creating map and use that within the mysql stored proc

I am working on a migration project and the data needs to be migrated from system A to system B. The id's in system A and system B will be different. So maintaining a table with source and target ...

1
2 3 4 5
31