Stack Overflow
Stack Overflow

Questions tagged [stored-procedures]

A subroutine available to applications accessing a relational database system.

A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, StoredProc, or SP) are actually stored in the database data dictionary. Also, stored procedures are pre-compiled statements.

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Using stored procedure this centralized logic can be easily manage if it is changing frequently. Extensive or complex processing that requires the execution of several statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures, by executing one stored procedure from within another.

Using stored procedures is also a part of the security management of systems. Database administrators can restrict access to individual stored procedures, giving them ability to define very granular security if needed.

Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.

Useful Resources

36817 questions
1817
votes
34 answers

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but…
Ferdeen
  • 21,332
  • 6
  • 29
  • 31
1077
votes
24 answers

Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id =…
DharaPPatel
  • 12,035
  • 9
  • 31
  • 49
982
votes
19 answers

Function vs. Stored Procedure in SQL Server

When should I use a function rather than a stored procedure in SQL, and vice versa? What is the purpose of each?
Tarik
  • 79,711
  • 83
  • 236
  • 349
511
votes
18 answers

List of Stored Procedures/Functions Mysql Command Line

How can I see the list of the stored procedures or stored functions in mysql command line like show tables; or show databases; commands.
systemsfault
  • 15,207
  • 12
  • 59
  • 66
495
votes
19 answers

Select columns from result set of stored procedure

I have a stored procedure that returns 80 columns, and 300 rows. I want to write a select that gets 2 of those columns. Something like SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2' When I used the above syntax I get the error:…
Rossini
  • 5,960
  • 4
  • 29
  • 32
449
votes
7 answers

SQL Server SELECT into existing table

I am trying to select some fields from one table and insert them into an existing table from a stored procedure. Here is what I am trying: SELECT col1, col2 INTO dbo.TableTwo FROM dbo.TableOne WHERE col3 LIKE @search_key I think SELECT ... INTO…
Daniel
  • 4,687
  • 2
  • 19
  • 9
436
votes
16 answers

How to SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a stored procedure is necessary. Is it possible to select the output by…
jonathanpeppers
  • 26,115
  • 21
  • 99
  • 182
399
votes
13 answers

SQL Server Insert if not exists

I want to insert data into my table, but insert only data that doesn't already exist in my database. Here is my code: ALTER PROCEDURE [dbo].[EmailsRecebidosInsert] (@_DE nvarchar(50), @_ASSUNTO nvarchar(50), @_DATA nvarchar(30) ) AS BEGIN …
Francisco Carvalho
  • 4,123
  • 3
  • 16
  • 12
397
votes
21 answers

How do I find a stored procedure containing ?

I need to search a SQL server 2008 for stored procedures containing where maybe the name of a database field or variable name.
Gary Kindel
  • 17,071
  • 7
  • 49
  • 66
359
votes
13 answers

How to pass an array into a SQL Server stored procedure

How to pass an array into a SQL Server stored procedure? For example, I have a list of employees. I want to use this list as a table and join it with another table. But the list of employees should be passed as parameter from C#.
Sergey
  • 7,933
  • 16
  • 49
  • 77
341
votes
16 answers

How to check if a stored procedure exists before creating it

I have a SQL script that has to be run every time a client executes the "database management" functionality. The script includes creating stored procedures on the client database. Some of these clients might already have the stored procedure upon…
The Shaper
  • 3,495
  • 2
  • 16
  • 6
329
votes
18 answers

What is a stored procedure?

What is a "stored procedure" and how do they work? What is the make-up of a stored procedure (things each must have to be a stored procedure)?
George Stocker
  • 57,289
  • 29
  • 176
  • 237
319
votes
13 answers

SQL Server: Query fast, but slow from procedure

A query runs fast: DECLARE @SessionGUID uniqueidentifier SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908' SELECT * FROM Report_Opener WHERE SessionGUID = @SessionGUID ORDER BY CurrencyTypeOrder, Rank subtree cost: 0.502 But putting the…
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
303
votes
14 answers

How to execute a stored procedure within C# program

I want to execute this stored procedure from a C# program. I have written the following stored procedure in a SqlServer query window and saved it as stored1: use master go create procedure dbo.test as DECLARE @command as varchar(1000), @i int SET…
Cute
  • 13,643
  • 36
  • 96
  • 112
302
votes
22 answers

How to call Stored Procedure in Entity Framework 6 (Code-First)?

I am very new to Entity Framework 6 and I want to implement stored procedures in my project. I have a stored procedure as follows: ALTER PROCEDURE [dbo].[insert_department] @Name [varchar](100) AS BEGIN INSERT [dbo].[Departments]([Name]) …
Jaan
  • 3,323
  • 7
  • 20
  • 24
1
2 3
…
99 100