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
21 views

Check if contract conflict with another contract query

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
1answer
39 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
487 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
20 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
16 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
21 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
29 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
98 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
51 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
37 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
64 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
17 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
30 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
13 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
34 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
65 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
282 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 ...
0
votes
0answers
71 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 @...
0
votes
3answers
46 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 ...
0
votes
0answers
34 views

Convert SQL server stored procedures to postgreSQL

I have an SQL server database that I am migrating to postgreSQL 12.0.I have managed to migrate the schema and the tables. Now the applications connecting to the database relies a lot on stored ...
0
votes
2answers
40 views

Nested Stored Procedure Connections

I have a Stored procedure that simply looks for Imported files through a set of other processes. The main SP runs every 5 mins and looks for new files in a directory and imports them into a staging ...
0
votes
0answers
20 views

Is it possible to select from procedure\function when the PDB is an argument without connect?

I have the following function in oracle 11g. Create or Replace function get_col_value( schema# in varchar2, table# in varchar2, column# in varchar2, rowid# in varchar2) Return varchar2 Is v_cmd ...
2
votes
3answers
407 views

Where does the stored procedure Searchinall come from?

When I create a new database in SSMS either using the creation wizard or the CREATE DATABASE command the database will contain a stored procedure called Searchinall. This procedure has never been ...
0
votes
2answers
44 views

procedure not returning desired result

I am trying to generate a report from this procedure create procedure [dbo].[hstatement] ( @fromdate date, @todate date, @prd varchar(8) ) as begin create table #tempdata ( ...
3
votes
2answers
99 views

Display line number where error occurred in DB2 stored procedure

I have added an EXIT handler in my procedure that captures the SQLSTATE and SQLCODE, and even found a way to get the procedure name, but I also need to know where the error occurred. Suggestions ...
0
votes
1answer
79 views

Postgres 12: Inserting array of composite types, composite type has an array field. Malformed array literal, unexpected end of input

I'm having an issue with inserting a simple array of integers into my table in Postgres via a procedure. The array of integers is declared within a composite type and I'm trying to insert an array of ...
0
votes
1answer
30 views

sql server stored procedure - collection of data for fields as input [duplicate]

I have a stored procedure to add a collection of values to a field in table. How I implement it now: from my .net app, I create a string of comma separated values from an array( eg: resource1, ...
1
vote
2answers
58 views

Stop SQL job if it runs past a certain time

I have a SQL job which executes a stored procedure. The proc loops through a number of databases, then executes a series of dynamic SQL queries to archive/delete a bunch of tables in each database. ...
1
vote
3answers
150 views

DB2, How to save all stored procedures as files

How does one extract all stored procedures from a DB2 schema as individual files (without using Data Studio so it can be scripted). I need them all so I can uploaded to source control. I have found ...
2
votes
3answers
77 views

Retain indent formatting when compiling a stored procedure without Data Studio

When I compile a stored procedure using CLPplus console (or even in dBeaver), all indentation is stripped out. Only when I compile in Data Studio is the formatting retained, but I would like to ...
1
vote
1answer
46 views

MySQL: SELECT to local variable in stored procedure

I have the following mysql stored procedure: DELIMITER // CREATE PROCEDURE INSERT_INTO_WORKDAY(IN deviceName VARCHAR(16), IN cardUID VARCHAR(14)) BEGIN SET @...
0
votes
1answer
21 views

Procedure with “bind varibale” does not return the desired result set

I have a table with below structure: create table CUST_MODERN_CHANNEL_BRANCH ( customer_Num NUMBER, channel_Code NUMBER(5) ) Some example data : Customer_Num Channel_Code ========...
0
votes
1answer
30 views

How do you call an external C function from a SQL stored procedure?

In Teradata 17, I am trying to call their own hash_md5 external function from my own SQL stored procedure: replace procedure p() sql security creator begin declare l_hash varchar(32); set l_hash = ...
0
votes
1answer
40 views

What is the format of EXTERNAL NAME argument?

When a Java class cl with a method mth resides in a package pck, what should I put into the EXTERNAL NAME clause of CREATE PROCEDURE? I tried CREATE PROCEDURE mth (arg1 INTEGER) LANGUAGE JAVA EXTERNAL ...
0
votes
1answer
33 views

Generate script for all stored procedures in Azure SQL database

I'm trying to create a SQL script that will create all the stored procedures that were exported from another database. Basically navigating in SSMS to the database > Generate Scripts > select ...
0
votes
2answers
61 views

Passing database name to input parameter from a stored procedure

I have some issue with this application. I know that there are a lot of topics about passing a Database name as input but still I couldn't figure out what is the issue my code. Hopefuly, you guys can ...
0
votes
0answers
44 views

mysql multiple transaction rollback problem

I want to have multiple procedure call and one transaction commit, for example: call CreateRestaurant(?, ?, ?); call CreateMenu(?, ?, ?, ?, ?); call CreateMenuItem(?, ?, ?, ?); but I want to wrap all ...
0
votes
1answer
24 views

Write Oracle proc/function to create dynamic views with changing column headers

I had similar requirement earlier but now i have been given a clear approach that needs to be implemented. I need to write a procedure(preferably)/function, which would on basis of app_id passed as ...
0
votes
1answer
28 views

PLS_INTEGER giving Error

My procedure giving numeric overflow error: ERROR at line 1: ORA-01426: numeric overflow ORA-06512: at "HDM_DBUSER2.HDM_CLEANUP_UTIL", line 1196 because it exceeds pls_integer maximum ...
2
votes
1answer
191 views

Script to backup TDE certificate and Key throws error Cannot write into file

I am attempting to automate TDE key backups. I created the stored procedure below and was going to schedule it to be executed as a sql agent job. However, when I execute the procedure I get this ...
2
votes
2answers
75 views

Why MySQL performs so poor on SHOW PROCEDURE STATUS?

I've installed MySQL server with many databases that use the InnoDB engine. With lots of procedures in each DB, mysql.proc table contains more than 250,000 rows and it becomes ultimately slow when I ...
0
votes
1answer
30 views

Dynamic execution query produces error when called from the procedure, but works when executed manually

create procedure createTable(@tableName varchar(25), @columnName varchar(25), @columnType varchar(25)) as begin declare @sqlQuery as varchar(MAX) set @sqlQuery = 'create table ' + @...
1
vote
0answers
43 views

Recursively list dependencies / references of a stored procedure

I'm trying to import a subset of our production dump with impdp for our local development database running on Oracle XE. To find out what other objects I have to import for a particular stored ...
0
votes
0answers
32 views

What C# type is the correct representation for DB2 UDB boolean?

The project that I work on uses IBM.Data.DB2 nuget package for its data layer. I need to call into utl_mail.send_attach_varchar2 stored procedure on the DB2 side. It has a boolean parameter att_inline ...
0
votes
0answers
19 views

Not able to create a stored procedure for a table insert in DB2 UDB 10.5 [duplicate]

I want to create a very simple stored procedure that will only take several IN parameters and insert them into a table. To learn how to do it, I went to CREATE PROCEDURE (SQL) statement From that I ...
3
votes
2answers
50 views

At my wits end trying to create my very 1st MySQL stored procedure

I am trying to create a stored procedure in MySQL 5.5.8. using connector 8.0.13. Once this procedure is debugged, it will have to be embedded into a c#.net application, to be created on the fly every ...
1
vote
1answer
57 views

Problem with unmasking a database user during a sproc

I am having an issue with a stored procedure that refreshes dynamic data masking on a database. This sproc is run as a job against all databases on this server. The problem that I have is that I have ...
0
votes
2answers
82 views

psycopg2 connect error: cannot pass more than 100 arguments to a function?

I am trying to pass json object as a parameter to my stored procedure. I tried many things and solved couple of error atlast I am stuck with the error : Error : psycopg2 connect error: cannot pass ...
0
votes
1answer
25 views

Stored Procedure Signed by Certificate Unable to Add Members to Database Roles

I created a Stored Procedure that performs the Following tasks: Creates database Change owner to sa Set recovery model Creates users in database Adds users to database roles. Grants execute and view ...

1
2 3 4 5
31