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

MSSQL CDC for views and stored procedures

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

Procedure insert into another table using cursor

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
21 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
11 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
33 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
185 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
46 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
31 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
54 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
54 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
73 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
50 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
0answers
130 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
22 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
46 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
40 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
44 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
50 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 ...
0
votes
1answer
154 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
49 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
24 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
32 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
31 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
19 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
145 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
882 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
95 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
509 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
28 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
43 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
89 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
102 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
73 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
121 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
90 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 ...
0
votes
0answers
16 views

MySQL stored proc to read from a table and insert into another table

I have 2 databses in the same host. My requirement is to read data from db1.test table Massage data. Replace state_cd with state_id from state table insert into db2.sample Get the auto generated id ...
1
vote
1answer
35 views

Is it possible to run a stored procedure automatically on a new database when it is attached?

Replication is causing issues on our daily snapshots. What we would like to do is remove replication automatically on a database when it is attached to the SQL server. Is this possible?
2
votes
4answers
72 views

For every inserted row, generate series of rows in another table with AFTER INSERT trigger in PostgreSQL

Let's say that a customer places an order with certain quantity and I want to insert this information into table1 initialy. At the same time, I want to generate a series of rows in the table2 with a ...
2
votes
4answers
286 views

Philosophy on stored procedures that perform updates: is a 'granular' update approach sufficient?

I am embarking on on a large project and have an inquiry about stored procedures in general. Let's say I have a User table. UserKey IDENTITY NOT NULL PRIMARY KEY FirstName nvarchar(50)NOT NULL ...
0
votes
1answer
25 views

File is getting generated into folder, But not a csv file

For the below SP, normal text file is getting generated instead of csv file. ALTER PROCEDURE [dbo].[GenerateCDR_Test] AS BEGIN DECLARE @FileName varchar(50), @bcpCommand varchar(2000) SET @FileName ...
1
vote
0answers
260 views

Unable to resolve column level collations

This whole stored procedure is to generate a csv file into the mention folder where file is getting generated but the headers are missing. --Adding missing headers into the file---- DECLARE @...
1
vote
4answers
141 views

How to write and read files from client machine SQL Server 2019

I need to prepare a stored procedure which export as many files as many tables I have in schema, but files need to be written on local machine on specific path e.g. C:\Temp\ not on SQL Server ...

1
2 3 4 5
31