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.
18 questions from the last 365 days
0
votes
1
answer
29
views
Oracle: Insufficent privileges when compiling a stored procedure as DBA role
I'm complining a stored procedure as user with DBA role. The stored procedure contains several "DELETE" statements. For each of them I'm getting:
PL/SQL: SQL Statement ignored
PL/SQL: ORA-...
- 221
1
vote
2
answers
233
views
Use column default value for optional parameter in stored procedure
What I'm trying to do is to have an optional parameter in my stored procedure that gets the default value defined in the table if not provided.
create table primates (
id int identity(1,1) ...
- 121
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 ...
- 821
0
votes
0
answers
127
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
93
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
514
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
113
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
157
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
186
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
308
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,001
3
votes
1
answer
83
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
599
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
44
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
245
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
966
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,001