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
44 views

Is DETERMINISTIC NO SQL function which returns the same value always a good alternative for using as a constant in MySQL?

MySQL does not support declaring constants which can be used between many stored procedures. For me, it seems that a deterministic no sql function can be used as an alternative for a constant. ...
1
vote
1answer
55 views

Stored Procedures execution permissions

So what I am trying to do is to limit the access to a stored procedure so that only another stored procedure may call it. Here is an example: create procedure Database_Deployment_Proc -- This ...
-2
votes
1answer
70 views

Password protected Database Engine with Server features and stored procedure [closed]

I like MS Access password protect feature but it does not useful for server based software like as SQLite which has password protect but not useful for server. SQL Server is one of the best DB engine ...
1
vote
1answer
27 views

I need to store count of ROWS in table to variable in which value of a column called 'procossed' =0, if value of variable >0 will execute next step

create PROCEDURE [dbo].[tablepass12](@TableName nvarchar(20)) AS BEGIN DECLARE @SQL VARCHAR(8000) declare @sql1 VARCHAR(1000) declare @counts numeric(4) set @counts=0; SET @sql1='SQLCMD -S ...
0
votes
0answers
62 views

MySQL prepared statement error

Good afternoon, I'm trying to use a prepared statement within a stored procedure in MySQL, but when I attempt to link the variable I get a syntax error. Here is the code SET _sql = ...
0
votes
1answer
18 views

First field from my return table is being eaten up

