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
63 votes
7 answers
77k 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,117
53 votes
2 answers
6k views

How to create Unicode parameter and variable names

All of this works: CREATE DATABASE [¯\_(ツ)_/¯]; GO USE [¯\_(ツ)_/¯]; GO CREATE SCHEMA [¯\_(ツ)_/¯]; GO CREATE TABLE [¯\_(ツ)_/¯].[¯\_(ツ)_/¯]([¯\_(ツ)_/¯] NVARCHAR(20)); GO CREATE UNIQUE CLUSTERED INDEX [¯...
Brent Ozar's user avatar
  • 42.9k
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
49 votes
1 answer
77k 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
48 votes
5 answers
76k views

Can I launch a stored procedure and immediately return without waiting for it to finish?

We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day. I have a second stored procedure that should be run after ...
Rachel's user avatar
  • 8,457
43 votes
4 answers
86k 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,413
36 votes
4 answers
6k views

Still wrong to start the name of a user stored procedure with sp_?

One of my co-workers named a stored procedure in our SQL Server 2008 R2 database sp_something. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
user avatar
35 votes
2 answers
5k views

In SQL Server, what is the purpose of grouping stored procedures?

One of the most perplexing issues with which I've had to deal has to do with Stored Procedure groups. Given a stored procedure, usp_DoSomethingAwesome, I can create that proc in another group by ...
swasheck's user avatar
  • 10.7k
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
30 votes
3 answers
35k 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
29 votes
1 answer
50k views

Is there an alternative to "Create or replace procedure" in MySQL?

Is there an version of "create or replace procedure" for MySQL? I can't seem to do this or script the dropping of the procedure if exists before recompiling without getting an error message that the ...
amatusko's user avatar
  • 477
27 votes
6 answers
8k views

Is table aliasing a bad practice?

I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type: SELECT t1.id, t2.stuff FROM someTable t1 ...
Ben Brocka's user avatar
  • 2,063
26 votes
10 answers
231k views

"Could not find stored procedure' even though the stored procedure have been created in MS SQL Server Management Studio

I have created a table testtable inside the database testbase that have the following structure: product_no (int, not null) product_name (varchar(30), not null) price (money, null) expire_date (date, ...
Jack's user avatar
  • 2,509
26 votes
1 answer
80k views

Insert results from a stored procedure into a table variable

I have a stored procedure that stores values in a table variable. I select these values and return them when the procedure is called. I am trying to set these return values in another table variable ...
ThunD3eR's user avatar
  • 397
24 votes
2 answers
9k views

PostgreSQL procedural languages - differences between PL/pgSQL and SQL

Can anybody please summarize the differences between: http://www.postgresql.org/docs/9.1/static/xfunc-sql.html and http://www.postgresql.org/docs/9.1/static/plpgsql.html ? Main points: ...
Gismo Ranas's user avatar
23 votes
2 answers
37k views

How to keep history of SQL Server stored procedure revisions

Note: I am not asking about full version control. Is there any way automatically to keep a history of stored procedures on SQL Server. Similar to how Google Docs automatically keeps a history of ...
cja's user avatar
  • 355
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
22 votes
6 answers
102k views

Don't use a transaction for Stored Procedure

I have a stored procedure that runs a few commands. I don't want these commands to be wrapped in the transaction of the stored procedure. If the 4th command fails, I want the 1st, 2nd and 3rd ones to ...
Matthew Steeples's user avatar
21 votes
6 answers
122k views

How to create a row for every day in a date range using a stored procedure?

I would like to create a stored procedure that will create a row in a table for every day in a given date range. The Stored Procedure accepts two inputs - A start date and end date of the date range ...
Rob V's user avatar
  • 375
21 votes
2 answers
35k views

Behavior of Varchar with spaces at the end

When I use a Varchar with spaces it ignores the spaces at the end. ex: declare @X varchar(50) This... set @X= 'John' ...is the same as... set @X= 'John ' It considers these to be ...
AMH's user avatar
  • 353
20 votes
6 answers
48k views

How do I find out if a procedure or function exists in a mysql database?

How do I find out if a procedure or function exists in a mysql database? and is there any discovery option? like a show procedures; (e.g. like show tables;)
xenoterracide's user avatar
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,100
20 votes
2 answers
48k views

Is commit necessary after DML operation in Function/Procedure?

I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure? Example: create or replace function test_fun return number is begin delete from a; return ...
kupa's user avatar
  • 1,707
19 votes
3 answers
32k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
Elias's user avatar
  • 301
19 votes
7 answers
32k views

Stored Procedures under Source Control, best practice

I am currently using Tortoise SVN to source control a .NET Web Application. What would be the best way to bring our SQL Server stored procedures into Source Control? I am currently using VS 2010 as my ...
QFDev's user avatar
  • 789
19 votes
2 answers
39k views

Disabling Checking of Schema On Function/Stored Procedure Creation

I'm trying to automate the process that executes changes to the SQL Server 2008 R2 database. The process I put in place drops and recreates my stored procedures and functions, as well as run scripts ...
Brian Mains's user avatar
19 votes
3 answers
51k views

How to use prepared statements within MySQL stored procedures?

I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable ...
donk's user avatar
  • 525
18 votes
2 answers
11k views

Central stored procedure to execute in calling database context

I am working on a customized maintenance solution using the sys.dm_db_index_physical_stats view. I currently have it being referenced from a stored procedure. Now when that stored procedure runs on ...
Josh Waclawski's user avatar
18 votes
1 answer
119k views

How does MySQL return a result set from a stored procedure?

I'm trying to get my head around stored procedures for use in a web application. It seems to me that whatever the last statement is in a MySQL stored procedure is seems to be what gets treated as that ...
Billy ONeal's user avatar
17 votes
1 answer
76k views

MySQL -> Loop through a table, running a stored procedure on each entry

I have a database with 'books' (short stories for children) and it would be extremely informative to have word counts of each word in the books. I figured out how to get the word count for each word ...
Michael MacDonald's user avatar
17 votes
2 answers
95k views

sp_send_dbmail stored procedure send with attachment

I've been tasked with sending a small monthly report to for one of my customers. The report has previously been run manually on the instance, the output copied to a spreadsheet and send to the ...
Gareth's user avatar
  • 313
17 votes
1 answer
31k views

How do you ORDER BY a parameter?

Just wondering if I could solicit some feedback on a stored procedure I'm running and whether there's a more efficient way of handling the scenario (I'm pretty sure there will be!). Basically I have ...
VaticNZ's user avatar
  • 385
17 votes
3 answers
18k views

Why would you want to avoid dynamic SQL in stored procedures?

I have heard it said you do not want to use Dynamic SQL. Can you give me some concrete or real-life examples? Personally, I code using it a few times in my database. I think it is OK because of its ...
Richard Sayakanit's user avatar
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
16 votes
1 answer
23k views

Is ASYNC_NETWORK_IO wait type anything to worry about?

In looking at the list of stored procedures that take long time to execute, one stands out as causing the most wait. However, most of that wait (81%) is ASYNC_NETWORK_IO and I know why: the stored ...
AngryHacker's user avatar
  • 1,921
16 votes
1 answer
582 views

What allows SQL Server to trade an object name for a string passed to a system procedure

What causes it to be legal to pass an object name to the system stored procedure sp_helptext? What mechanism converts the object name to a string? e.g. -- works sp_helptext myproc sp_helptext [...
JJS's user avatar
  • 721
15 votes
2 answers
125k views

Transaction in a stored procedure

I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest ...
Charlie K's user avatar
  • 161
15 votes
4 answers
27k views

In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure

One way to determine the executing stored procedure is to use "dynamic management" methods, like so: SELECT sqlText.Text, req.* FROM sys.dm_exec_requests req OUTER APPLY sys....
user420667's user avatar
15 votes
2 answers
69k views

Returning values for Stored Procedures in PostgreSQL

I was reading this on PostgreSQL Tutorials: In case you want to return a value from a stored procedure, you can use output parameters. The final values of the output parameters will be returned to ...
Purav Uday Desai's user avatar
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
15 votes
2 answers
2k views

Optimization: Moving variable declarations to the top of your procedure

While working on optimizing some stored procedures, I sat down with the DBA and went through some stored procedures with high blocking and/or high read/write activity. One thing the DBA mentioned was ...
brad.v's user avatar
  • 263
15 votes
1 answer
55k 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
  • 465
14 votes
5 answers
40k views

Get Stored Procedure Result Column Types

I'm trying to get whatever a stored procedure returns, with the column names and their types. I can do this with tables but I couldn't figure it out for stored procedures. I tried the sp_columns but ...
Mein Hat's user avatar
  • 165
14 votes
5 answers
9k views

How do I limit a SQL stored procedure to be run by one person at a time?

I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time. While this ...
twoheadedmona's user avatar
14 votes
1 answer
2k views

Why does a simple natively compiled stored procedure run out of memory when table variables are used?

My version of SQL Server is SQL Server 2019 (RTM-CU18). The following repro code requires that an in memory filegroup is created. For anyone following along, please remember that an in-memory ...
Joe Obbish's user avatar
  • 32.1k
14 votes
2 answers
18k views

How to get the current line number from an executing stored procedure

Is there a function or keyword that will allow me to get the current line number in a stored procedure? I know there's an undocumented LineNo function that allows you to set the line number, and ...
JJS's user avatar
  • 721
14 votes
2 answers
53k 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
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
14 votes
1 answer
107k views

PLS-00306 Error: How to find the wrong argument?

PLS-00306: wrong number or types of arguments in call to 'string' Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram ...
Stephan Schielke'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,457

1
2 3 4 5
34