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.

A stored procedure is a callable code module that can be installed on a database manager. Normally they are written in the native query language or a specialist language such as PL/SQL. Some database management systems allow other languages to be used.

Stored procedures export an API that can be used from other SQL code. They can be used to encapsulate complex operations, provide a secure API around sensitive data or as a platform to develop a process that needs to execute on the server for performance reasons.

1632 questions
54
votes
7 answers

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

So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL. The comment links to a wikipedia article but some of this don't seem to apply (e.g. that they can be used in a…
DrColossos
  • 6,697
  • 2
  • 31
  • 30
53
votes
2 answers

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 [¯\_(ツ)_/¯] ON…
Brent Ozar
  • 42,296
  • 45
  • 201
  • 356
53
votes
4 answers

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 some_stored_procedure; But create a table which…
Just a learner
  • 2,522
  • 6
  • 32
  • 49
48
votes
1 answer

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
  • 723
  • 2
  • 7
  • 10
48
votes
5 answers

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 the first stored procedure runs since it is based on…
Rachel
  • 8,367
  • 20
  • 48
  • 74
42
votes
4 answers

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 not null ,name varchar(50) not null ,value…
adopilot
  • 2,393
  • 6
  • 30
  • 44
35
votes
4 answers

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 online article that explains why it is wrong, so I…
user5147
35
votes
2 answers

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 calling it usp_DoSomethingAwesome;2. I discovered this…
swasheck
  • 10,555
  • 4
  • 45
  • 88
30
votes
2 answers

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 master stored procedure, but the 2nd stored procedure…
user2483342
  • 441
  • 1
  • 5
  • 4
30
votes
3 answers

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 technical reasoning for this (in a way that I can explain…
webdad3
  • 572
  • 1
  • 7
  • 14
28
votes
1 answer

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 stored procedure exists. DELIMITER $$ -- would…
amatusko
  • 457
  • 1
  • 5
  • 11
26
votes
6 answers

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 INNER JOIN otherTable t2 ON…
Ben Brocka
  • 2,053
  • 6
  • 28
  • 38
25
votes
10 answers

"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, null) expire_time (time(7), null) which I used…
Jack
  • 2,429
  • 14
  • 35
  • 42
25
votes
1 answer

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 but I can't figure it out. Stored procedure ALTER…
ThunD3eR
  • 387
  • 1
  • 3
  • 8
23
votes
2 answers

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 versions of documents and Wikipedia automatically…
cja
  • 355
  • 2
  • 4
  • 10
1
2 3
99 100