Another problem with some PostgreSQL stored procedure code... Same from this question: I have this function drop function uptimes(inicio date, fim date); create or replace function uptimes(inicio ...
1
vote
1answer
155 views

MySQL 5.6 - Creating an event inside a stored procedure

I am trying to create a procedure which create an event in MySQL. For example, something similar to this: delimiter // CREATE PROCEDURE schedule_my_db_job() BEGIN CREATE EVENT MY_DB_JOB ...
0
votes
1answer
52 views

Subquery returned more than 1 value. My WHERE clause needs to remove SELECT statements [closed]

I have a SQL Stored Procedure that is giving issues. I am aware that it is most likely the SELECT statements in the WHERE clause that are returning multiple values. I commented out the WHERE and ...
3
votes
1answer
113 views

Determine Which Parameters Passed Into Stored Procedure

Is there a way to determine (from within a stored procedure) which parameters were passed into a stored procedure (vs. those which just took the default value)?
1
vote
1answer
57 views

Procedure completes prematurely after converting comma-delimited value strings to XML

I have a procedure which executes beautifully within SSMS environment yet, when I hook it up to a web page, instead of seeing a list of packageIDs returned, the return is 'null'. I've checked ...
0
votes
0answers
29 views

Set a variable outside loop and fetch it every item of loop

My oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi I'm trying to set a variable outside loop and make its value change as the each item of loop. (Just like we ...
0
votes
0answers
57 views

MySQL procedure with sys_eval (mysqludf) called by trigger

I'm executing a trigger after a row in a certain table has been updated. That trigger calls another procedure which is supposed to do some quick checks before executing a small pythonscript. If the ...
1
vote
2answers
109 views

deteriorating stored procedure running times

I have a stored procedure that is executed by my web application. Today, when an end user started an operation that affected more rows than usual, I started getting calls that the wait times for the ...
5
votes
1answer
174 views

When does an execution plan not show in sp_WhoIsActive?

I use Adam Machanic's excellent sp_WhoIsActive procedure to see activity and to log to a table to troubleshoot long running queries. Why does the execution plan not show all the time in the results? ...
0
votes
1answer
35 views

How do I go about creating a script to compare , update and match structure of two databases

I am comparing two databases using Opendiff which is similar to sqlCompare. I have two databases which should match in structure so as I compare I would like to construct a script such that if the ...
0
votes
2answers
46 views

Column name or number of supplied values doesn not match table definition

I have created three tables using USE UserDecomAudit GO CREATE TABLE ADUserData ( ADUserID int NOT NULL PRIMARY KEY, Name varchar(50), SamAccountName varchar(10) NOT NULL, DistinguishedName ...
0
votes
1answer
33 views

Exhaustive SQL stored proc validation make insert slow

So basically our team is working on a module to retrieve rows value from excel file and insert it during batch job. The requirement are as follow: - Exhaustive validation should be done for each ...
5
votes
1answer
391 views

SQL Server passing variable to a string in a stored proc

I am trying to concatenate a custom string with a variable that is an int. Investigation is pending for ['+ @investigationidout +']. When I hover the mouse pointer over the first + sign it says: ...
0
votes
1answer
31 views

Unused Postgres sequence values

Observation: I have a serial index on a table in a PostgreSQL instance, and I execute a function that inserts a new record into that table. The function fails (reason not important), and the ...
0
votes
0answers
98 views

Creating audit triggers in a generic way for mysql tables

We have many many tables in our operational database. We would like to create an audit table for each one of them. We would like to create a trigger for each of our tables that will simply insert an ...
0
votes
0answers
15 views

Automatic creation of MySQL triggers

I would like to automatically create triggers for several table as part of the deployment process. trigger example: CREATE DEFINER=`root`@`localhost` TRIGGER xxx_admin.payment_AINS AFTER INSERT ON ...
2
votes
0answers
38 views

Mysql log table

I have a logging table that contains 3 fields Id action time The time column is 11 digit integer. Our goal is that each month the logs of the prior month will be deleted. It is possible to do with a ...
0
votes
1answer
51 views

When to use stored procedure / UDF? [closed]

I'm confused about usage of SP / UDF. Typically, it is also possible to write code in programs outside of database. So is there any general advice to decide when to use them?
1
vote
1answer
81 views

How to create a stored procedure to update database after table edit

In a C# website application with a database, I set a gridView table so that I could edit the values in the table by row, but it appears that I need a stored procedure to do that. Below is the stored ...
-1
votes
1answer
187 views

Job successfully execute Stored Procedure but no results [closed]

I have stored procedure which is using dynamic query and inserts details of severs into table. If I execute stored procedure manually it works fine. but if try to execute the same with JOB, Job ...
2
votes
1answer
215 views

Encryption/Decryption with StoredProcedure/View

I'm working on a project that uses EntityFramework for programmers to access the data. We've recently found need to add fields that were determined that need to be encrypted. This will be the first ...
0
votes
1answer
436 views

how to call a stored procedure in Oracle Scheduler job

i want to call a stored procedure in Oracle Scheduler job, my procedure and job like below ----stored procedure create or replace PROCEDURE emp_test IS begin delete from emp; insert into ...
0
votes
0answers
81 views

Trigger (insert/update) and procedure Required to encrypt data in MySQL

Is there any good way of doing encryption/decryption by use of triggers and procedure in MySQL. Looking for following to achieve in sequence Unload data for specific column to some other ...
0
votes
0answers
106 views

Postgres asynchronous procedure execution?

I have an ETL script entirely contained within a Postgres 9.3 procedure. The script takes several hours to process, which is fine for our purposes. What I haven't been able to find out is if there ...
2
votes
1answer
132 views

sys.dm_exec_procedure_stats missing history?

My boss would like me to find which (of approx 400+) stored procedures are not being used by our website any more. I'm found and reworked a script to give me the details of most recent executions, ...
1
vote
0answers
46 views

MySql - view out params of stored procedure when it selects from table

I have stored procedure: DELIMITER // CREATE PROCEDURE prcGetFemaleUsers() begin select firstName, lastName from tblUser where gender = 'female'; end // DELIMITER ; How can I get ...
3
votes
2answers
2k views

Loop through comma separated values

I have a Master DB. I want to make duplicate DB of the Master DB but not all tables just specified. I have those table names in comma separated string. tbl1, tbl2, tbl7, tbl10 etc. I'm trying to ...
4
votes
1answer
123 views

sys.dm_exec_procedure_stats DMV contents contradict BOL

I am doing some analysis of long running stored procedures and have come across something of an anomaly, according to books online sys.dm_exec_procedure_stats should only display one row per cached ...
0
votes
1answer
220 views

Simply inserting to temp table and failing

Can anyone tell me why this won't work for me? CREATE TABLE #UndistributedCmds ( pendingcmdcount int, estimatedprocesstime INT ) INSERT INTO #UndistributedCmds EXEC ...
0
votes
1answer
69 views

SET ANSI_NULLS - Performance of Stored Procedures

Does setting ANSI_NULLS ON/OFF affect the performance of stored procedures? SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[TestProcedure]
0
votes
1answer
130 views

Stored Procedure produce data for a range of dates

I have this table: PRJ_ID | START | FINISH xxxx | 2015-01-01 | 2016-01-01 I have two separates date start_range and end_range. I would like to produce the following output using a stored ...
2
votes
1answer
78 views

Generate Database Creation Script Within A Stored Procedure

I'm looking to automate a wipe of our test environment to bring it up to be in-line with production at the end of every sprint. As it stands this is currently being completed by the test enviroment ...
1
vote
4answers
91 views

MYSQL: used variable on IN condition

EDIT] The query below is not the actual query which I am having issues with performance. It is only a simplified query. As I only would like to know if it is possible to store multiple results into a ...
1
vote
1answer
516 views

SQL Server stored procedure producing data related to months between two dates [duplicate]

I'm stuck on the following problem. I have a start range date and end range date calculated by a query and I have three columns also produced by another query, the outcome of the last query is: ...
1
vote
1answer
21 views

Grant Permissions Within Report SP

Leaving aside why one would want to do such a thing: In MS SQL Server 2008, Is it possible to have a report SP grant permissions to a column used in that report to a table in a database other than ...
1
vote
1answer
67 views

Postgres : Using WITH … AS insert into to return non-inserted column

I'm creating an import job which uploads to a temporary table and then splits the data into two separate tables, joined by a key. What I'm doing is more complicated than the following but this simple ...
0
votes
0answers
34 views

I currently have a stored procedure that contains 5 joins, would it be more efficient to implement it as a view?

My current stored procedure is badly written and needs to be refactored. I've managed to find the time to do that. This procedure will be called every two minutes or so, and as more products are sold ...
2
votes
1answer
78 views

Calling stored Procedure from one db to give me a result in another db

CREATE DEFINER=root@localhost PROCEDURE AccountReqs( IN IsNewAccountStart VARCHAR (255) , In AccountName varchar (255) , IN AccountNumber varchar(255) ...
2
votes
1answer
119 views

How to remove the WITH ENCRYPTION from the code of the procedure - via T-SQL [duplicate]

I have many procedures that I use the option WITH ENCRYPTION when I create them. You can see this on the picture below, as an example: the problem with this is that when I want to save my previous ...
1
vote
1answer
288 views

mySql while loop ending after a insert

I have a stored procedure that is looping through a result set from a select statement. Inside of the loop there is a if statement that has two insert statements. When one of those insert statements ...
2
votes
1answer
33 views

Insufficient Prilivege for Function

I'm using a Function that I can't read, when I try to open it, I can't see the definition. I get following error Error : Insufficient privilege. Nevertheless, it is being executed by my app ...
-3
votes
1answer
52 views

Retrieve result for all the users

I have one query which returns particular user total hours for a month. I want to return for all the users in an org. UserRegistration table contains UserID. My code for particular user is, SELECT ...
0
votes
3answers
85 views

Joins in Stored Procedure

Below is my code, which returns billable and non billable total hours for their projects. I want to show in single row, but i am getting two result sets like below. DECLARE @UserName NVARCHAR(50); ...
0
votes
0answers
57 views

Varchar array as an input to Oracle stored procedure

I'm new to dynamic SQL execution. I'm working on an application which will take a list of input from an excel and generate a report out of it. I'm preparing the input data in the format required by ...
0
votes
1answer
133 views

Can I create a stored procedure in mysql using a txt file?

My server OS is Red Hat 6 with MySQL. I'm using this line in a .sh file to run some jobs on the server: mysql -u root -p[password] databasename < MySQL_JOBS_SENTENCES.txt The content of the .txt ...