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

0
votes
0answers
9 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
26 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 ...
-1
votes
2answers
46 views

How can we create a table from stored procedure?

CREATE DEFINER=`root`@`localhost` PROCEDURE `create_tb_one`(In tableName varchar(255)) BEGIN CREATE table tableName( ID INT NOT NULL, SNAME VARCHAR(100) NOT NULL, ...
-1
votes
2answers
51 views

SQL Server : create table within stored procedure

I have a stored procedure that will be run by a user with public permissions. When operational, it will be called from an Excel spreadsheet and the results presented on a given tab. The procedure has ...
0
votes
1answer
19 views

mysql error handling stored procedure doesn't catch error

NOTE: I also tried CONTINUE instead of EXIT with same results. I am running mysql 5.5 and am trying to create a stored procedure that sends a variable out based on if it supports full text. ...
0
votes
1answer
15 views

Comparison of Performance Metrics between File System and RDBMS [on hold]

Our client currently has numerous interface implementations into Oracle EBS and Retail Sales Audit that use a File System. Data in the form of flat files are being processed by Shell Scripts numerous ...
2
votes
1answer
63 views

Scramble the data output of a stored procedure

I have a stored procedure that produces data for a call center system every morning. However, the data is sent in the same order daily. Is there a way to send the day in a different order every day ...
2
votes
2answers
154 views

How to backup the current code of a stored procedure and its permissions as well?

I often get requests to alter stored procedures, in both test, development and production environments. I would like a simple way to save the current code of the stored procedure, before the changes, ...
0
votes
0answers
37 views

Method to economically insert Bill of Materials BOM items into a Stock Table?

I’m new to the wonderful world of database design and I’m currently experimenting with an implementation for my little brother relating to Model Train Sets. [I will be using SQL Server 2012 / Asp.Net ...
2
votes
0answers
58 views

SELECT … INTO variable results in NULL or idk

you can find all the relevant code on pastebin. The problem: the variable to_spend stays default. If I leave the default 0, it is 0, if change it to NULL, it is NULL. So the statement if(to_spend ...
0
votes
1answer
24 views

Store Procedure for adding cascade

I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add ...
0
votes
0answers
29 views

stored procedure to display error messages & success when a sp_refreshview is ran

How can I convert this stored procedure to display error messages when a sp_refreshview fails? I need to know which view failed. I would also like to know which "database_name.SQL_View_object_name" ...
0
votes
0answers
35 views

MySQL: Slow Query Performance - Parameter Sniffing?

For a personal weather project, I am trying to define and run the following stored procedure: DROP PROCEDURE IF EXISTS history_between$$ CREATE PROCEDURE history_between (IN date_start DATETIME, ...
0
votes
0answers
33 views

call a stored procedure from sql window in PHPMyAdmin

I have a simple stored procedure that selects rows from a table. In phpMyAdmin, in the list of my procedures, if I click the Execute option, it executes properly, returning the desired rows, and ...
6
votes
1answer
114 views

How to find the stored procedure which executed an event?

I have a problem where a certain stored procedure disappears occasionally and I need to find out which script deletes it. I found this piece of code which gives the events related to the deletion of ...
0
votes
0answers
30 views

Table functions unnest, cardinality not found in DB2 10.1 LUW [closed]

I'm having difficulty in trying to re-deploy a stored procedure in DB2 10.1 LUW. CREATE TYPE "TBSGL"."ACCT" AS ROW( ACCT_NO VARCHAR(10), ACCT_DESC VARCHAR(40)); CREATE TYPE "TBSGL"."ACCT_ARR" AS ...
2
votes
1answer
41 views

How to get an array as output parameter from stored procedure?

This is my stored procedure: CREATE PROCEDURE `getAttritionData`( IN `supid` BIGINT, IN `from_date` DATE, IN `to_date` DATE, OUT `attr_rate` FLOAT, OUT `TERM_EMP_CNT` INT ) DETERMINISTIC ...
1
vote
2answers
24 views

Object Reference Not Set … Debugging Stored Procedure in Visual Studio

I'm working on updating a stored procedure that is called to update a user. This stored procedure needs to also create a description of what was updated for auditing purposes. However when debugging ...
1
vote
1answer
40 views

How can i write a stored procedure which will perform a calculation and store the result in a table?

I have two tables: CREATE TABLE `siteArea` ( `length` decimal(6,2) NOT NULL, `breadth` decimal(6,2) NOT NULL, `Area` decimal(6,2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE ...
0
votes
1answer
29 views

How to send an email with embedded values

I need to do a stored procedure that sends an email when a value reaches 100 with this wording in the body: "100 daily file downloaded from the user:USERID" where USERID is the user that reached 100. ...
1
vote
0answers
16 views

Oracle 11g XE - Giving granting privileges to a stored procedure

I need to use a stored procedure to give certain privilges over a view to users. However I don't know how to give the procedure the necessary privileges to grant users privileges.
1
vote
1answer
72 views

Benefits of using prepared statements in stored procedures?

Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a store procedure (assume query is only executed once in the procedure)? Does the engine ...
3
votes
1answer
83 views

Is it possible to validate more than one parameter simultaneously into a Stored Procedure in sql?

On Microsoft SQL Server 2008 I realized a stored procedure which contains more than 20 parameters, in this SP I update or insert data into table which does not accept null values. I need to evaluate ...
3
votes
4answers
142 views

Creating one stored procedure for all CRUD operations

I'm very inexperienced at writing stored procedures with conditional logic and I could really use some help. I have a data model with 7 tables and I'm trying to write a stored procedure for each table ...
0
votes
1answer
35 views

GRANT EXECUTE ON PROCEDURE unable to USE database

I am attempting to grant a user execute privileges to a single stored procedure without any other privileges in a MySQL 5.1.45 instance. The stored procedure is defined with SQL SECURITY as DEFINER. ...
0
votes
0answers
32 views

How to generate a JSON file from stored procedure(SQL) result

I want to get the data from DB using stored procedures in the form of a JSON file. In simple words, my output should be a JSON file which should be the result of data in DB (collected by a stored ...
8
votes
3answers
285 views

Who created a procedure? (SQL Server 2008 R2 )

Is there a way to list who created certain procure in SQL Server 2008? In this SO link there are some answers but from 6yrs ago. I know it's a best pratice to write your name and date, and then ...
3
votes
2answers
94 views

Use a view to join tables or a stored procedure for a better execution plan?

First let me state I am a C# developer, not a SQL Server DBA, so please excuse my ignorance on SQL Server database query execution plans. What I want to know is for a stored procedure which the ...
0
votes
0answers
26 views

How can I associate different not done continuation blocks with two cursors in the same scope?

If I have two cursors declared in the same block, how can I associate different not done continuation handler actions that are independent of one another and only execute with the cursor they're ...
0
votes
1answer
13 views

MySQL - using result of Procedure in Trigger to Insert in new table

I'm guessing I'm doing something that's prohibited by MySQL, but can't see what. Basically, I have a set of tables that hold lists of things (item name, item types, item groups etc.), and I need to ...
3
votes
1answer
29 views

Stored procedure deployment - push or pull into database? [closed]

Using stored procedures as part of an ETL process to populate tables in another database, which method would be considered a best practice (if any) in terms of deployment, maintenance, visibility and ...
1
vote
1answer
45 views

Why RECOMPILE doesn't fix parameter sniffing, but local variables does?

SQL Server 2008 R2 A developer was asking me about why his procedure wasn't running, but the query was ok. When I run the query, it was fast.But running the procedure, it was executing forever. ...
4
votes
2answers
489 views

How do I find the next saturday in MySQL

I have trigger and a stored procedure in it (so SP runs when trigger runs). I need a function which finds the next saturday to put in SP. So lets say, it is Wednesday (2015-7-22) today. If my ...
0
votes
0answers
22 views

Converting Legacy SQL Server 2005 Stored Procedure to Transaction based for parallelism [closed]

My task is to convert a legacy stored procedure written in SQL Server 2005 so that it can be used in parallel (multiple executions from different users). As of now the stored procedure is designed ...
1
vote
1answer
47 views

mysql trigger / stored procedure - update another table row by row

I've already, searched and read, many answers about this issue for 3 days, but couldn't get a clear answer on how to do this. this will be a little long, be patient please. First of all, I am trying ...
0
votes
0answers
68 views

optimizing paging in a stored procedure

I have the following SQL command which is built inside a stored procedure according to input parameters: SELECT DISTINCT * FROM ( SELECT DISTINCT TOP @displayLength * FROM ( ...
1
vote
1answer
59 views

No rows in sys.dm_exec_procedure_stats for a particluar stored procedure

I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats. Please suggest what could be various reason for this ...
1
vote
1answer
129 views

Most efficient way to insert rows into a temp table in a stored procedure

I am working in SQL Server 2008. I have a stored procedure with 5 queries inside of it that select rows from very large fact tables (on the order of millions of rows) into another table (call it ...
1
vote
1answer
30 views

Procedure to read from crontab like table and populate another one periodically

Postgres 9.3 Debian 7 I'm working on a mobile game project (poker like) where an admin user can register a recurrent tournament. As it's recurrent, I modeled it crontab like as follows. ...
-1
votes
1answer
67 views

Group by creating problem in Ranking of player [closed]

CREATE Table dbo.[#TMP] (UId uniqueidentifier, Points int, DC datetime, CId int, Username nvarchar(50)) ...
2
votes
1answer
83 views

Tracking down unused Views and Stored Procedures

I am working on a SQL Server 2005 database that has two Microsoft Access ADP frontends. This database needs a lot of love and attention which I am attempting to give it. There are lots of stored ...
2
votes
1answer
28 views

How to allow users to run a function, but not access objects the function is accessing

I have a PL/PgSQL stored procedure that performs various operations on tables (SELECT, UPDATE, INSERT, TRUNCATE). I want users to be able to run the stored procedure, but not themselves perform those ...
2
votes
1answer
133 views

Get values from Select statement and Insert them in Temp table Stored procedure [closed]

Here is my stored procedure. I want to select data from a table, save it in a variable then insert in other variable. However, my select statement has where. In it the value for the day should come ...
0
votes
1answer
63 views

Pass parameters to a procedure with SQLcmd

I use this SQL command: sqlcmd -v first_date="30/09/2015" last_date="15/09/2016" And I would like to pass that parameters to a stored procedure: ALTER PROCEDURE [dbo].[_testProcedure] WITH ...
0
votes
0answers
39 views

Stored procedure return -1

Normally, when I execute a SP, it returns a value of zero (0), but I have a SP is returning a -1 and can't figure out why. Does the -1 indicate an error? If I copy all the SP code into a separate ...
0
votes
1answer
72 views

Select all from table using procedure oracle

I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the ...
1
vote
1answer
32 views

Problem locating rows with dynamic SQL

I'm new at SQL in general, so I'm have a few problems with some of my stored procedures I'm designing. My procedure takes in 3 parameters. A location (or table name basically, a varchar), a room ...
2
votes
1answer
128 views

stored procedure not showing a result

If I run the query outside the stored procedure, I get results, but I only get the column names as the result after executing the query - even though return value is 0. Any help would be appreciated. ...
2
votes
2answers
212 views

Creating a stored procedure and adding date manually to filename

I am new to SQL administration and have been tasked with creating some nightly jobs that send an email with certain details contained within a spreadsheet and so far I have the following: EXEC ...
1
vote
2answers
64 views

How to use a MySQL error handler in a stored procedure with prepare/execute

I have a procedure defined like this: drop procedure if exists drop_tables_like; delimiter $$ create procedure drop_tables_like(myint int, pattern varchar(255), db varchar(255) ) begin DECLARE ...