All Questions
Tagged with sql stored-procedures
12,372
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
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
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)?
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 ...
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
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
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
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
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
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
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
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
127
votes
3
answers
158k
views
MySQL: Selecting multiple fields into multiple variables in a stored procedure
Can I SELECT multiple columns into multiple variables within the same select query in MySQL?
For example:
DECLARE iId INT(20);
DECLARE dCreate DATETIME;
SELECT Id INTO iId, dateCreated INTO ...
- 3,510
124
votes
20
answers
83k
views
Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS's? [duplicate]
Conventional wisdom states that stored procedures are always faster. So, since they're always faster, use them ALL THE TIME.
I am pretty sure this is grounded in some historical context where this ...
- 68.8k
120
votes
8
answers
179k
views
In SQL Server, what does "SET ANSI_NULLS ON" mean?
The definition says:
When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses ...
- 5,026
117
votes
6
answers
102k
views
SQL function as default parameter value?
I tried changing a default parameter value with this:
ALTER PROCEDURE [dbo].[my_sp]
@currentDate datetime = GETDATE()
and all the SQL pre-compiler gave me was this error:
Msg 102, Level 15, State ...
- 1,218
112
votes
9
answers
56k
views
Insert Update stored proc on SQL Server
I've written a stored proc that will do an update if a record exists, otherwise it will do an insert. It looks something like this:
update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@...
- 66.2k
110
votes
5
answers
156k
views
How to check if an array is empty in Postgres
I have a Postgres function:
CREATE OR REPLACE FUNCTION get_stats(
_start_date timestamp with time zone,
_stop_date timestamp with time zone,
id_clients integer[],
OUT date timestamp ...
- 2,925
105
votes
19
answers
325k
views
How to call a stored procedure from Java and JPA
I am writing a simple web application to call a stored procedure and retrieve some data.
Its a very simple application, which interacts with client's database. We pass employee id and company id and ...
- 1,191
104
votes
7
answers
238k
views
Return multiple fields as a record in PostgreSQL with PL/pgSQL
I am writing a SP, using PL/pgSQL.
I want to return a record, comprised of fields from several different tables. Could look something like this:
CREATE OR REPLACE FUNCTION get_object_fields(name text)...
- 3,231
104
votes
12
answers
880k
views
Must declare the scalar variable
@RowFrom int
@RowTo int
are both Global Input Params for the Stored Procedure, and since I am compiling the SQL query inside the Stored Procedure with T-SQL then using Exec(@sqlstatement) at the end ...
- 1,131
100
votes
10
answers
221k
views
Show procedure definition in MySQL
What is the MySQL command to show the definition of a procedure, similar to sp_helptext in Microsoft SQL Server?
I know that SHOW PROCEDURE STATUS will display the list of the procedures available. I ...
- 6,558
96
votes
5
answers
108k
views
How to generate a new Guid in stored procedure?
I currently have a stored procedure in which I want to insert new rows into a table.
insert into cars
(id, Make, Model)
values('A new Guid', "Ford", "Mustang")
So the primary key 'id' is a Guid. I ...
- 5,813
95
votes
12
answers
44k
views
Functions vs Stored Procedures
Let's say I have to implement a piece of T-SQL code that must return a table as result. I can implement a table-valued function or else a stored procedure that returns a set of rows. What should I use?...
- 13.9k
93
votes
15
answers
130k
views
How to compare values which may both be null in T-SQL
I want to make sure I'm not inserting a duplicate row into my table (e.g. only primary key different). All my fields allow NULLS as I've decided null to mean "all values". Because of nulls, the ...
- 8,082
90
votes
6
answers
603k
views
SQL Server IF EXISTS THEN 1 ELSE 2
Using Sql Server 2012. I have a stored procedure and part of it checks if a username is in a table. If it is, return a 1, if not, return a 2. This is my code:
IF EXISTS (SELECT * FROM ...
- 2,567
90
votes
9
answers
105k
views
Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'
I am using WinHost.com to host my site. The SQL Database/membership system works perfectly on my local computer, but when uploaded to the server it doesn't work. I've followed all steps correctly. And ...
- 11.5k
90
votes
4
answers
329k
views
How to return the output of stored procedure into a variable in sql server
I want to execute a stored procedure in SQL Server and assign the output to a variable (it returns a single value) ?
- 64.3k
88
votes
5
answers
141k
views
MySQL : transaction within a procedure
The basic structure of my procedure is:
BEGIN
START TRANSACTION;
.. Query 1 ..
.. Query 2 ..
.. Query 3 ..
COMMIT;
END;
MySQL version: 5.1.61-0ubuntu0.11.10.1-log
Currently, if '...
- 1,783
86
votes
3
answers
70k
views
Inserting rows into a table with one IDENTITY column only [duplicate]
I have a table Administrator with only one column, adminId which is the primary-key. Because of business rules it has to be this way.
I'd like to understand once and for all how I can write stored ...
- 3,984
85
votes
6
answers
141k
views
Is it possible to have a default parameter for a mysql stored procedure?
I have googled this and keep coming up with "No it is not possible" but these posts were dated 2005-2007 so I'm wondering if this has been changed. A code example:
CREATE PROCEDURE `blah`
(
...
- 36.7k
82
votes
10
answers
258k
views
How do I pass a list as a parameter in a stored procedure?
Looking to pass a list of User IDs to return a list names. I have a plan to handle the outputed names (with a COALESCE something or other) but trying to find the best way to pass in the list of user ...
user4840156
78
votes
5
answers
107k
views
Pass In "WHERE" parameters to PostgreSQL View?
I have a rather complicated query on my PostgreSQL database spanning 4 tables via a series of nested subqueries. However, despite the slightly tricky looking appearance and setup, ultimately it will ...
- 1,056
78
votes
3
answers
100k
views
Passing Output parameters to stored procedure using dapper in c# code
I have a stored procedure in this format
CREATE PROCEDURE SP_MYTESTpROC
@VAR1 VARCHAR(10),
@VAR2 VARCHAR(20),
@BASEID INT ,
@NEWID INT OUTPUT
As Begin
INSERT INTO TABLE_NAME(...
- 1,293
77
votes
7
answers
49k
views
SQL Server silently truncates varchar's in stored procedures
According to this forum discussion, SQL Server (I'm using 2005 but I gather this also applies to 2000 and 2008) silently truncates any varchars you specify as stored procedure parameters to the length ...
- 28.7k
75
votes
8
answers
170k
views
How to pass a null variable to a SQL Stored Procedure from C#.net code
Im calling a SQL stored procedure from a piece of C#.net code:
SqlHelper.ExecuteDataset(sqlConnection, CommandType.StoredProcedure, STORED_PROC_NAME, sqlParameters);
where the sqlParameters variable ...
- 2,489
75
votes
7
answers
241k
views
What is the difference between function and procedure in PL/SQL?
What is the difference between function and procedure in PL/SQL ?
- 8,601
74
votes
11
answers
136k
views
Passing an array of parameters to a stored procedure
I need to pass an array of "id's" to a stored procedure, to delete all rows from the table EXCEPT the rows that match id's in the array.
How can I do it in a most simple way?
- 2,164