Questions tagged [stored-procedures]
A subroutine available to applications accessing a relational database system.
37,084
questions
1841
votes
35
answers
2.6m
views
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 ...
- 21.6k
1107
votes
24
answers
2.5m
views
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
...
- 12.3k
996
votes
19
answers
788k
views
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?
- 80.8k
514
votes
20
answers
615k
views
Show 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.
- 15.3k
499
votes
20
answers
770k
views
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'
...
- 6,009
458
votes
7
answers
628k
views
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
...
- 4,777
446
votes
16
answers
1.3m
views
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 ...
- 26.3k
413
votes
14
answers
1.1m
views
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 ...
- 4,261
409
votes
21
answers
893k
views
How do I find a stored procedure containing <text>?
I need to search a SQL server 2008 for stored procedures containing where maybe
the name of a database field or variable name.
- 17.4k
365
votes
13
answers
561k
views
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 ...
- 8,013
347
votes
16
answers
704k
views
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 ...
- 3,555
330
votes
18
answers
591k
views
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)?
322
votes
13
answers
256k
views
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 ...
- 252k
307
votes
14
answers
1.0m
views
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....
- 13.8k
307
votes
22
answers
593k
views
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)
...
- 3,373
283
votes
2
answers
733k
views
Check if a string contains a substring in SQL Server 2005, using a stored procedure
I've a string, @mainString = 'CATCH ME IF YOU CAN'. I want to check whether the word ME is inside @mainString.
How do I check if a string has a specific substring in SQL?
- 21.4k
274
votes
47
answers
79k
views
What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
What are the advantages/disadvantages of keeping SQL in your C# source code or in Stored Procs? I've been discussing this with a friend on an open source project that we're working on (C# ASP.NET ...
254
votes
14
answers
937k
views
Execute stored procedure with an Output parameter?
I have a stored procedure that I am trying to test. I am trying to test it through SQL Management Studio. In order to run this test I enter ...
exec my_stored_procedure 'param1Value', 'param2Value'
...
- 14k
247
votes
7
answers
230k
views
Creating stored procedure in SQLite
Is it somehow possible to create a stored procedure when using SQLite?
- 12.5k
245
votes
10
answers
332k
views
Show the code of a function, procedure and trigger in PostgreSQL
How can I show the code of a function, procedure and trigger in PostgreSQL? Please let me know if any one know the query to show the code of them.
- 2,499
241
votes
7
answers
190k
views
Is there a way to call a stored procedure with Dapper?
I am very impressed with the results of Dapper Micro ORM for stackoverflow.com. I am considering it for my new project and but I have one concern about that some times my project requires to have ...
- 14k
237
votes
7
answers
337k
views
How do I execute a stored procedure once for each row returned by query?
I have a stored procedure that alters user data in a certain way. I pass it user_id and it does it's thing. I want to run a query on a table and then for each user_id I find run the stored procedure ...
- 43.3k
223
votes
9
answers
640k
views
How to check date of last change in stored procedure or function in SQL server
I need to check when function was changed last time. I know how to check creation date (it is in function properties window in SQL Server Management Studio).
I found that in SQL Server 2000 it wasn't ...
- 9,664
214
votes
9
answers
368k
views
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
Error message on MySql:
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
I have gone through several other posts and was not able to solve this ...
- 4,155
213
votes
10
answers
296k
views
What is the difference between a stored procedure and a view?
I am confused about a few points:
What is the difference between a stored procedure and a view?
When should I use stored procedures, and when should I use views, in SQL Server?
Do views allow the ...
- 10.6k
206
votes
7
answers
133k
views
MySQL procedure vs function, which would I use when?
I'm looking at MySQL procedures and functions. What is the real difference?
They seem to be similar, but a function has more limitations.
I'm likely wrong, but it seems a procedure can do everything ...
- 7,505
197
votes
17
answers
279k
views
SQL Call Stored Procedure for each Row without using a cursor
How can one call a stored procedure for each row in a table, where the columns of a row are input parameters to the sp without using a Cursor?
- 35.6k
195
votes
6
answers
317k
views
How to pass table value parameters to stored procedure from .net code
I have a SQL Server 2005 database. In a few procedures I have table parameters that I pass to a stored proc as an nvarchar (separated by commas) and internally divide into single values. I add it to ...
- 9,664
192
votes
14
answers
396k
views
How to find a text inside SQL Server procedures / triggers?
I have a linkedserver that will change. Some procedures call the linked server like this: [10.10.100.50].dbo.SPROCEDURE_EXAMPLE. We have triggers also doing this kind of work. We need to find all ...
- 11.6k
190
votes
22
answers
114k
views
LINQ-to-SQL vs stored procedures? [closed]
I took a look at the "Beginner's Guide to LINQ" post here on StackOverflow (Beginners Guide to LINQ), but had a follow-up question:
We're about to ramp up a new project where nearly all of our ...
- 7,965
190
votes
4
answers
367k
views
Optional parameters in SQL Server stored procedure
I'm writing some stored procedures in SQL Server 2008. Is the concept of optional input parameters possible here?
I suppose I could always pass in NULL for parameters I don't want to use, check the ...
- 16.1k
183
votes
2
answers
286k
views
The maximum recursion 100 has been exhausted before statement completion
I keep getting a max recursion error with this query.
At first I thought it was because a null was being returned and then it would try and match the null values causing the error however, I rewrote ...
- 2,709
173
votes
8
answers
800k
views
Call a stored procedure with parameter in c#
I'm able to delete, insert and update in my program and I try to do an insert by calling a created stored procedure from my database.
This button insert I made works well.
private void btnAdd_Click(...
- 2,592
172
votes
12
answers
819k
views
How to Execute SQL Server Stored Procedure in SQL Developer?
I've been given a user account to a SQL Server database that only has privileges to execute a stored procedure. I added the JTDS SQL Server JDBC jar file to SQL Developer and added it as a Third Party ...
- 7,962
157
votes
8
answers
130k
views
Mysql - How to quit/exit from stored procedure
I have very simple question but i did't get any simple code to exit from SP using Mysql.
Can anyone share with me how to do that?
CREATE PROCEDURE SP_Reporting(IN tablename VARCHAR(20))
BEGIN
IF ...
- 2,149
155
votes
13
answers
199k
views
Show full stored function or procedure code in PostgreSQL?
How do you view a stored function or procedure?
Say I have an old function without the original definition - I want to see what it is doing in pg/psql but I can't seem to figure out a way to do that.
...
- 1,553
155
votes
10
answers
226k
views
How can I check if a View exists in a Database?
I have some SQL code that needs to be executed if a certain View exists in a database. How would I go about checking if the View exists?
EDIT: The DBMS being used is Microsoft SQL Server
- 16.3k
150
votes
16
answers
569k
views
How do I search an SQL Server database for a string?
I know it's possible, but I don't know how.
I need to search an SQL Server database for all mentions of a specific string.
For example: I would like to search all tables, views, functions, ...
- 5,128
150
votes
16
answers
230k
views
How do you debug MySQL stored procedures?
My current process for debugging stored procedures is very simple. I create a table called "debug" where I insert variable values from the stored procedure as it runs. This allows me to see the value ...
- 14.6k
148
votes
5
answers
152k
views
T-SQL stored procedure that accepts multiple Id values
Is there a graceful way to handle passing a list of ids as a parameter to a stored procedure?
For instance, I want departments 1, 2, 5, 7, 20 returned by my stored procedure. In the past, I have ...
- 23.7k
143
votes
11
answers
229k
views
How to detect if a stored procedure already exists
I have to write a deployment script which will work if a stored procedure exists or does not exist. i.e. if it exists, then I need to alter it, otherwise create it.
How can I do this in the sql.
I ...
- 7,157
139
votes
7
answers
412k
views
How to assign an exec result to a sql variable?
How do you assign the result of an exec call to a variable in SQL? I have a stored proc called up_GetBusinessDay, which returns a single date.
Can you do something like this:
exec @...
- 13.2k
137
votes
15
answers
81k
views
Dynamic Sorting within SQL Stored Procedures
This is an issue that I've spent hours researching in the past. It seems to me to be something that should have been addressed by modern RDBMS solutions but as yet I have not found anything that ...
- 5,707
135
votes
17
answers
92k
views
What is your naming convention for stored procedures? [closed]
I have seen various rules for naming stored procedures.
Some people prefix the sproc name with usp_, others with an abbreviation for the app name, and still others with an owner name. You shouldn't ...
- 32.6k
134
votes
14
answers
245k
views
Data is Null. This method or property cannot be called on Null values
I'm working on an application where one can get information on movies from a database as well as add, update and delete the movies. In the database I have three tables (Movie, Genre and MovieGenre <...
- 20.4k
130
votes
12
answers
250k
views
Procedure expects parameter which was not supplied
I'm getting the error when accessing a Stored Procedure in SQL Server
Server Error in '/' Application.
Procedure or function 'ColumnSeek' expects parameter '@template', which was not supplied.
This ...
- 20.8k
130
votes
8
answers
231k
views
C# SQL Server - Passing a list to a stored procedure
I am calling a SQL Server stored procedure from my C# code:
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("InsertQuerySPROC", ...
- 11.8k
129
votes
7
answers
178k
views
Paging with Oracle
I am not as familiar with Oracle as I would like to be. I have some 250k records, and I want to display them 100 per page. Currently I have one stored procedure which retrieves all quarter of a ...
- 12.4k
129
votes
16
answers
276k
views
How to convert int to char with leading zeros?
I need to convert int datafield to nvarchar with leading zeros
example:
1 convert to '001'
867 convert to '000867', etc.
thx.
This is my response 4 Hours later ...
I tested this T-SQL Script and ...
128
votes
19
answers
466k
views
SQL Server: Invalid Column Name
I am working on modifying the existing SQL Server stored procedure. I added two new columns to the table and modified the stored procedure as well to select these two columns as well. Although the ...
- 12.1k