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
7 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, ...
-4
votes
0answers
24 views

Get the output of a stored procedure into a table [on hold]

Can anyone help me with a table that takes the output from a stored procedure. It should not be a temp table or table valued parameter. I am able to insert values to the table, but when I use select ...
0
votes
0answers
25 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
80 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
409 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
29 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 ...
7
votes
2answers
258 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 ...
2
votes
1answer
41 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
23 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
29 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 ...
1
vote
1answer
32 views

weekly moving average from current_date to the last 52 weeks

I'm a new head to the mysql programming. I have to write a procedure for calculating a weekly moving average grouped by week from 'monday-friday'. Criteria is : current_date is the end date. start ...
1
vote
1answer
42 views

Create a stored-procedure to insert multiple rows on a junction table and other tables

I am new to Databases and Design and needs some help on a INSERT issue. I want to create a procedure that will insert multiple values to different tables, I need to make sure it stays atomic so I need ...
1
vote
2answers
23 views

MySQL stored procedure returns null

I wrote a simple procedure to modify the whole column to a set of meaningless values. However, when I tried call NewProc('tableName','colName') it returned 1064 - You have an error in your SQL ...
-3
votes
0answers
18 views

Object dependencies of stored procedure in SQL Server 2008

I need to have object dependencies of stored procedure in same sequence that objects are called in the procedure Can we export these dependencies to an Excel file?
0
votes
2answers
46 views

Create a copy of a SQL Server Job

Using T-SQL or Powershell, how can I create a stored procedure that performs the following: Copy a SQL Server Agent Job to another backup Job on the same server (with a new name) Disable the backup ...
0
votes
1answer
39 views

how to get multiple rows from multiple tables concatenated into a single string in a stored procedure in Oracle

I have a table A that has 7 different 1-N relationships. Given an A id, now I am running 7 simple queries to get the N side rows. I suspect this could be sped up. Is it possible to: create a stored ...
0
votes
0answers
39 views

Mulit-part identifier could be found - stored procedure

Hopefully I can get some sight into to what's going, Apologies for my lack of terminology and poor TSQL skills, I'm very much a developer that's inside an IDE and using ORM's. So I took the plunge to ...
0
votes
0answers
14 views

Track updated columns postgresql trigger?

I am using postgreSQL and I have tables on which I use triggers to get notified of changes on tables. Now, I have a usecase where when an update on a table is done, I want to notify only the updated ...
0
votes
2answers
37 views

Is there a way to reference a column dynamically within a trigger?

I asked a similar question a few months back and I'm improving upon my solution! I have an Oracle database where I would like to trigger a procedure to do additional work on a column upon updating of ...
4
votes
1answer
157 views

How can I speed up a stored procedure?

Here is an interview question I just had. I would like to know what a good answer to this would have been and what the different options would be. We have a stored procedure that is taking 26 ...
0
votes
0answers
12 views

sp_procoption seems to be failing on SQL 2005 instance

We have a SQL script that creates a Stored Procedure which is responsible for creating a SQL trace. On SQL Server 2008, the script runs fine, the SP gets created and the SP is configured to ...
0
votes
1answer
26 views

Result consisted of more than one row in procedure

i confused with this i try not using cursor and same get error so i back using cursor but before this day my query success and can generate but this get error please help me i already look other ...
1
vote
1answer
39 views

SPID Change for a process?

On SQL Server 2012 can a process SPID change mid-operation? We have a very unique query that we've been monitoring (production, but controlled testing) and have seen it at different intervals with ...
3
votes
1answer
65 views

Inconsistent Execution Plan for Stored Procedure

We have a business-critical stored procedure that normally runs daily at 2am from a scheduled job (in an SSIS package) on the production DB server. The same procedure/package is called from a second ...
2
votes
1answer
51 views

Can I avoid a join for my select procedures?

