71
votes
What are the differences between "Stored Procedures" and "Stored Functions"?
PostgreSQL 11 added stored procedures as a new schema object. You can create a new procedure by using the CREATE PROCEDURE statement.
Stored procedures differ from functions in the following ways:
...
- 1,243
44
votes
Accepted
How to create Unicode parameter and variable names
Well, identifiers are always Unicode / NVARCHAR, so technically you can't create anything that doesn't have a Unicode name 🙃.
The problem you are having here is due entirely to the ...
- 68.8k
31
votes
Accepted
MySQL -> Loop through a table, running a stored procedure on each entry
Create a second procedure that uses two nested cursors.
Cursors in stored procedures allow you to do a very non-SQL-like thing: iterate through a result set one row at a time, putting the selected ...
- 22.6k
29
votes
Accepted
Insert results from a stored procedure into a table variable
To insert the results into a table, you just want INSERT....EXEC... not the VALUES part of the query.
In your case, this would look like the following:
INSERT INTO @DomainHistory(DomainId,HasHistory)...
- 10.5k
26
votes
Accepted
Transaction in a stored procedure
You need to wrap that code in CREATE PROCEDURE ... syntax, and remove the GO statements after BEGIN TRANSACTION and before COMMIT TRANSACTION.
GO
CREATE PROCEDURE dbo.AssignUserToTicket
(
@...
- 70k
26
votes
Accepted
How to create a row for every day in a date range using a stored procedure?
One option is a recursive CTE:
DECLARE @StartDate datetime = '2017-03-05'
,@EndDate datetime = '2017-04-11'
;
WITH theDates AS
(SELECT @StartDate as theDate
UNION ALL
...
- 11.6k
22
votes
How to create Unicode parameter and variable names
I don't think it's Unicode that's causing the problem; in the case of local variable or parameter names, it's that the character isn't a valid ASCII/Unicode 3.2 character (and there isn't any escaping ...
- 180k
20
votes
Accepted
Get Stored Procedure Result Column Types
Specifically for objects, there is a DMV called sys.dm_exec_describe_first_result_set_for_object which will describe the first result set if SQL Server can figure out what it should be (dynamic SQL, ...
- 32.5k
20
votes
Accepted
Postgres get function owner
There is a simple and a complicated way.
The complicated way is to join with pg_roles and get the user name from there.
But since PostgreSQL hackers don't want to type more than necessary, they came ...
- 51.7k
19
votes
Accepted
Execute stored procedure remotely using linked server
Assuming the procedure lives on the remote server: have you tried using the EXECUTE AT command?
DECLARE @RunStoredProcSQL VARCHAR(1000);
SET @RunStoredProcSQL = 'EXEC [Database].[dbo].[StoredProcName]...
- 2,254
18
votes
Get Stored Procedure Result Column Types
You should be able to use the new system stored procedure sp_describe_first_result_set for that - see the MSDN docs for details:
EXEC sp_describe_first_result_set N'YourStoredProcedureNameHere'
- 8,932
18
votes
Business logic in stored proc vs middle layer (c#)
A) Scale The middle tier can be scaled easily - hence the web farm concept. Scaling out the DB tier is much more difficult. While some products can do this it is not yet trivial and mainstream.
B) ...
- 24.8k
17
votes
Accepted
How exactly does sp_msforeachdb work behind the scenes?
First of all don't use sp_msforeachdb it has several known issues. You are better off using Aaron Bertrand's version here and here.
However it uses a cursor, dynamic SQL and a replace. You can ...
- 24.1k
17
votes
Accepted
Find the value OPTIMIZE FOR UNKNOWN is using
OPTIMIZE FOR UNKNOWN doesn't use a value - instead, it uses the density vector.
If you run DBCC SHOWSTATISTICS, it's the value listed in the "All density" column of the second result set:
In this ...
- 43k
17
votes
SQL PRINT statement prints nothing
The PRINT statement will print nothing if the expression evaluates to a null. Apparently @team ends up being null as the result of the preceding statement, causing the PRINT to output nothing. If you ...
- 23k
17
votes
How do I limit a SQL stored procedure to be run by one person at a time?
To add to @Tibor-Karaszi's answer, setting a lock timeout doesn't actually produce an error (I've submitted a PR against the docs). sp_getapplock just returns -1, so you have to check the return ...
- 46.5k
17
votes
Accepted
Why does a simple natively compiled stored procedure run out of memory when table variables are used?
When a table variable is used and accessed via a Bw-tree (range) index, memory is allocated at the start of every scan when the engine finds the starting entry (hkengine!HkCursorRangeGetFirst and ...
- 84.1k
16
votes
Accepted
Better execution plan if parameter is stored in local variable
Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem ...
- 84.1k
16
votes
In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure
This information -- run-time parameter values passed into a Stored Procedure (i.e. RPC call) or parameterized query -- is only available via a SQL Trace (and I assume the equivalent Extended Event in ...
- 68.8k
16
votes
Accepted
In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?
Since Postgres functions (CREATE FUNCTION) only run in a (single) transaction context, several important commands cannot be executed inside a function body. Like CREATE DATABASE or CREATE INDEX ...
- 176k
16
votes
Accepted
Why is part of transaction committed when stored procedure faults?
Transactions don't automatically roll back on error--that's not what they are designed to do. They are designed to give you the ability to rollback. However, you still need to do something to make ...
- 16.1k
15
votes
Accepted
What is the SSMS option "Execute stored procedure shortcuts without additional execution options"?
This is certainly a wordy option and it seems a bit ambiguous to me. You could take "stored procedure shortcuts" a number of ways; initially, I did not reach the conclusion that these were ...
- 180k
15
votes
Accepted
How to get the parameter values of a stored procedure that's in the middle of execution?
For SQL Server 2016 you need to have the query profiling infrastructure enabled in advance with trace flag 7412 or an extended events session capturing query_thread_profile (and be on at least SQL ...
- 84.7k
14
votes
Run stored procedures in parallel
At one point I answered this question over at StackOverflow, but it seems like it would be useful to have that information at DBA.SE as well, revised and updated.
Just to be totally explicit: TSQL ...
- 9,964
13
votes
In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure
You can turn on the actual execution plan and then look at the execution plan XML.
Or you can use sql sentry's plan explorer tool and see the parameters tab that will list the compiled value and run ...
- 62.2k
13
votes
Accepted
sp_msforeach db - Why do we need to use the USE keyword
The procedure does not perform a USE command for you. The way the procedure works is that it replaces every ? in your command with the database prefix.
If you run this:
USE foodb;
GO
EXEC sys....
- 180k
13
votes
Accepted
CAST throwing error when run in stored procedure but not when run as raw query
You're better off not depending on the indexing to avoid these errors, and instead writing the query to guard against this situation. Since you're on SQL Server 2012, one option would be to use ...
- 29.2k
13
votes
Accepted
Can a stored procedure reference the database in which it is stored?
I want to reference the database in which the stored procedure is stored, even if it is executed [from] another database.
Just use one-part or two-part names in the stored procedure, and it will ...
- 46.5k
13
votes
Accepted
Returning values for Stored Procedures in PostgreSQL
Both your sources are plain wrong.
A FUNCTION basically always returns something. void at the minimum, a single value, a row, or a set of rows ("set-returning function", a.k.a. "table-...
- 176k
13
votes
Accepted
Compilations/sec high when load testing a simple procedure
A compilation is not the same as an optimization.
Compilation itself is cheap. It happens any time a match is not found in the plan cache. Optimization is much more expensive.
A compilation may or may ...
- 84.1k
Only top scored, non community-wiki answers of a minimum length are eligible