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.

learn more… | top users | synonyms

2
votes
1answer
12 views

Howto implement correctly Businness Logic in database

I'm searching about how to disable triggers, and seems like it's not possible under MariaDB. My problem is, how to manage then, when you import data. I have a database where almost all business logic ...
0
votes
0answers
6 views

postgres create a view with data transformation rules

I was hoping someone could point me to a resource that can help me solve my problem. I have a largish dataset (25 Million rows), and I need to export the table based on a set of rules. 6 rules to ...
1
vote
2answers
32 views

Execute stored procedure remotely using linked server

We have 3 SQL Server 2012 instances, 2 of them we're not db_owner. Some of our stored procedures use DMV and sys views and stored procedures that only work when executed locally. I'd like to ...
2
votes
1answer
42 views

Creating a helper UDF or stored procedure and limiting external access

I'm looking for a recommended best practice for dividing functions and stored procedures into a 'public API' and 'private implementation details'. I'm working on a security system and I want client ...
0
votes
1answer
26 views

Random performance of stored procedure across databases

I have a stored procedure that is installed on 30 databases across 4 servers. We encountered an issue last night where 3 of these stored procs are now taking over 8 minutes (one 18 minutes!) to ...
0
votes
1answer
21 views

How to connect to an Oracle db using a stored procedures within a Microsoft SQL server? [on hold]

I need to verify data between and Oracle db and a Microsoft SQL server db to verify that if there differences between the two and then update the Microsoft SQL server with any changes there may be
1
vote
2answers
48 views

Сopy rows from one table to another and replace duplicates in the process

I have an SSIS job that loads daily data from CSV files into a raw staging table in a SQL Server database (technically is an Azure SQL database). Then after that, a store procedure is executed to copy ...
0
votes
0answers
20 views

How to return columns when using Dynamic Sql

In my project, i want to search input values and i think i should create a SP using dynamic Sql. When i call this SP in MVC, it return "int".I want it return columns as ProductName,ProductCat ... . ...
0
votes
0answers
10 views

Security of SQL Trigger Using dynamically stored table name

I'm working with PostgreSQL 9.0 and am learning how to use Triggers to enable some useful functionality. I have an insert trigger on a table that looks like this: feature_id | layer_name| latitude | ...
1
vote
1answer
34 views

Use stored procedure to get minimum value from column and decrement it and other values

I'm trying to understand how the following algorithm could be completed in SQL 2008 R2. I have a table that contains production data. The table uses a "rolling" scheduling method, meaning that orders ...
0
votes
2answers
34 views

Can I tell if a stored procedure has executed against a particular database?

As a result of a release a db is behaving "unusually". I suspect a stored procedure was missed during release, but my dba is telling me 100% it was executed. Is there any way to definitively prove ...
1
vote
3answers
28 views

Why doesn't the following stored procedure compile with MySQL Workbench version 6.3.?