Here is a really simple example of a select procedure I'd like to create, although it's only partly done (it's pretty self-explanatory): delimiter // CREATE PROCEDURE mysql.selectTables (_first_name ...
0
votes
1answer
26 views

Can I load a CSV into a stored procedure that inserts into multiple tables?

OK, an example is often easier to follow, so here is my procedure: delimiter // CREATE PROCEDURE mysql.insertTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, ...
3
votes
3answers
136 views

SQL Logins in db_owner role on database cannot create stored procedures

I started locking down permissions in our test environment by removing sysadmin permission from generic accounts. The first account, used by several developers, is a local SQL login. I gave the ...
3
votes
3answers
38 views

Call to default values (MySQL) in a procedure

For the sake of an experiment, we're working with the following procedure (same DB, multiple tables): delimiter // CREATE PROCEDURE mysql.insertTables (_first_name varchar(30), _last_name ...
0
votes
0answers
42 views

Sql Server Stored Procedure calling table-valued function - Performance

I'm using Sql Server 2012 and I have a stored procedure that does about 15 left joins and one of those joins is a CROSS APPLY to a table-valued function. The first 14 left joins are not gonna restrict ...
0
votes
1answer
41 views

Stored Procedure Is Slower Than Query Due to Collation

I'm running two servers with MySql installed. I'm having an issue with a slow stored procedure (approximately 3 secs vs 0.1 seconds), solely on my production server, that is very quick on development ...
0
votes
1answer
46 views

Innovative solution required - Remotely create stored procedure

I have a local stored procedure that I want to distribute out to many other SQL Servers without having to log onto each one manually. All servers can access the same network share if I want to go ...
4
votes
1answer
45 views

How can a database parameter be used on `sp_addextendedproperty`?

I have found the MSDN definition and code examples of 'sp_addextendedproperty'. In Example A on that page, I see an example of how to add an extended property to a database. USE AdventureWorks2012; ...
2
votes
1answer
49 views

Update with CTE & OUTPUT blocks on concurrent calls

I was having deadlock issues, so I started using this CTE trick that I read somewhere. No more deadlocks. But now each client that I add slows down (blocks?) the sproc. E.g. a 1-2 second update with ...
4
votes
3answers
137 views

Get Stored Procedure Result Column Types

I'm trying to get whatever a stored procedure returns, with the column names and their types. I can do this with tables but I couldn't figure it out for stored procedures. I tried the sp_columns but ...
1
vote
1answer
61 views

Oracle: Query gives different answer when running from procedure

I guess my problem is somehow related with grant permissions. I have an "ADMIN" user, I grant it "dba" permissions like this: grant dba to admin Now in the "ADMIN" schema I have 2 tables: The ...
3
votes
1answer
51 views

Logging Stored Procedure Parameter Values

I've worked in an SQL Server environment before where logging was made part of our stored procedures to capture execution start/end, parameter values and error messages which I found very useful and ...
0
votes
1answer
51 views

Duplicate all stored procedures with a new name

I want to take all stored procedures and create them under a new name. My problem is with procedures that are called from other procedures . To rename a procedure is no problem for me. But to ...
4
votes
3answers
96 views

Overloaded Constructor for SQL Server stored procedure?

I'm making a stored procedure and I want it to accept a different number of parameters and have different behavior based on the number of parameters supplied. So let's say if I supply one parameter ...
3
votes
2answers
100 views

Transaction in a stored procedure

I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest ...
2
votes
1answer
37 views

Stored procedure unable to compare

Hi I tried implementing the below stored procedure which always takes my if condition as true and implements . CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertOpen`(in uid varchar(11),in S_ID ...
1
vote
1answer
72 views

Start failed for Job in SQL Server Agent

I am trying to schedule a job which executes stored procedure but it is failing. DECLARE @return_value int EXEC @return_value = [dbo].[sp_WarehouseAttendance_New] SELECT 'Return Value' = ...
3
votes
0answers
103 views

Read/Write Deadlock on Ms-Sql Server

We are facing deadlocks when store procedure is concurrently executed. We are using Microsoft SQL Server 2008 R2, concurrent execution is done with java executing multiple request of sp. I have ...
7
votes
1answer
321 views

Better execution plan if parameter is stored in local variable

I have two stored procedures. This one is incredibly fast (~ 2 seconds) CREATE PROCEDURE [schema].[Test_fast] @week date AS BEGIN declare @myweek date = @week select distinct serial ...
7
votes
4answers
465 views

How to get response from stored procedure before it finishes?

I need to return partial result (as simple select) from a Stored procedure before it is finished. Is it possible to do that? If yes, how to do that? If not, any workaround? EDIT: I have several ...
4
votes
1answer
124 views

Insert results from a stored procedure into a table variable

I have a stored procedure that stores values in a table variable. I select these values and return them when the procedure is called. I am trying to set these return values in another table variable ...
0
votes
0answers
14 views

How to point View to a dynamic database

I would like to create a view upon a database which changes on time For Example: initially, a view layer is to be created for the database B1_2016 in the next refresh, a new database is created and ...
1
vote
1answer
31 views

SQL Server - Analyzing Multiple Stored Procedure Statistics

I'm trying to analyze 1000+ stored procedures and I need to find out the: Elapsed Time in Sec (AVG based on # of iterations) CPU (AVG based on # of iterations) Logical Reads At first I'm using SQL ...
0
votes
1answer
43 views

Create a temp table that consolidates start and end dates plus generates a uniqueid for each consolidated record

Hey Microsoft SQL Gurus I need your help, please. This is my first post, and I have a table called projecthistory, it’s actually a view so I could take advantage of the Rank()function to get a unique ...
0
votes
1answer
70 views

Database backup over sqlcmd call from batch file

I'm trying to do a database backup by sqlcmd called from batch file but I can't achieve that. I'm getting error "Sqlcmd: Error: Connection failure. SQL native Client is not installed correctly. To ...
-2
votes
1answer
173 views

Why i am getting error “Incorrect syntax near the keyword 'where' ” while executing this variable?

The code is given below. I have stored in the variable some value and want to execute it. But it is throwing error "Incorrect syntax near the keyword 'where'" . Please help. Declare @P1_Gold ...