Questions tagged [stored-procedures]
Callable code installed on a database manager, exposing an API through which it can be invoked. Normally written in the native query language, some DBMS platforms support other languages as well.
1,695 questions
4
votes
3
answers
1k
views
Is it better to add conditional logic to a heavily used stored procedure or create a separate one for a new use case?
We have a core stored procedure that's a critical part of our system and gets called thousands of times every hour to handle important operations. Now, there's a new application being introduced that ...
- 801
0
votes
0
answers
111
views
On Sybase ASE 16, why is a simple query using a prepared statement 37 % slower than an equivalent stored procedure?
I am using Sybase Adaptive Server Enterprise (ASE) 16. I am using jConnect 4 in version 7.07.
I have a stored procedure that contains a rather simple query, equivalent to this one (I have changed the ...
- 101
0
votes
2
answers
80
views
Can I get data from an estimated Execution Plan in my C# code?
After years of growth, some reports now take minutes to extract from our database, and in some cases fail due to excess resource consumption. This depends on the parameters that our customers choose ...
3
votes
2
answers
483
views
Tracking stored procedure performance metrics
I’m trying to track impacts of stored procedure executions - CPU time, execution duration, logical/physical reads, memory grants, etc. - in order to visualize them. In particular, I'm looking for ...
0
votes
1
answer
99
views
Could not find stored procedure 'sp_BlitzLock' [closed]
I'm running into an issue while trying to execute the stored procedure sp_BlitzLock in SQL Server 2022 Developer Edition. Here's the exact error message I'm getting:
Msg 2812, Level 16, State 62, Line ...
- 111
2
votes
2
answers
145
views
sp_describe_first_result_set with json columns
I'm working on an analyzer that accepts a SQL Server query and returns information about all columns in the first result set - until for clause is used, this is pretty straightforward with ...
- 205
2
votes
1
answer
159
views
On a linked server I can execute a stored procedure A but Can Not execute stored procedure B
On a linked server I can execute a stored procedure A but Can Not execute stored procedure B
I have a db server DBServer and a linked server LinkedServer. From the DBServer I can execute successfully ...
- 23
5
votes
1
answer
281
views
OUTPUT as default parameter value for stored procedure?
I've encountered peculiar behaviour by SQL Server, and I wonder why does it behave like this.
Consider following CREATE PROCEDURE script:
CREATE PROCEDURE dbo.myProc
@parameter INT = OUTPUT
AS
...
- 2,000
3
votes
1
answer
80
views
MYSQL and random performance of store procedure
I run this SP at night, when there’s almost no activity. The SP performs an accumulative calculation of machine usage from a laundromat—an artisanal BI query. It writes the result to a table.
On most ...
3
votes
2
answers
578
views
Deploy encrypted stored procedures that cannot be decrypted
There are many ways you can see an encrypted procedure.
Example of an encrypted procedure in SSMS:
There is this very good question: How to view an encrypted view or stored procedure where you can ...
- 17.3k
0
votes
0
answers
43
views
SQL Server Agent job to verify logshipping on multiple databases
I'm trying to come up with a SQL script that will verify log shipping on about 100 databases in an instance.
I need to be alerted if log shipping is failing, but I do not want 100 plus emails coming ...
3
votes
3
answers
242
views
Stored procedure to get max startDate with optional AgentId
I have this stored procedure that I want the max startDate base on AgentId or without the AgentId. The way I am doing this is using a if else and wanted to see if there was a better way to do this?
...
- 111
6
votes
1
answer
918
views
When creating stored procedure, are the comments before the definition always preserved?
When I wrote some comments before the CREATE PROCEDURE command, those comments seem to be preserved as part of procedure definition.
For example, when I create procedure like this
/*Comment header*/
...
- 2,000
0
votes
1
answer
109
views
Why can't I create a new stored procedure?
I need to copy existing stored procedures from one SQL-Server database to another one.
In the first SQL-Server database (the source), when clicking on "Stored Procedures", this is what I see:...
- 609
0
votes
1
answer
85
views
How to tune the complex Oracle stored procedure?
I have an Oracle stored procedure that takes a bunch of parameters and heavily does the left outer join like this:
create or replace PROCEDURE "SOME_PROCEDURE" (
cRECORDSET OUT ...
- 103