I would like to know how to make the following stored procedure compile with MySQL Workbench version 6.3. use puppy; DELIMITER $$ CREATE PROCEDURE spGetSystemLicensing ( IN _licenseName varchar(...
3
votes
2answers
33 views

How do I get postgres to log the stored procedures it is running?

I'm not asking how to log what the stored procedure does, I simply want a list of all the stored procedures that are being triggered and run dumped into the statement log. I've got it logging all the ...
0
votes
0answers
19 views

How to delete/disable Extended SQL functions or turn off extended stored procedures in postgres

I have set up a new postgres installation and have to meet the security requirements from my company. There is one requirement were i'm not quite sure how to implement: Extended SQL functions ...
0
votes
1answer
39 views

store procedure variable storage takes long time to execute

i have a store procedure This procedure take a endless time for execution BEGIN set @my_user_id=null; select user_id into @my_user_id from office_user where username like my_username ...
0
votes
1answer
26 views

PostgreSQL - cannot create RULE from stored procedure

I'm trying to automate boring database work. I would like to have one function which creates views, rules, etc so I don't need to run corresponding SQL queries from file. Currently I faced an issue ...
1
vote
0answers
42 views

Query to find plan details for a specific stored procedure, searching by name

I'm trying to write a query to find information about query stats and the query plan for a specific stored procedure, but I can't manage to find the right DMV or query to look for a specific stored ...
1
vote
1answer
30 views

Incorrect key file for table error. How can I change location of MySQL's temp folder to another part of drive with more disk space?

I am using MySQL with Sequel Pro on a Mac Pro OSX Mavericks and am trying to run the code for a stored procedure in order to add two new columns to a table. The procedure does math operations on ...
0
votes
1answer
21 views

Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE?

I have a function which returns: TABLE (id TEXT, collected TIMESTAMPTZ) In another function, I would like to be able to pass the result of that first function as a parameter. For example: CREATE ...
1
vote
0answers
10 views

Plpgsql function array plus replace

Please be indulgent to my question, 'cause I am newbee at plpgsql/sql. :) I need to do a replace in table and the insert 'replaced' data in the same table. How to replace some values using array with ...
1
vote
2answers
47 views

Why does my statement result in a syntax error?

I tried to run this SQL statement in MySQL 5.5 and 5.7.1 but always got error. DELIMITER // CREATE PROCEDURE one_off_update_user_info_view_type() BEGIN DECLARE @batchsize BIGINT DEFAULT 1; ...
1
vote
1answer
55 views

Contradiction in book related to variable scope in Stored Procedures

I was reading the 70-461 TSQL training book and the part about stored procedures said this: Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by ...
0
votes
0answers
29 views

Stored procedure generating dynamic SQL is slow for some paramaeters

I'm having issue with a stored procedure that generates dynamic SQL, it performs ok for some parameters but very slow for some parameters. This has been an issue everyday and we are making some ...
1
vote
1answer
33 views

SUM() 3 columns with alias into a fourth one

This is one part of my procedure: select nome, --casos expedidos (- 3 meses) isnull((SELECT count(cod_rep) FROM tbReps where ...
4
votes
1answer
115 views

Microsoft SQL Server 2016 performance degradation for stored procedure called 1000's of times

Context : Small table, stored procedure to update table, first two thousand hits no problem. After those first hits it starts to crawl to a snails pace. This stored procedure has no problems in our ...
1
vote
1answer
32 views

MySQL Temporary Tables

I am facing Memory issue in our server. While debugging I came to know that, many number (millions) of temporary tables are created. Is there any way to identify which databases are creating this ...
0
votes
1answer
28 views

Errors declaring variables in stored procedure?

I'm sort of new to SQL and stored procedures. I'm trying to set some variables to use later on in a transaction while also setting a rollback variable on an exception, but I'm not able to do this. I ...
0
votes
1answer
34 views

Abstracting away filtering behavior from Oracle SQL Function

I have a Oracle SQL function, which returns a bunch of records through various sub-queries. However, there are countless filters that can be applied to these result records. In an application ...
0
votes
1answer
37 views

Unique key constraint violation not escalated to parent stored procedure

I have a stored procedure which calls 2 nested sub procedures. For Example, MyProc BEGIN BEGIN TRY BEGIN TRAN EXEC UpdateProc EXEC InsertProc COMMIT TRAN END TRY ...
7
votes
1answer
527 views

Business logic in stored proc vs middle layer (c#)

This is more of an architecture question. I once worked at a financial company that had a web application. Front end javascript. Middle layer was WebAPI to access the backend SQL Server. Back End ...
0
votes
0answers
47 views

DB2 While Loops

I have written some SQL for MS SQL Server that does what I want it to do. DECLARE @UpdateCount INT = 1; DECLARE @BatchSize INT = 1000; WHILE @UpdateCount > 0 BEGIN UPDATE tb SET tb....
1
vote
2answers
49 views

MySQL Stored Procedure - no of times executed

My question is simple, is there any way to calculate, number of times a stored procedure is called on applications or executed on MySQL server. For example: Procedure name: sptest Executing ...
1
vote
1answer
49 views

Wrong Last Execution Time sys.dm_exec_procedure_stats

I am running the following query it shows a Stored Proc that I know ran on Mon 6/6/2016 8:45 AM but the below query shows that the last execution time was 2016-05-19 12:09:23.197. Is there something I ...
0
votes
1answer
40 views

Organizing Stored Procs and Views

I have a database with 112 stored procs, 7 views and 88 tables. The problem is over time I have created tables and stored procs as I needed without taking the time to think about what I might be ...
1
vote
1answer
34 views

Auto-calculated field which includes letters and numbers

I'm currently creating a workflow form to allow a new part creation, as a part of this I need to write an SQL statement which must be a stored procedure to auto populate the Part code field. I have ...
1
vote
0answers
37 views

INSERT MySQL stored procedure executes twice when it contains a select statement as well [closed]

I have a very simple MySQL stored procedure as shown below: BEGIN INSERT INTO listing_offers (lo_active, lo_amount, lo_amount_number, lo_contract_reference,lo_created, lo_currency, ...
1
vote
1answer
81 views

How to change aggregate function without duplicating SQL (by using SQL)

In MS SQL Server 2016 I have a scenario where data will be processed according to different aggregation functions in a large GROUP BY ROLLUP. I would like to have a stored procedure that has a ...
1
vote
1answer
106 views

Execute a stored procedure in a SELECT or FROM statement or a SQL function

Are there any possibilities to execute a stored procedure in a SELECT or FROM statement or a SQL function? Like select sp_name_and_parameters from table_name_or_sp_name_and_parameters or select ...
3
votes
1answer
32 views

Mysql table crash, what do I need to do to fix this

Table '.\mysql\proc' is marked as crashed and should be repaired. Can anyone help me figure out how to fix this?
3
votes
1answer
53 views

Execute Stored Procedure with Account that has only read access

I created a stored procedure that has TRUNCATE and INSERT statements in it that I use for an SSRS report. I typically only use an account that is a member of db_datareader for SSRS reports, however ...
0
votes
0answers
35 views

Export Stored Procedure Results To CSV

I have seen many options for doing such, however most of them involve using bcp or using exec master..xp_cmdshell which I am unable to use on my server (security restrictions). I was looking at ...
0
votes
0answers
15 views

Recursive Conditional trigger for insert in table MYSQL

user[ user_id, parent_user_id] user_id parent_user_id 1 null 2 1 3 1 4 1 5 1 user_assigned_role [user_assigned_role_id, role_id, ...
0
votes
0answers
31 views

Should I use mysql stored procedure or regular inline statement?

So here is the problem: I have been currently working on a project that would be handling millions of data and thousands of records would be inserted as well as retrieved at the same time. Before ...
3
votes
1answer
105 views

Most useful stored procedures for a new DBA? [closed]

I am a new DBA working with several more experienced DBA's. When I am scratching my head one of them says "oh just run SP_xx_xx", and sure enough that begins the path to solution. Of course they all ...
8
votes
2answers
445 views

Create a “INTO” table with primary key

maybe for this community my problem is easy, but for me (a simple java programmer) it's a BIG problem. I have a Big DB with more and more data. So, the external db admin had create a job that show me ...
0
votes
1answer
65 views

Raise Multiple Exceptions Postgres

I am using Postgres 9.5, and I am trying to create exception handling in a stored procedure/function. I can't quite figure out how to raise the error should any of the 'INSERT' statements fail. If ...
8
votes
2answers
280 views

Same code in multiple stored procedures

I recently joined a company, and I just noticed that many of the stored procedures have the same section of code repeated throughout. I noticed because I was tasked with changing a small section of ...
3
votes
2answers
76 views

howto get the current line number from an executing stored procedure

Is there a function or keyword that will allow me to get the current line number in a stored procedure? I know there's an undocumented LineNo function that allows you to set the line number, and ...
2
votes
1answer
33 views

Logical condition when declaring handler procedure

I have a procedure in MySQL that executes a handler for SQLEXCEPTION to read the errno code and insert it to a table. DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @...
1
vote
1answer
54 views

MySQL -> Loop through a table, running a stored procedure on each entry

I have a database with 'books' (short stories for children) and it would be extremely informative to have word counts of each word in the books. I figured out how to get the word count for each word ...