Skip to main content

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
23 votes
2 answers
71k views

MySQL: Tree-Hierarchical query

SUB-TREE WITHIN A TREE in MySQL In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor (...
Grijesh Chauhan's user avatar
10 votes
1 answer
16k views

When to use stored procedure / user-defined function?

I'm confused about usage of SP (stored procedures) and UDF (user defined functions). Typically, it is also possible to write code in programs outside of the database. Is there any general advice to ...
DANG Fan's user avatar
  • 211
11 votes
2 answers
15k views

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

I understand the distinction between: Scalar functions Set-Returning-Functions (SRF)s Internal functions Window functions Aggregate functions of all sorts User-Implemented functions (which in ...
Evan Carroll's user avatar
  • 63.2k
20 votes
4 answers
105k views

Call a stored procedure from a trigger

I have created a stored procedure in mysql using the following syntax. DROP PROCEDURE IF EXISTS `sp-set_comment_count`; DELIMITER $$ CREATE PROCEDURE `sp_set-comment_count` (IN _id INT) BEGIN -- ...
Mark D's user avatar
  • 1,110
13 votes
3 answers
7k views

Dynamic SQL in MySQL stored routines

According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And ...
Derek Downey's user avatar
  • 23.5k
49 votes
1 answer
78k views

Dump only the Stored Procedures in MySQL

I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
nakhli's user avatar
  • 733
16 votes
1 answer
85k views

Setting user permissions for different SQL Server schemas

I need to limit access to a particular user, but they still need to be able to see the data in tables owned by dbo. I'm trying to do the following: dbo schema functions as it would normally, has ...
Julia McGuigan's user avatar
12 votes
2 answers
15k views

Performance of a Trigger vs Stored Procedure in MySQL

A post here on DBA.StackExchange (What are the best practices for triggers to maintain a revision number on records?) has spawned an interesting question (at least, interesting to me) regarding ...
Richard's user avatar
  • 6,393
5 votes
1 answer
11k views

Move values from one table to another one via stored procedure in sql server 2008

I have two tables Login (userid, username, password) audit_login (userid, Transaction_Date, Status (success or failed) Now I want create a stored procedure in which, if anyone wants to login then ...
user31595's user avatar
30 votes
3 answers
36k views

Stored procedures vs. inline SQL

I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why. I'd like to know the ...
webdad3's user avatar
  • 572
14 votes
2 answers
54k views

Can mysqldump dump triggers and procedures?

Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db? Some time ago I read that mysqldump will also save my triggers, but it doesn't look like it....
dole doug's user avatar
  • 391
11 votes
2 answers
1k views

How can I fake inet_client_addr() for unit tests in PostgreSQL?

I have a simple stored procedure whose return value depends on the value of inet_client_addr(). How can I override inet_client_addr() for the purpose of unit tests when testing my stored procedure? ...
Flimzy's user avatar
  • 599
8 votes
2 answers
20k views

DROP PROCEDURE IF EXISTS not included in mysqldump

I'm dumping my stored procedures only using the following command: mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt databasename -u username -p > outputfile.sql but the ...
nakhli's user avatar
  • 733
2 votes
1 answer
14k views

Recursive Query in MySQL using stored proceedure and CURSOR

I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships. For simplicity sake, lets say users ...
oucil's user avatar
  • 506
1 vote
1 answer
1k views

decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

i recently testing the move of our db from one server to another. as part of this process i copied all the data to the new db and started testing it. as a comparison i run a store procedure whose ...
Raven's user avatar
  • 113
64 votes
7 answers
78k views

What are the differences between "Stored Procedures" and "Stored Functions"?

So a comment to this question mentions, that there is a slight difference between "Stored Procedures" and "Stored Functions" in PostgreSQL. The comment links to a Wikipedia article ...
DrColossos's user avatar
  • 7,237
30 votes
2 answers
52k views

How to rollback when 3 stored procedures are started from one stored procedure

I have a stored procedure that only executes 3 stored procedures inside them. I am only using 1 parameter to store if the master SP is successful. If the first stored procedure works fine in the ...
user2483342's user avatar
16 votes
1 answer
56k views

Declare variable of table type in PL/pgSQL

I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like: q1 = select * from foo; q2 = select * from bar; ...
JRR's user avatar
  • 475
8 votes
3 answers
3k 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 ...
Chris Rice's user avatar
5 votes
1 answer
11k views

Who changed/modified stored procedure in SSMS 2005 or 2008? [duplicate]

Possible Duplicate: Determining how a schema change occurred? I have a stored procedure that was modified late last night and just spent the majority of the morning tracing issues back to this ...
User Smith's user avatar
0 votes
2 answers
1k views

improving performance - how to pass a table as parameter in sql server 2005?

I have been monitoring the execution of my stored procedures in my webservers in order to tune them for best performance. I will call the procedure that has most executions per day as myprocedure and ...
Marcello Miorelli's user avatar
53 votes
4 answers
291k views

What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

Many times I need to write something like the following when dealing with SQL Server. create table #table_name ( column1 int, column2 varchar(200) ... ) insert into #table_name execute ...
Just a learner's user avatar
43 votes
4 answers
87k views

Table-Valued Parameter as Output parameter for stored procedure

Is it possibile to Table-Valued parameter be used as output param for stored procedure ? Here is, what I want to do in code /*First I create MY type */ CREATE TYPE typ_test AS TABLE ( id int ...
adopilot's user avatar
  • 2,423
11 votes
2 answers
8k views

'Conversion failed' error with ORDER BY CASE expression

I have the following Stored Procedure that works great EXCEPT when I set the 'OrderBy' to 'OrderNumber'. I get the following error: Conversion failed when converting the nvarchar value 'SK11270' to ...
VaticNZ's user avatar
  • 385
9 votes
4 answers
8k views

How can I know which stored procedure or trigger is using a table on SQL Server 2008 R2?

This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in ...
farp332's user avatar
  • 193
9 votes
1 answer
20k views

How to output more than 4000 characters in sqlcmd

I've made a stored procedure restoredatabase that returns code to restore a database. The output script looks like this: restore database [DB] from disk='F:\FULL1.bak' with norecovery restore ...
user1261104's user avatar
8 votes
1 answer
199 views

Giving server-role to login breaks stored procedures

I have a login called user1. user1 can execute stored procedure [Test].[SP1] EXEC [Test].[TestSortation] EXEC [TestSortation] Both of the above commands work. Now if I give the user1 the server ...
BenVlodgi's user avatar
  • 265
4 votes
1 answer
1k 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 ...
JPVoogt's user avatar
  • 223
3 votes
1 answer
3k views

stored procedure can select and update tables in other databases - minimal permissions granted

I recently had to make the stored procedure spPartyOrderAllocation_AllocateItems to select and update tables in other databases, while keeping the pemissions levels to the minimum. This was ...
Marcello Miorelli's user avatar
2 votes
2 answers
16k views

Issues with module signing and SSIS catalog internal procedures

I'm trying to set up a stored procedure to execute an SSIS package. I want to allow a user to execute only this stored procedure, so I need it to run as a different user. I found this article which ...
Mansfield's user avatar
  • 1,021
15 votes
6 answers
17k views

Suddenly Slow Execution Plan for Stored Proc

I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, ...
Ciaran Archer's user avatar
14 votes
2 answers
46k views

Why does a query run slower in a Stored Procedure than in the Query window?

I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure? Here's what my query looks ...
Rachel's user avatar
  • 8,477
14 votes
1 answer
17k views

What are the privileges required to execute a trigger function in PostgreSQL 8.4?

What are the privileges required to execute a trigger function in PostgreSQL 8.4? It seems that the privileges set to a role does not matter to execute a trigger function. I think I have seen some ...
user avatar
13 votes
6 answers
57k views

Run stored procedures in parallel

I'm looking to try and run the same stored procedure multiple times with different parameters but at the same time. I'm using SQL 2014 The reason for this is that the procedure takes around 7 hours ...
user124569's user avatar
11 votes
1 answer
14k views

How to avoid using Merge query when upserting multiple data using xml parameter?

I am trying to update a table with an array of values. Each item in the array contains information that matches a row in a table in the SQL Server database. If the row already exists in the table, we ...
GMalla's user avatar
  • 189
11 votes
3 answers
3k views

What is the best method to add error handling in SQL 2005 stored procs?

What’s a good way to make stored procs robust enough that they can scale very well and also contain error handling? Additionally, whats the best way to handle multiple error scenarios in a stored ...
kacalapy's user avatar
  • 2,052
9 votes
2 answers
23k views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
Richard's user avatar
  • 216
7 votes
2 answers
4k views

Why does the order of parameters matter for sp_trace_create?

The first batch of the following script calls stored procedure sp_trace_create with parameters in documentation order; the second batch swaps the positions of parameters @tracefile and @options: ...
Iain Samuel McLean Elder's user avatar
6 votes
4 answers
18k views

how to dump a single stored procedure from a database

I need to create a dump file that contains a single stored procedure from a database, not all routines. I've tried something like this: mysqldump -u root -pssmart --skip-triggers --no-create-info --...
BALA's user avatar
  • 155
6 votes
2 answers
67k views

Data Truncated for Column

DECLARE float_one, float_two, my_result NUMERIC(7,2) my_result = CONVERT(float_one/float_two, DECIMAL(7,2)); In this mysql query, I do this type of operation in a stored procedure, but the Linux ...
dole doug's user avatar
  • 391
5 votes
2 answers
4k views

What's the best practice of inserting a collection of data into a SQL Server database

I am using C#, Entity Framework. SQL Server for the development. I have a method which has two parameters (int ID, List<String> _listobj). For each ID, there is a list of obj. All of these IDs ...
user3656901's user avatar
5 votes
2 answers
387 views

Transaction with lock on a specific table

NOTE : If there is similar question, please refer me to it. I have two tables Request & Receipt. I want table Receipt remain locked for CRUD operations from anywhere until transaction finished. ...
ABS's user avatar
  • 157
4 votes
3 answers
11k views

SQL Server: How to call stored procedure without schema name?

SQL Server 2005std/2008web edition db=user=schema=owner='John' I'm moving website from one server to another. This piece of code works well on old server with SQL Server 2005. Dim sqlCmdVehicle ...
Putnik's user avatar
  • 285
3 votes
2 answers
4k views

No rows in sys.dm_exec_procedure_stats for a particluar stored procedure

I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats. Please suggest what could be various reason for this ...
manjunathnarayani's user avatar
2 votes
1 answer
509 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 ...
PTL_SQL's user avatar
  • 329
2 votes
3 answers
37k views

How to Solve ERROR 1064 (42000)?

DROP PROCEDURE IF EXISTS proc_x; DELIMITER # CREATE DEFINER=`root`@`localhost` PROCEDURE proc_x(sequel CHAR) proc_main:BEGIN SET @SQL = CONCAT(@sequel); PREPARE stmt FROM @SQL; EXECUTE stmt; ...
Krunal Patel's user avatar
2 votes
1 answer
2k views

Drop Table and recreate Permissions

So we followed an best practice by Andreas de Ruiter (https://blogs.msdn.microsoft.com/andreasderuiter/2015/04/20/transforming-data-in-a-data-warehouse-through-sql-views) The Idea behind it is, that ...
Peter's user avatar
  • 121
2 votes
1 answer
4k views

Oracle: Not able to create tablespace from stored procedure

I am trying to create tablespace from stored procedure. create or replace procedure testprc is query_string VARCHAR2(4000); tbs_name VARCHAR2(20):='test77'; begin dbms_output.put_line('test..'); ...
Sathish Sharma's user avatar
2 votes
2 answers
8k views

`RETURN NEXT` vs `RETURN RECORD` in PostgreSQL

What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?
JRR's user avatar
  • 475
2 votes
1 answer
26k views

What is the best way to insert a XML input to an oracle table in stored procedure?

I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this: <rowset> <row> <name>tom&...
rahim asgari's user avatar