Questions tagged [stored-procedures]

A subroutine available to applications accessing a relational database system.

Filter by
Sorted by
Tagged with
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 ...
Ferdeen's user avatar
  • 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 ...
DharaPPatel's user avatar
  • 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?
Tarik's user avatar
  • 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.
systemsfault's user avatar
  • 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' ...
Rossini's user avatar
  • 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 ...
Daniel's user avatar
  • 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 ...
jonathanpeppers's user avatar
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 ...
Francisco Carvalho's user avatar
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.
Gary Kindel's user avatar
  • 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 ...
Sergey's user avatar
  • 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 ...
The Shaper's user avatar
  • 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 ...
Ian Boyd's user avatar
  • 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....
Cute's user avatar
  • 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) ...
Jaan's user avatar
  • 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?
NLV's user avatar
  • 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' ...
user70192's user avatar
  • 14k
247 votes
7 answers
230k views

Creating stored procedure in SQLite

Is it somehow possible to create a stored procedure when using SQLite?
grady's user avatar
  • 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.
ganesh's user avatar
  • 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 ...
Jalpesh Vadgama's user avatar
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 ...
MetaGuru's user avatar
  • 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 ...
Marek Kwiendacz's user avatar
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 ...
Manatax's user avatar
  • 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 ...
NoviceToDotNet's user avatar
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 ...
Anonym's user avatar
  • 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?
Johannes Rudolph's user avatar
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 ...
Marek Kwiendacz's user avatar
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 ...
Victor Rodrigues's user avatar
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 ...
Scott Marlowe's user avatar
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 ...
larryq's user avatar
  • 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 ...
HELP_ME's user avatar
  • 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(...
FrankSharp's user avatar
  • 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 ...
sdoca's user avatar
  • 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 ...
Joe Ijam's user avatar
  • 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. ...
darren's user avatar
  • 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
Draco's user avatar
  • 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, ...
bobetko's user avatar
  • 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 ...
Cory House's user avatar
  • 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 ...
JasonS's user avatar
  • 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 ...
GordyII's user avatar
  • 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 @...
Prabhu's user avatar
  • 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 ...
Sean Hanley's user avatar
  • 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 ...
DOK's user avatar
  • 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 <...
holyredbeard's user avatar
  • 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 ...
Tony Peterson's user avatar
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", ...
Brett's user avatar
  • 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 ...
stephenbayer's user avatar
  • 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 ...
Kamran Ahmed's user avatar
  • 12.1k

1
2 3 4 5
742