Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

2
votes
1answer
60 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 ...
0
votes
0answers
14 views

Stored Procedure call from Web App creates Very Large File on C drive [on hold]

We have a SP that is a Select that is creating a very large file (7GB+) on our C drive. Our Databases are stored on a separate drive. I've searched to determine the location of these files without ...
6
votes
1answer
441 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
41 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
55 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
21 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
36 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
119 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
25 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
216 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
40 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
21 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
112 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
55 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
801 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
30 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
1k 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
14 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
40 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
33 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
61 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
13 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
31 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
678 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
73 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
640 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 ...
1
vote
0answers
101 views

Stored procedure to UPDATE row to “closest” match in one table from data in another table

I have a table called ThawLaneA that has a populated column ThawTime that has DATETIME value. The first row starts at a specific time, and each row from there "increases" by a specific amount of time (...
0
votes
0answers
38 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 ...
2
votes
0answers
81 views

Stored Procedure: How remove Comments, spaces, and extra lines, or companies binaries?

We are trying to compare stored procedure codes. Example, these two stored procedure codes produce same result. (1) Is there method to convert stored procedures into binaries to validate they ...
3
votes
2answers
329 views

Get top 1 row value from third table while joining 3 tables mssql

I am new to mssql .here I need to get some data using joins between three tables . Header join Lines join Images --> Result Header Table : Line Table : For each header record we can have multiple ...
0
votes
1answer
166 views

DB2 - how to call a stored procedure that returns a result set in another user defined table function

I have a db2 stored procedure that takes in some parameters, gets some data from somewhere and then returns a result set through a cursor. Now I want to write a table function in db2, that will call ...
2
votes
1answer
102 views

Stored Procedure: table-Value parameters

We have a number of stored procedures where we pass in a user defined table type parameter. As per the MS doc below, my understanding is that SQL Server doesn't retain statistics on these (and ...
0
votes
1answer
28 views

Pass 'interval' value to date_trunc function in PostgreSQL procedure

I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns. The syntax in the ...
0
votes
0answers
43 views

init_connect not executing

From this question I was able to make my init_connect = "CALL mydb.Warnings()", but a user login in does not triggers the SP. the SP is like: CREATE DEFINER=`root`@`localhost` PROCEDURE `Warnings`...
2
votes
3answers
131 views

How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?

Weird question?...maybe, but I have a need. :) I have a stored procedure that I want to use universally in any database. The stored procedure generates some dynamic SQL and then executes that SQL in ...
1
vote
1answer
27 views

How can I get a stored procedure to create 2 temporary tables and send 2 emails from the separate tables?

I am writing a stored procedure which creates 1 temporary table populates and then emails the results through if it is above a certain threshold. I am wanting to alter this so that if the results are ...
0
votes
1answer
197 views

Error when creating a stored procedure in postgreSQL-10 [closed]

I couldn't get my first stored procedure to work, so I copied this example from the manual: CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL AS $$ INSERT INTO tbl VALUES (a); INSERT ...
0
votes
1answer
101 views

CASE in WHERE clause in MySQL

I'm working in a project with MySQL and using stored procedures to make my SELECT queries, so all of them have this same structure: DELIMITER $$ CREATE PROCEDURE `case_in_where`(IN `column_selector` ...
2
votes
1answer
61 views

When to Use Functions over Stored Procedures

What advantages/circumstances do functions have over stored procedures? Want to justify use for function. It seems stored procedures benefit in every way, from transaction handling, input, multiple ...
1
vote
2answers
78 views

Stored Procedure on a read-only AlwaysOn Availability Group replica

We have a read-only reporting server that is part of the AlwaysOn availability group. Our developer wants to create some stored procedures. My question is where should the stored procedure be ...
0
votes
1answer
20 views

SQL Job step history to display DML changes

A vendor provided a script to run with many DML statements and i dump this in a stored procedure to run it from SQL job. When running the script via SSMS, it returns affected row counts after going ...
0
votes
2answers
56 views

select query with where clause having variable taking more time to execute

I have a query which selects a couple of rows from a view based on the variables declared. I ran the same query in lower environments and to my surprise, I got the result in less than 3 minutes ...
3
votes
0answers
132 views

Combine multiple stored procedures into one report?

This is my first time doing this... For my assignment, I need to combine multiple stored procedures into one big report using a cursor. There is only one table Debt from the database college. Here is ...
1
vote
1answer
106 views

SQL Creating Error Log Table Benefits

We are conducting a datawarehouse project, moving data from Staging database to Kimball Data Warehouse. What is the purpose of creating a custom stored procedure Error Log Table? I am seeing a lot ...
1
vote
1answer
151 views

Find SSIS package which calls stored procedure

We are trying to catch errors into an Error Log Table, similar to below. We have many Master SSIS packages, calling many stored procedures. Following code will log into the Error Log Table. Is there ...
0
votes
1answer
23 views

Oracle: How do i manage two versions of a stored procedure at the same time?

Here is the situation: There are some stored procedures (3 - 5 no's) already in production. we made some changes for a fix. It also involved changes to the code that is using these stored procedures....
0
votes
1answer
19 views

How can I have intensive stored procedures run on a different machine to where tables are stored?

For a home air quality monitoring project I am building, I am collecting sensor readings and storing them on my Synology server, which includes a MariaDB instance. The Synology is great for managing ...
7
votes
0answers
94 views

SQL Server 2016 & 2017 Force Plan failing with reason 8695

I'm trying to force a plan using query store but it keeps failing with reason 8695 according to data in SYS.QUERY_STORE_PLAN the last_force_failure_reason_desc is 'General_Failure'. Does anyone know ...
1
vote
0answers
16 views

Filtered Merge Statement [duplicate]

This is, probably, a really simple answer, but I am not coming up with it... Any help would be greatly appreciated: I created a web form, to a stored procedure, that submits userID, deptID, and a ...
2
votes
0answers
66 views

SQL Server stored procedure not throwing certain kind of error

I noticed an issue in my app recently where an database insert that occurred in a stored procedure was failing, but no exception was getting thrown. The insert failed because a column that doesn't ...