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.

3
votes
2answers
256 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
18 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
87 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
0answers
7 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 ...
-1
votes
0answers
30 views

How to create stored procedure involving wildcards? [on hold]

I have a question, I want to write a stored procedure given a name in the where clause of the query, return all matching identities of the staff members who have staff name that contains the queried ...
0
votes
0answers
29 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
122 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
23 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
37 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
32 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
55 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
31 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
16 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
48 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
49 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 ...
2
votes
1answer
48 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 ...
2
votes
1answer
29 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
21 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 ...
5
votes
0answers
71 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
38 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 ...
0
votes
0answers
36 views

How to modify a column in a result set returned by a stored procedure (Sybase)

I have a Stored Procedure in Sybase that selects a bunch of columns. The result set contains multiple rows. I want to modify one of the columns in the result set before the Store Procedure returns. ...
0
votes
0answers
45 views

Ignoring Null (optional) Variables in Stored Procedure

I am looking for what is hopefully something easily achievable without a ton of coding. I have a stored procedure that does some work and has about 20 optional parameters: CREATE PROCEDURE dowork ...
1
vote
1answer
44 views

AlwaysEncrypted error when inserting via stored proc

lets start with the error first: Encryption scheme mismatch for columns/variables . The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '3' ...
2
votes
2answers
45 views

How to grant user rights to create stored procedures in a custom schema

I'm trying to grant SQL Server login rights to create stored procedures and bind them to a custom schema. In this case I've created a schema called IC. A service account will then be added to the ...
1
vote
1answer
113 views

Splitting large database into smaller ones

Our system currently stores all customer (merchant) accounts in one "flat" MySQL (5.6) DB namespace. We would like to scale out better - we are considering breaking our data up based on the merchant ...
0
votes
1answer
32 views

Get full tree hierarchical query

I want to get all data of tree by ID. My table is: ID NAME PARENT_ID ------------------------ 1 A NULL 2 B 1 3 C 1 4 D 3 5 E 4 6 ...
1
vote
1answer
62 views

Stored procedure executable only after shrink database [closed]

I am using SQL Server 2005 (I know...it is old!) and I wrote a stored procedure where the main part makes a few JOIN and OUTER APPLY between tables with 500.000-1.000.000 rows table, then INSERT the ...
1
vote
1answer
57 views

alternatives to passing temp table as a parameter to a stored procedure

some time ago I was developing a simple procedure to get the database sizes on a particular server. Here it is the procedure: USE MASTER GO ALTER PROCEDURE dbo.sp_getDBSpace @Radhe sysname=null, ...
-2
votes
1answer
30 views

Error in stored procedure on Phpmyadmin [closed]

I have created this simple StoredProcedure in the Phpmyadmin SQL console but I keep getting syntax errors. I have looked at similar created StoredProcedures but can't seem to figure out what the error ...
0
votes
0answers
35 views

Run query dynamically in store procedure oracle

Problem Select all the tables of database where column match than pass table name to next query using loop. If column name and column values matches than return true and exist for loop CREATE OR ...
0
votes
0answers
22 views

DDLTrigger_Audit error “The user does not have permission to perform this action”

I have a DDLTrigger_Audit enabled in my database when I try executing a stored procedure having Drop and Create index statement in it fails with the below error. database "XXX" failed on Jan 17 2019 ...
5
votes
0answers
93 views

Do I need a “downtime” to update a stored procedure? (Due to “definition changed” error)

I always thought that updating a stored procedure was something I could do while users were actively using the system. But I am currently testing out an update to a stored procedure and while a long ...
3
votes
1answer
103 views

Does setting DEADLOCK_PRIORITY to LOW cause a stored procedure to run slower?

I have a Stored Procedure that I have written that deletes rows from a table in the database. When I will run the Stored Procedure in the Production environment it will run whilst another program is ...
0
votes
3answers
75 views

Slow performance matching columns to lists of values

I've made a stored procedure to get all results which match specific students and objectives. CREATE PROCEDURE [dbo].[GetResults] @students [IdList] READONLY, @objectives [IdList] READONLY AS SELECT ...
-2
votes
1answer
57 views

What difference between stored procedure and query in application [closed]

What difference between stored procedure and query in application. Performance , network load, server load and speed Thank you very much
0
votes
1answer
108 views

sp_DatabaseRestore msg 50000

I used Ola's script to backup my database (SQL Server 2017) and now I'm trying to restore it using Brent's sp_DatabaseRestore. Currently trying to do a Full restore only. Here is my code, written ...
0
votes
2answers
226 views

Sql Max value from multiple columns in Computed Column

In this article solution 1, it talks about finding the maximum value from many columns. I would like to conduct this in a computed/persisted column. How would I do this? https://www.mssqltips.com/...
0
votes
2answers
52 views

Multiline UDF or Stored Procedure for SSIS Data Source

We are creating an SSIS Data source with complex code. I read MultiLine Table Functions are slow. Would utilizing a stored procedure produce any faster results when streaming data from one place to ...
0
votes
0answers
30 views

Primary key error when running in C# but not SSMS

I have written a stored procedure to insert a record into a table that is used with our ERP system. When a record is written to this table there are triggers on the table that were written by the ...
0
votes
1answer
94 views

How to create stored procedure that returns query result?

I have query that has a lot of columns (about 30) and I would like to place that query in stored procedure. The reason behind that is because this query is used in multiple places in the system. There ...
1
vote
0answers
105 views

Improve parallel running of bulk copy insert and stored procedures

I have a Java message listener, which upon receiving a message starts bulk inserting thousands of records into a table. After that it calls a few stored procedures sequentially that make some updates ...
0
votes
1answer
34 views

What does “_localserver_view” mean after an MSDB Database table

What is equal to the database table [msdb]. [dbo]. [sysschedules_localserver_view], which contains the suffix _localserver_view? I am having a permissions problem with a [msdb].[Dbo].[...
0
votes
1answer
42 views

MySQL syntax error 1064 near IN stored procedure

an aspiring DBA Here. I'm trying to create a stored procedure for a query I'm running. It is a memsql server which is MySQL based. The procedure is as such: delimiter // CREATE PROCEDURE ...
0
votes
2answers
50 views

How to efficiently handle querying for different combinations of optional parameters

Consider the following Tables: Table: Parts part_id INT PK part_number VARCHAR Table: Jobs job_id INT PK job_number VARCHAR Table: Job_Parts part_id INT FK PK job_id INT FK ...
0
votes
1answer
45 views

Find if Stored Procedure Error

Is there a method in T-SQL to see if error occurred in Stored Procedure, without the use of @Output or try catch? We have a lot of legacy code 200+ stored procedures. I have seen @Output method and ...
0
votes
1answer
18 views

How to optimize this MySQL routines [closed]

Using MySQL 5.7.22 Run pt-query-digest on slow query log # Query 6: 0.00 QPS, 0.00x concurrency, ID 0x5863FD9F47316C48AE1FBD931F05AA56 at byte 9577338 # Scores: V/M = 2.36 # Time range: 2018-10-...
0
votes
2answers
67 views

Running SSIS jobs sequentially in T-SQL

We are loading ETL data in two parts, first with an SSIS package and then stored procedure. In the script below, Part2 stored procedure will run even before SSIS Part1 is complete. How do I get Part2 ...
1
vote
1answer
59 views

How to format Procedures for complex searches

So I am trying to create a way for users to search through data on my SQL Database. Right now I've been getting away with using a single view using a lot of Joins: CREATE VIEW PartsData AS SELECT ...